How do I assign an already known integer to a field after the NotInList event is called?-CodePudding

I have this complicated VBA function on a MSAccess form frm_DataEntry. It searches for values which are not in a list. The function is called on de NotInList event of the comboboxes.

When the typed string in combobox cbo_CustomerLocations is not in the list, it will ask if I want to add it to the table tbl_CustomerLocations by Yes/No question. After that it goes from 1st column to the last column and asks if I want to add some new data. The code below shows how to add a CustomerLocation.

The last field CustomerID of my table tbl_CustomerLocations is linked to the CustomerID field of table tbl_Customers

Now my question:

How do I alter my VBA code when the NotInList event is called, and when it reaches the CustomerID column (the last column), It must not ask 'What do you want for CustomerID', but rather automatically selects the CustomerID I previously selected on the same form frm_DataEntry on combobox cbo_Customers?

Private Sub cbo_CustomerLocationID_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
    
    String_Value = Me.cbo_CustomerLocationID.Text
    
    MsgBold = String_Value
    MsgNormal = "Add to list with locations?"
    
    Debug.Print
    
    If Eval("MsgBox ('" & MsgBold & vbNewLine _
    & "@" & MsgNormal & "@@', " & vbYesNo & ", 'New Location')") = vbYes Then
        Set oRS = CurrentDb.OpenRecordset("tbl_CustomerLocations", 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
        cbo_CustomerLocationID = Null
        cbo_CustomerLocationID.Requery
        DoCmd.OpenTable "tbl_CustomerLocations", acViewNormal, acReadOnly
        Me.cbo_CustomerLocationID.Text = String_Value

End If

End Sub

CodePudding user response：

Use an If Then block within the loop to check for name of field.

If oRS(i).Name = "CustomerID" Then
    oRS(i) = Me.cbo_Customers
Else
    sMsg = "What do you want for " & oRS(i).Name
    oRS(i).Value = InputBox(sMsg, , oRS(i).DefaultValue)
End If