Combo Box (Adding New Values from Form)

  • Thread starter Thread starter Amy Lapinsky
  • Start date Start date
A

Amy Lapinsky

I have a combo box on a form that is based on a table and
feeds the selected value into another table
(named "data"). I would like to have the end user be able
to enter a value in the combo box and have it feed to the
data table and add to the combo box as well so that it
will be in the dropdown the next time.

I have done some research on Microsoft help for this and
have found some information, but do not have all the
details I need. Apparently on the combo box properties, I
need to set the LimitToList property to "yes" and add an
event procedure or macro to the On Change event in
properties. I just do not have any details about what
action to choose in my macro.

Any ideas????

Thank you!
Amy
 
Hi Amy

Use this code, simply replace the appropriate items with yours.

If you have any problems, let me know.

Best regards

Maurice

-------------


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
 
Back
Top