Combo Boxs

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

When you have a combo box on a form can you get it to auto
fill the value when you start typing?

Example: I have a box that is labled "City" and I want the
users to select from the cities listed. However if the
city is not listed I want the city to be added to the list
in the combo box.

I can not seem to figure out how to set the properties to
do this...
 
Hi Keith

Use the On Not In List event. The Limit To List event should be set to Yes



Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name"
strMsg = strMsg & "@Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub
 
-----Original Message-----
Hi Keith

Use the On Not In List event. The Limit To List event should be set to Yes



Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name"
strMsg = strMsg & "@Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & "@Click Yes to link or No to re- type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub
.
Click to expand...

Should I type all this in the On Not in List field?
 
Should I type all this in the On Not in List field?
Click to expand...

In the list field? No!

Open the form in design view.
Right-click on the combo box.
Select Properties.
Click on the Events tab.
On the NotInList event line, write [Event Procedure]
then click on the button with the 3 dots that will appear on that
line.
Write everything the other poster sent you that is BETWEEN the

Private Sub cbxAEName_NotInList( etc. )
and the
End Sub

When you are done, the code window should look just like the other
posters code.
Exit the code window.
Click on the property sheet Data tab
Find the Limit To List property.
Set it to Yes.
Save the changes. Run the form and try adding a new city.
 
Back
Top