Ok, I can't really improve on the example, but hopefully I can explain it a
little.
If you set the LimitToList property of the Combo Box to Yes, then the combo
box will cause the NotInList event to run if you try to type in something
that's not in the list. This gives you the opportunity to programmatically
add the item to the table (or query based on a table) that the combo box
gets its Row Source from. You start by asking the user if they really
intended what they entered (it could be a typo).
If the user says Yes, then you add the item to the table and tell the combo
box that an item has been added and it needs to go back and get the new
list. This is done by using the Response=acDataErrAdded line. acDataErrAdded
is really just a built-in constant with a value of 2. However, it is easier
for the programmer to remember "Data Added" than that 2 means data has been
added. Access will accept either one as a valid answer.
If the user replies No (it was a typo) then you undo what the user did and
tell Access to "never mind" by using Response=acDataErrContinue.
Taking the code as listed, I will add some comments to show you where the
above description is being applied. Comment lines start with ' and are
ignored by VBA.
'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
'The above line is the name of the NotInList event for the combo box
'called cbxAEName. NewData is what the user typed in that
'wasn't in the list. Response is how you tell Access what to
'do when you are done here.
'This next part just lists the variables we are going to use
'in this procedure and describes what type of variables they are
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
'These 3 lines create the message that we are going to ask the user when
'we pop-up a message box asking them if they really intended to enter
'what they did or if it was a typo.
strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."
'This give us the message box with the message we created above and 2
buttons,
' a Yes button and a No button. If you user respondes No (as indicated by
the vbNo)
'then we tell Access "never mind" by setting Response to acDataErrContinue
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
'I usually add Me.cbxAEName.Undo here as well to undo what was typed
'by the user. However, you can just let the user fix it themselve also, as
is
'being done here.
Response = acDataErrContinue
Else
'The Else part is what we do if the user didn't say No. In this case, since
there were
'only 2 choices, this means the user said Yes, add the new item. The Set db
and
'Set rs statements are DAO statements that open the recodset (table) that
contains
'the Row Source data so that we can add the item to it. The name of the
table
'is tblAE.
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
'These next 3 lines are what actually add the data. The line above
'tells VBA that if there is an error doing this, ignore it for now.
rs.AddNew
rs!AEName = NewData
rs.Update
'This part goes back and checks to see if there was an error and
'alerts the user if there was and tells Access to ignore the new
'value, just as we did above when the user said No.
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
'If there was no error, then we tell Access that the new value was added,
'go get it and show it in the list.
Response = acDataErrAdded
End If
'The 3 lines below "clean up" the object variables we Set and Opened above.
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************
-- Wayne MorganMS Access MVP
