Combo Box allowing not in list items

[gsandy]

I am using the following code in a textbox (CountryID) to show a country name (CoName) from a table (tblCountry). If the comboBox (cmbCoName) in current form (frmCountry) has no entry then CountryID remains blank, great. If I use the form to add a new entry then CountryID flashes because it does not recognize the entry. Can I use “or” to add another condition in the IIF statement so that it allows the “not in the combobox list” entry? Thanks Sandy

  =IIf(IsNull([txtCoName]),"",DLookUp("CoName","tblCountry","Country_ID = " & [Forms]![frmCountry]![txtCoName]))

 

[burrina]

Perhaps something like this could help.

On Error GoTo err_handler

Dim strSQL As String

' The user just entered a value that's not in the Country table.
' Prompt the user to verify that they wish to add the new value.
If MsgBox("That is not in the list of countries. Add it?", _
vbOKCancel, "New Country?") = vbOK Then
' Set the Response argument to indicate that data is being added.
' (This will automatically re-query the combobox when done.)
Response = acDataErrAdded
' Add the user-entered string (NewData) to the appropriate table.
strSQL = "INSERT INTO tblCountry( CoName ) " & _
"VALUES (""" & StrConv(NewData, vbProperCase) & """);"
CurrentDb.Execute strSQL, dbFailOnError
Else
' If user chooses Cancel, suppress the error message and undo changes.
Response = acDataErrContinue
End If

exit_here:
Exit Sub

err_handler:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox str(Err)
MsgBox Err.Description
Resume exit_here
End If

HTH

 

[gsandy]

Thanks for your help Burrina. Cheers Sandy