Add value to combo box

[tmyers]

I am trying to use Allen Brownes code for adding a value to a combo, but am having some difficulty.

Microsoft Access tips: Adding items to a combo/list box

Techniques for adding items to drop-down boxes in Microsoft Access

allenbrowne.com

Private Sub TypeID_NotInList(NewData As String, Response As Integer)

Dim strtmp As String
    strtmp = "Add '" & NewData & "' as a new type?"
    If MsgBox(strtmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        strtmp = "INSERT INTO tblFixtureTypes ( TypeName )"
        strtmp = strtmp & "UPDATE tblFixtureTypes SET tblFixtureTypes.JobID = [Forms]![JobQuote]![JobID]"
        strtmp = strtmp & "SELECT """ & NewData & """ AS TypeName;"
        DBEngine(0)(0).Execute strtmp, dbFailOnError
        
        Response = acDataErrAdded
    End If

End Sub

The problem I am having is the field [JobID] is required. I tried adding the update portion into it, but then get a syntax error on the INSERT statement. What am I messing up?

 

[moke123]

Your not really adding to the combo, Your adding to the table the combo is based on.
What are the fields in the table and why is JobID required?

no reason to have an update statement in there.

Your string should look more like
strtmp = "INSERT INTO tblFixtureTypes ( YourField ) Values( """ & NewData & """ )"

use a debug.print to make sure its got proper syntax

If you need a 2nd field in there for some reason add it as you would with any insert sql.

 

[tmyers]

Your not really adding to the combo, Your adding to the table the combo is based on.
What are the fields in the table and why is JobID required?

no reason to have an update statement in there.

Your string should look more like
strtmp = "INSERT INTO tblFixtureTypes ( YourField ) Values( """ & NewData & """ )"

use a debug.print to make sure its got proper syntax

If you need a 2nd field in there for some reason add it as you would with any insert sql.

JobID is a FK in the table I am inserting into. I had recently restructured my app so may have to still tweak a few settings still.
I will try your variation real quick and see how it works.

 

[tmyers]

How would I pull the JobID from my main form in the SQL statement?
I tried:
"VALUES(""" & NewData & """, JobID = [Forms]![JobQuote]![JobID]);"

That isn't right, but my mind hasn't quite started up yet.

 

[moke123]

first for the message I would change that variable name to strMsg.
strMsg = "Add '" & NewData & "' as a new type?"

strtmp = "INSERT INTO tblFixtureTypes ( YourField, JobID ) Values( """ & NewData & """ ," & forms("JobQuote").JobID & ")"
debug.print strtmp

I dont know what the field is for the newdata variable. If it is "Type", which it looks like your using, that is a reserved word so you need to change that. use the debug.Print so you can see what strtmp resolves to.

Quick example attached.

 

[tmyers]

first for the message I would change that variable name to strMsg.
strMsg = "Add '" & NewData & "' as a new type?"

strtmp = "INSERT INTO tblFixtureTypes ( YourField, JobID ) Values( """ & NewData & """ ," & forms("JobQuote").JobID & ")"
debug.print strtmp

I dont know what the field is for the newdata variable. If it is "Type", which it looks like your using, that is a reserved word so you need to change that. use the debug.Print so you can see what strtmp resolves to.

I just noticed the issue with the name being Type as well. I thought I had found all of those and fixed them. Will correct that.

That change worked however. I haven't seen that syntax with forms before. Wouldn't have easily guessed that one.
Thanks for your help Moke!

 

[moke123]

forms("JobQuote") is just a personal preference. If it is on the same form you can use the Me. keyword, as in Me.JobID.

good luck with your project.

 

[moke123]

Just curious Pat, did you work for Carriers or agent/brokers?

 

[moke123]

I just noticed all the Ins. references in your app. I was a home office claims mgr in nyc for years. Dealt heavily with our reinsurers in hartford area.