Here's something to get you started. You WILL have to modify it to reflect
the specific objects in your database. If you are having problems, then post
the code back and let us know what's going on. The code includes logic which
allows the user to enter an alternate identity to select the item. For
example, if the comboBox is a list of Airport names, you can enter the full
airport name or the IATA code for it (assuming that the IATA code is in the
underlying table). Or if you're dealing with states where the full state name
is entered, the abbreviation can be used. 'ME' selects 'Maine'. In the
example below, the database displays PROJECT NAMES but gives the person the
option of entering the PROJECT NUMBER. Since the Project Number is
technically not in the list, the NotInList event fires, but checks to see if
the Project Number exists in the underlying table and if so finds the
corresponding Project Name. (One of my niftier little tricks.)
Private Sub cboSelectProject_NotInList(NewData As String, Response As Integer)
'Code which selects the project based on the project number is of my own
doing
'Code which adds a new record courtesy of Microsoft.
'Since the .LimitToList = True, this code allows the user to enter a
project number and locate the corresponding project from there
'Although the txtProjectNumber is the first field in the combo list and
the bound field, we have to retreive the project name from
'the underlying table in order to move the list to that specific record
Dim strMsgText As String
Dim varResult As Variant
On Error GoTo Err_cboSelectProject_NotInList
'Exit out if for some reason NewData is zero-length, if it is the
ECount() will crap out
If NewData = "" Then Exit Sub
'Check the table to see if we can find the Project Name using the value
that was entered. ECount() is from mvps.org, you can subsitute DCount().
If ECount("txtProjectName", "tblProjectInformation", "txtProjectNumber =
'" & NewData & "'") > 0 Then
'Here we're setting the value of the comobBox to the Project Number
entered as the Project Number is the value that is actually stored. The
comboBox only displays Project Names hence its looking for a Name to be
entered not a number.
Me.cboSelectProject = NewData
Response = acDataErrContinue
Me.cboSortOrder.SetFocus
Exit Sub
End If
'Since we couldn't find the Project Name via the value entered, see if
we need to add it
strMsgText = ""
strMsgText = strMsgText & "The project number or name has not been
set-up in the database" & Chr(13) & Chr(13)
strMsgText = strMsgText & "Do you want to add the project?"
If MsgBox(strMsgText, vbInformation + vbYesNo + vbDefaultButton2) = vbNo
Then
strMsgText = "Please select a show from the list."
MsgBox strMsgText, vbInformation
Response = acDataErrContinue
Exit Sub
End If
'If we're going to add the project, open the form to let it be added.
NewData is passed as the OpenArguement for the form so that we can reuse the
value via code in the forms OpenEvent. Its not neccessary to do this, but it
saves the user from having to reenter the information again.
DoCmd.Echo False
DoCmd.OpenForm "frmProjectInformationDetail", , , , acFormAdd, acDialog,
NewData
DoCmd.Echo True
varResult = ECount("txtProjectName", "tblProjectInformation",
"txtProjectNumber = '" & NewData & "'")
If IsNull(varResult) Then
' If the Project was not added set the Response argument to suppress
an error message and undo changes.
strMsgText = "Please select a project from the list."
MsgBox strMsgText, vbInformation
Response = acDataErrContinue
Else
'Since the user may have entered the Project Number or Project Name
it was neccessary to change how we deal with the requery of the comboBox.
detectDataType() is a function that guesses which was entered based on rules
for the Project Name and Project Number. For this situation the Project
Number can contain letters but would never be more than 9 characters in
length and would never contain spaces or punctuation.
If detectDataType(NewData) = "number" Then
'Clears the value typed
Me.cboSelectProject.Undo
'Requeries to ensure the new record is present
Me.cboSelectProject.Requery
'Sets the value
Me.cboSelectProject = NewData
'Clears the error
Response = acDataErrContinue
'Closes the drop down list if viewed
'This just moves the focus to the next field in the tab order
Me.cboSortOrder.SetFocus
End If
If detectDataType(NewData) = "name" Then
Response = acDataErrAdded
'Shift the focus for cosmetic reasons to give the same effect
when the project number is entered
Me.cboSortOrder.SetFocus
End If
End If
Exit_cboSelectProject_NotInList:
Exit Sub
Err_cboSelectProject_NotInList:
MsgBox getDefaultErrorMessage(Me.Name, "cboSelectProject_NotInList",
Err.Number, AccessError(Err.Number)), vbCritical
Resume Exit_cboSelectProject_NotInList
End Sub
