The following code would be a good starting point, I've marked the areas that
you'll need to modify. The code allows the user to enter a value not in the
displayed list and advance to that record. In this implementation, the value
entered corresponds to the bound field. You can just as easily look up a
value in another field and get the key using DLookup().
Private Sub cboSelectShow_NotInList(NewData As String, Response As Integer)
'Code which selects the show based on show number is of my own doing
'Code which adds a new record curtesy of MS.
'Since the .LimitToList = True, this code allows the user to enter a job
number and locate the corresponding show from there
'Although the txtShowNumber is the first field in the combo list and the
bound field, we have to retreive the show name from
'the underlying table in order to move the list to that specific record
Dim strMsgText As String
Dim varResult As Variant
Dim rs As DAO.Recordset
Dim strShowName As String
On Error GoTo Err_cboSelectShow_NotInList
If NewData = "" Then Exit Sub
'If the value entered matches a record in the database, set the comboBox
to the
'value entered and tell Access that no error occurred. If we weren't
dealing with
'the bound value, we could obtain it using a DLookup(). For example, if
I entered
'DCH, we could use 'DCH' to lookup my employee number.
If ECount("txtShowName", "tblShowInformation", "txtShowNumber = '" &
NewData & "'") > 0 Then
Me.cboSelectShow = NewData
Response = acDataErrContinue
Me.cboSortOrder.SetFocus
Call cboSelectShow_AfterUpdate
Exit Sub
End If
'You'll probably add code here to search the alternate table. If the value
is found,
'change the control source on the comboBox, requery it to load the values,
set the
'value, and then use Response = acDataErrContinue. You'll probably want to
display
'a message that the comboBox control is now pointing to an alternate
dataSource.
strMsgText = ""
strMsgText = strMsgText & "The show number or name has not been set-up
in the database" & Chr(13) & Chr(13)
strMsgText = strMsgText & "Do you want to add the show?"
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
DoCmd.Echo False
DoCmd.OpenForm "frmShowInformationDetail", , , , acFormAdd, acDialog,
NewData
DoCmd.Echo True
varResult = ECount("txtShowName", "tblShowInformation", "txtShowNumber =
'" & NewData & "'")
If IsNull(varResult) Then
' If the show was not added set the Response argument to suppress an
error message and undo changes.
strMsgText = "Please select a show from the list."
MsgBox strMsgText, vbInformation
Response = acDataErrContinue
Else
If detectDataType(NewData) = "number" Then
'Clears the value typed
Me.cboSelectShow.Undo
'Requeries to ensure the new record is present
Me.cboSelectShow.Requery
'Sets the value
Me.cboSelectShow = NewData
'Clears the error
Response = acDataErrContinue
'Closes the drop down list if viewed
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 show number is entered
Me.cboSortOrder.SetFocus
End If
Call cboSelectShow_AfterUpdate
End If
Exit_cboSelectShow_NotInList:
Exit Sub
Err_cboSelectShow_NotInList:
MsgBox getDefaultErrorMessage(Me.Name, "cboSelectShow_NotInList",
Err.Number, AccessError(Err.Number)), vbCritical
Resume Exit_cboSelectShow_NotInList
End Sub
