Access Use Unbound Combobox To Find and Add Record

Question

I have an item lookup form that displays the information of a single record. There is an unbound combo box on the record that the user uses to type in the item number and go straight to that record via DoCmd.SearchForRecord. That part works fine.

What I would also like to do is have that unboundcombobox be able to add new items to the table. I have the NotInList right now set to this:

If MsgBox("Create new inventory item " & NewData & "?", _
    vbQuestion + vbYesNo) = vbNo Then
    Me.PartNumberList.Undo
    Response = acDataErrContinue
    Exit Sub
Else
    CurrentDb.Execute "INSERT INTO Inventory ( PartNumber ) VALUES ('" & NewData & "');"
    Response = acDataErrAdded
End If

The table receives the new data, but now I need to requery the form before the form can find the data. If I add Me.Requery immediately after that code, the form thinks I'm trying to enter the same item into the form, it still hasn't found it in the table, and it asks again if I want to add that item. If I try to requery the combobox first, it tells me I must save the data first. Adding If Me.Dirty then Me.Dirty = False right in front of the Requery doesn't fix this.

So after some searching I found that I should try moving to the first record in the combobox before a requery. This works except now I get an notice "The value you entered isn't valid for this field." I press OK and then everything is requeried and I can now manually navigate to my new part number. But I can't figure out exactly what is throwing this notice. On Error Resume Next doesn't bypass this notice. When I step through the code, the notice seems to happen after NotInList, and then it skips all other events. I won't fire my BeforeUpdate or AfterUpdate.

Maybe I'm just going about my item lookup all wrong. Maybe I should have the user add items with a different form. It would just be convenient to keep it all to one single form. Oh maybe it's worth mentioning, this form is opening as a Dialog, in the Add mode. But the errors stay the same even if I open the form by itself with Data Entry off.

DanielG DanielG · Accepted Answer · 2017-11-08T21:15:01

Instead of requerying the form after you successfully add the row to the table, maybe programatically set the forms' record source instead? So, make sure the form has no recordsource in design view and save the form. Open the dialog form where needed, and make sure the ComboBox has a RecordSource. The user enters a value not in the list, you use the NotInList event to add it to the DB, store the value the user entered into a variable, requery only your ComboBox, set the combobox to the variable so that the new entry is selected, and then your code would use:

Me.RecordSource = "SELECT * FROM Inventory WHERE PartNumber= " & Me!YourComboboxValue

OR

Me.RecordSource = "SELECT * FROM Inventory WHERE PartNumber= '" & Me!YourComboboxValue & "'"

If "PartNumber" it is a string value.

This should be WAY faster than DoCmd.SearchForRecord. When you open the form intitally, you're pulling rows into the form that will never be needed.

After you requery the combobox, the new value will be in the list but not selected. That may be ok for your situation. If you store the part number in a variable first, you can do something like this:

Dim sPartNumber as String
sPartNumber = NewData
<ADD YOUR DATA TO THE TABLE>
Me!ComboBox.Requery
Me.RecordSource = "SELECT * FROM Inventory WHERE PartNumber = '" & sPartNumber & "'"
Me!Combobox = sPartNumber (Optional)

Code may not be perfect as doing it from memory. :-)

Let me know, and good luck!

Access Use Unbound Combobox To Find and Add Record

1 Answers