Add new item to Combo Box list from table
I am wanting to allow the user to update (add) a category. There is a tblCategory that has three field: ID (primary), VendorName and Category. the Combo box opens only the categories associated with that vendor. Through the help of these threads, we got that to work. this is a Row Source query that allows that to work. I found some code to update tblCategory when the user adds Category. It is 'working' in that the category is added to the tblCategory. the problem is that the new record in tblCategory needs to include the VendorName as well. This is available on the form. Actually I was able to get to two consecutive operations which adds the Category and then adds the VendorName but it made two record entries so I have a record with ID, blank VendorName, Category then ID,VendorName, blank Category. So this doesn't work. Next I found the INSERT INTO, which was used semi successfully, also has another version which inserts the entire record at once but I must have something wrong in the syntax because I get errors. Run-time error 3061, too few parameters. Expected 2. The highlighted line is DBEngine(0)(0).Execute strTmp, dbFailOnError. the code for "not on list' is below.
Private Sub Combo53_NotInList(NewData As String, Response As Integer)
'Allow user to save non-list items.
Dim strTmp As String
'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new category?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
' These two work but they each start a new record so that one record has a blank for VendorName_
'And the other has the correct VendorName but a blank for CAtegory_
'Append the NewData as a record in the Categories table.
' strTmp = "INSERT INTO tblCategory ( Category ) " & _
' "SELECT """ & NewData & """ AS Category;"
' DBEngine(0)(0).Execute strTmp, dbFailOnError
' 'Add the SupplierName in the record
' strTmp = "INSERT INTO tblCategory ( VendorName ) " & _
' "SELECT """ & Forms!frmMRRLog!SupplierName & """ AS VendorName;"
' MsgBox "Gets to Here" & Forms!frmMRRLog!SupplierName
'DBEngine(0)(0).Execute strTmp, dbFailOnError
'Notify Access about the new record, so it requeries the combo.
'Response = acDataErrAdded
' Try the 'VALUES' vesion of INSERT INTO
strTmp = " INSERT INTO tblCategory " _
& "(ID,VendorName, Category) VALUES " _
& "(500,Forms!frmMRRLog!SupplierName, NewData);"
DBEngine(0)(0).Execute strTmp, dbFailOnError
Response = acDataErrAdded