Update table value using combo box - new entries

Question

Apologies in advance for the subject may not match exactly what I am trying to achieve. I have a form that has a pair of combo boxes, one is for a vehicle make/manufacturer, the other a vehicle model. The vehicle model combo box gets filtered based upon a value in the make/manufacturer combo box. This is all working as expected.

The problem I am having is when entering new values into the combo box, specifically the vehicle model combo box. In my vehicle model table I have:

ModelID (auto number, primary key)

Model

ManufacturerID

If I enter a new value into this combo box, the vehicle model table only updates the ModelID and Model, but not the manufacturer ID. Which then breaks my filter of selecting a manufacturer and only displaying valid models. This is the code that I am using to update the Model combo:

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

    Dim strTmp As String

    strTmp = "Add '" & NewData & "' as a new model?"

    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

          strTmp = "INSERT INTO tblVehicleModel ( VehicleModel ) " & _

            "SELECT """ & NewData & """ AS VehicleModel;"

        DBEngine(0)(0).Execute strTmp, dbFailOnError

        Response = acDataErrAdded

    End If

End Sub

I cannot figure out how to take the ManufacturerID from the Manufacturer combo box and insert it into the Model table. I tried modifying this piece:

          strTmp = "INSERT INTO tblVehicleModel ( VehicleModel, ManufacturerID ) " & _

            "SELECT """ & NewData & """ AS VehicleModel;cboMake.Column(1) AS ManufacturerID"

But, this does not work so I suspect I am missing something, or have something incorrect in the code. Any help appreciated.

Thanks.

Answer 1

I would use a Values clause instead.

trTmp = "INSERT INTO tblVehicleModel ( VehicleModel, ManufacturerID ) " & _

            "VALUES('" & NewData & "', " & cboMake.Column(1) & ");"

This assume that ManufacturerID is a number data type.

Answer 2

If your bound column is 0, then you don't need to use the Column property.

Answer 3

Thank you. That worked perfectly, I had to modify it to:

"VALUES('" & NewData & "', '" & cboMake.Column(0) & "');"

Extra apostrophe around the cboMake.Column(0) and also had to change the column to 0 from 1, but it works. Thank you.