Add new item to Combo Box list from table

[gakiss2]

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
'End If
' 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
End If
End Sub

 

[gakiss2]

Wanted to add that I tried getting rid of the primary key to add simplicity but that caused other issues so I have abandonded. And I don't even think it would solve it anyway.

 

[theDBguy]

Hi. Have you considered simply using the List Items Edit Form property?

 

[gakiss2]

Hi. Have you considered simply using the List Items Edit Form property?

Do you mean change the property to allow additions? I thought that in that case the underlying table. TblCategory would not get updated.

 

[theDBguy]

Do you mean change the property to allow additions? I thought that in that case the underlying table. TblCategory would not get updated.

No. I mean, if you go to the design view of the combobox, go to the properties window and look for the "List Items Edit Form" property and select your data entry from for Category from the dropdown list.

 

[gakiss2]

No. I mean, if you go to the design view of the combobox, go to the properties window and look for the "List Items Edit Form" property and select your data entry from for Category from the dropdown list.

OK I found the place in the properties you are talking about. Right now I only have the one form which actually contains the Combobox so that doesn't make sense (at least to amateur me). Instead do you mean for me to make a Form whose purpose is to add records to tblCategory? In fact I think that even the Access Form wizard could handle that task. I can see how that would be simpler.... I can try it and see how it works

Sorry if I'm not tracking with you yet. thanks for your patience.

 

[gakiss2]

OK I found the place in the properties you are talking about. Right now I only have the one form which actually contains the Combobox so that doesn't make sense (at least to amateur me). Instead do you mean for me to make a Form whose purpose is to add records to tblCategory? In fact I think that even the Access Form wizard could handle that task. I can see how that would be simpler.... I can try it and see how it works

Sorry if I'm not tracking with you yet. thanks for your patience.

Not sure if I'm just not getting what you are saying but that seems very clunky and doesn't seem to be working. the data entry form I created does launch. the very first time it even launched with the right Vendor name in the VendorName field and the text I had typed in the Category field. so I closed the form and went to the frmMRRLog and it would still say the item was not in the list. I looked at the tblCategory and it had not been updated. and besides it seems like a lot of extra busywork just to add a category that is already typed in. Sorry, I don't mean to criticize your help. And it may well be that I am just not understanding your advice.

 

[theDBguy]

OK I found the place in the properties you are talking about. Right now I only have the one form which actually contains the Combobox so that doesn't make sense (at least to amateur me). Instead do you mean for me to make a Form whose purpose is to add records to tblCategory? In fact I think that even the Access Form wizard could handle that task. I can see how that would be simpler.... I can try it and see how it works

Sorry if I'm not tracking with you yet. thanks for your patience.

Hi. I can see you're getting there though. Let us know how it goes. Just make sure the form to enter new categories is set as a Data Entry form. Cheers!