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.

 

[Pat Hartman]

Personally, I don't allow users to update combos on the fly. It may be that I've just never had an app that the combos got added to so frequently that it was inconvenient for people to do the maintenance separately. The reason I don't allow this is because people have a really bad habit of creating typos that essentially duplicate existing values. I've attached a demo that in concept was created 40 years ago using COBOL and IMS (a hierarchical DBMS from IBM) It was rebuilt several times in various mainframe databases and finally ended up as an Access database. When I use the app, the BE tables are usually SQL Server but they are embedded in this example for ease of distributing.

 

[moke123]

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

 

[Pat Hartman]

I've worked for Insurance companies, Banks, Direct Marketing, Retail, various types of manufacturers including the "military industrial complex", and state and federal agencies both in the US and the Middle East. But not common carriers. Insurance companies have both agents and brokers so you would need to be more specific.

The very first version of this mini-app was built for Pratt & Whitney. They make engines for the military's planes. I used it a second time at Pratt for a different app

 

[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.

 

[Pat Hartman]

I had two assignments at GenRe in Stamford and another at a smaller company whose name excapes me at the moment. In Hartford, I worked as an employee for Traveler's (they took me in off the street and made me a programmer in 1968 before I could even vote) and later at the Hartford. I also had a contract with Beasley in Farmington. They were a UK company doing business in the US. The app I created for them was probably my all time favorite. They were new in the US so they were constantly adding new policy types. It took about four months for the IT people in London to update their web app to enable it to handle the new LOB. I created an Access app that used an Entity-Attribute data model. Each new product required 6 types of policy documents and potentially thousands of endorsements. The app allowed the user to create a completely new product in less than a day including creating the crossreference lists to map table data to word documents. The IT department loved it but hatted the fact that they didn't own it so they built their own. It only took them three years and a couple of million dollars to reproduce what I did in about 3 months with Access.

I left the data from the insurance app in the sample because it had the most tables. Insurance companies love their codes