Add items to an Access combo box on the fly

[mlubbs]

I am trying to add data to a combo box on the fly using Docmd but seem to be running into an error that I cannot find an answer to.

Private Sub Descrepancy_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Discrepancy " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "")

If intAnswer = vbYes Then
DoCmd.RunSQL "INSERT INTO Discrepancy_Choices (ID,Discrepancy) " & "VALUES ('" & NewData & "','');"
MsgBox "Addition completed.", vbInformation, ""
Response = acDataErrAdded
Else
MsgBox "Please choose a Discrepancy from the list." _
, vbInformation, ""
Response = acDataErrContinue
End If
End Sub

The debug message says "Runtime error '3464':

Data type mismatch in criteria expression.

The code that gets highlight during debugging is:

DoCmd.RunSQL "INSERT INTO Discrepancy_Choices (ID,Discrepancy) " & "VALUES ('" & NewData & "','');"

I think my issue is either the ID,Discrepancy or NewData, but I cannot seem to get the right combination to make it work.

Any help would be appreciated

Thx,

Michael

 

[John Big Booty]

Perhaps the sample here will give you some pointers.

 

[mlubbs]

Thanks for passing this along.


1. I am not really sure what the combo2 part is doing and am having trouble relating it to my tables

2. I was hoping to cut steps out with what I was trying to do.
a. You have to click ok
b. You have to double click
c. You have to click done
d. You have to then find your entry

 

[John Big Booty]

...

d. You have to then find your entry

Incorrect, as the sample does exactly that, there is no requirement to find the new item that has been added unless you have added multiple items and want to refer to the first item added. Please read the comments in the code.

Try the attached.

Caveat Emptor; This method will only work where the record being added only requires the one piece of data to be added. It will not work if you are trying to add items to a price list, for example, where the product code, product description and unit price are require, for that you would need to use the process described in the link in my first post.