Suggestions needed on reSetting a Combo BOX after a new entry ..

[WhiteZiggy]

OK, I have a combo box (cbo), that gets its values from a table/query. I want to add the entry to the table if its not there and then reset the drop down.

Here is what I have so far..

Private Sub cboVendorNames_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim dblVendorID As Double

dblVendorID = InputBox("Please enter vendor ID if known.", "Vendor ID", 0)

strSQL = "INSERT INTO tblVendorList ( VendorName, VendorID ) values ('" & NewData & "'," & dblVendorID & &quot"
CurrentDb.Execute (strSQL)
DoEvents
end sub

This works except it doesnt requery the box.. It ALWAYS says...."The text you enterer is not in the list."

Why?

Suggestions?

Thanks,
WZ

 

[jimb0ne]

if your rowsource of your combobox is based on a query which is based on the table you are entering the value you should add the following line after doevents

cboVendorName.requery

that should solve the issue.

 

[WhiteZiggy]

When I do that it gets stuck in a loop and keeps saying the entry isn't there. Yet when i look, it is..

It is really annoying to have it say its not there, then it is... (

 

[jimb0ne]

that not in list event is monkey garbage. I can't even get it to fire for some reason. Just search for the value they enter when the box loses focus, if you don't find the value, prompt for one or whatever, add it, then requery the box, should avoid the looping issue as well.

 

[jimb0ne]

umm, is the problem you are having basically with not being able to get the message saying "the value you entered was not in the list" to go away? other than that everything works fine?

 

[RoyVidar]

Hi!

- barging in on your conversation...

If the entry is made, then you'll just have to tell Access how to handle it. This event has two arguments, you've alredy used the NewData, but the other one is also interesting. With the response argument you can tell what to display (or not display anything at all;-)).

Assign either acDataErrContinue or acDataErrAdded to the response argument, the first will remove the default message box, the other will give you a "added" message.

[tt]Response = acDataErrContinue[/tt]

- nother advice - better check if the user enters any value thru the input box, and avoid running the sql if not.

Roy-Vidar

 

[jimb0ne]

thanks roy, as you can see after you answered my notinlist question I realized the event was in fact not monkey garbage, thanks for the help with the event y0

 

[WhiteZiggy]

Yes thanks...I will try that first thing in the morning..

Jeremy