Access Combox value not in list.

[shani908]

Hello

I have a form name "Properties", In property form I have a combobox labelled "Agent Name", control name "CboAgent".
If a user enter an Agent name that is not in the list it, for example "Agent 15", It opens another form name "Agent Details".
In the form "Agent Details" I have a field name "AgentName", How can I auto populate this field with the text I entered previously in the combobox in Propertied form ? like "Agent 15".

Thanks

[ranman256]

the popup field should be bound to the table that lists the agents.
once you add the new agent, you should add code when the form closes to update the combo box with the new items added:

sub form_close()
  on error resume next
  forms!fMainForm!cboAgents.requery
end sub

if you dont have the code to refresh, you can manually refresh it by clicking the REFRESH ALL button icon on the tool bar. (buy why if the system can update it)

[Micron]

You would have to have saved a record before having access to the name in the field?
One way to pass values to an opened form is via OpenArgs.
https://learn.microsoft.com/en-us/of....form.openargs

[Gicu]

Can you show us the code you have in the NotInList event of the cboAgent? Within that event the value you want is stored in a variable called NewData. So you can pass it to the Agents Detail form as the OpenArgs in the Docmd.OpenForm method or simply populate on the next line:

Forms![Agents Detail]![AgentName]=NewData

Cheers,

[Edgar]

The NotInList event for combobox controls is cursed. You do NOT want to use that event ever. It is far better to just open a form, add the record you need, close the form, requery the combo with the added value and be done with it.

If you still want to use the NotInList event, see the attached database for the complete solution.

The simple way to pass the written value in the combobox to the opened form is as follows...

frmProperties code:

Private Sub cboAgentID_NotInList(NewData As String, Response As Integer)
    ' Open form in dialog mode, pass the text written (NewData) as OpenArgs
    If MsgBox("Do you want to add that?", vbOKCancel) = vbOK Then
        DoCmd.OpenForm "frmAgents", acNormal, , , acFormAdd, acDialog, NewData
    End If
    
    ' Suppress the annoying message
    Response = acDataErrContinue
End Sub

frmAgents code:

Private Sub Form_Open(Cancel As Integer)
    ' When form opens, if OpenArgs is not Null, then:
    If Not IsNull(Me.OpenArgs) Then
        ' Grab the text in OpenArgs and write it to the textbox
        Me.txtAgentName = Me.OpenArgs
    End If
End Sub

[Welshgasman]

I do not think so, I used it successfully for over 5K records?

Here is my code for the CrewName

Private Sub Crew_ID_NotInList(NewData As String, Response As Integer)
'Dim strSurname As String
'NewData = Left(NewData, InStr(NewData, " ") - 1)
'Response = AddNewToList(mixed_case(NewData), "Crew", "Surname", "Crews", "frmCrew")
    Dim txtSurname As String, txtInitials As String, strPKField As String
    Dim intNewID As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String, strNewForm As String


    Response = acDataErrContinue
    strNewForm = "frmCrew"


    If MsgBox(NewData & " is not in list. Add it?", vbYesNo) = vbYes Then


        strSQL = "SELECT * from  Crew WHERE 1 = 0"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSQL)
        'Need to check if name does have initials, some crew do not have/use them
        If InStr(1, NewData, " ") Then
            txtSurname = Left(mixed_case(NewData), InStr(1, NewData, " ") - 1)
            txtInitials = UCase(Trim(Mid(NewData, InStr(1, NewData, " ") + 1)))
        Else
            txtSurname = mixed_case(NewData)
            txtInitials = ""
        End If
        rs.AddNew
        ' add dummy cabin just to get to form
        rs!Cabin = "C"
        rs!Surname = txtSurname
        rs!Initials = txtInitials
        strPKField = rs(0).Name                  'Find name of Primary Key (ID) Field
        rs.Update


        rs.Move 0, rs.LastModified
        intNewID = rs(strPKField)


        DoCmd.OpenForm strNewForm, , , strPKField & "=" & intNewID, , acDialog
        
        Response = acDataErrAdded
MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    Else
        Response = acDataErrDisplay
    End If
End Sub

[Edgar]

welshgasman, your code is not representative of what OP wants, it is about opening a form to enter the value the combobox needs and likely more information about the record, not dummy data.

[Gicu]

But what you submitted was already suggested by Micron in post 3 and me in post 4; and in my opinion there is no cursed events if used properly and as intended....🙂
Cheers,

[Edgar]

Counter my arguments with code, folks. Developer experience is not nice with that event.

[shani908]

Thank you all . Much Appreciated