Successfully updating a combobox not in list event

[kimarimonku]

I have a basic form linked with a subform inside of it. The main form has a list of customers in a combobox and the subform lists all the things they have ordered from us. This all works perfectly fine, I can add data to the list of things ordered and it's ok.

I decided instead of using a whole extra form to add customers I'd just have a not in list event and allow users to add customers through there, bit more intuitive and cut down on forms. This is my first time using VBA so I decided to get help and found this link from Microsoft Dev that seems to be exactly what I need using the second example: (can't link URLs)

It works to certain extent except one small problem, if I add a customer I have to close and reopen the form to be able to add/remove data from them in the subform. If I leave the form open and try to enter in data in the subform immediately it just shows whatever customer I had last on the combobox and adds it to the last customer as well.

It works perfectly fine if I reopen it so I thought it was some kind of query or update snafu but all the VBA code examples I found don't seem to do anything. Not exactly sure where to go from here.

Code of the example I found that mostly worked except the update problem

Private Sub cboDept_NotInList(NewData As String, Response As Integer)
    Dim oRS As DAO.Recordset, i As Integer, sMsg As String
    Dim oRSClone As DAO.Recordset

    Response = acDataErrContinue

    If MsgBox("Add dept?", vbYesNo) = vbYes Then
        Set oRS = CurrentDb.OpenRecordset("tblDepartments", dbOpenDynaset)
        oRS.AddNew
        oRS.Fields(1) = NewData
        For i = 2 To oRS.Fields.Count - 1
            sMsg = "What do you want for " & oRS(i).Name
            oRS(i).Value = InputBox(sMsg, , oRS(i).DefaultValue)
        Next i
        oRS.Update
        cboDept = Null
        cboDept.Requery
        DoCmd.OpenTable "tblDepartments", acViewNormal, acReadOnly
        DoCmd.GoToRecord acDataTable, "tblDepartments", acLast
    End If
End Sub

 

[vbaInet]

Welcome to AWF kimarimonku!

Unfortunately, your situation doesn't sound like a good candidate for using the Not In List event for. The fact that you're calling several InputBoxes to capture customer data is in itself not very intuitive. What if the user wishes to change the field previously entered, what would you do in such a case?

You're better off using a pop-up dialog form for capturing the data, then you can simply requery the combo box when the form closes.

 

[kimarimonku]

Thanks! I haven't really messed around with Access that much and there's a lot more to it than I thought.

Actually I forgot to remove the loop. I only one item: customer name. I just copy and pasted the example from the Dev site.

I'm assuming I have to requery something...I'm just not sure what. and if I should have the requery in the not in list event or something else entirely.

 

[vbaInet]

Sounds like a possible problem with your table structure. Can you describe the Customers table structure.

 

[kimarimonku]

Nothing major at the moment: Customer table with just a name at the moment. With a one to many relationship with a Order table that has items and quantity at the moment.

Outside of the not in list event it works perfectly fine and when I close and restart the form everything works fine it's getting it to update that I'm having issues with.

 

[vbaInet]

It's not an ideal setup. Do you understand normalisation?

These are the steps you will need to perform:
1. Copy the saved value to a variable
2. Set the Value of the combo box to ""
3. Requery the combo box
4. Set the Text of the combo box to the value saved in the variable in step 1

Give it a go and ask if you're stuck.

 

[apr pillai]

If Combo box Source data is from a separate table then take a look at this link: Updating Combobox when Not in List is active