I have a data entry form with a combo box to look up an entrant's name.
If the name is already in the table then it pulls up the record and
that part of the form works fine. If the name isn't in the table then
I want to be able to add it but this part is not working. I enter a
name that is not in the table and press Enter. I step through the code
and it appears to work until I get to "Exit Sub". At this point a
warning message box appears with the message "Characters found after
end of SQL statement." and an OK button. When I click OK another
message box pops up with the message "The text you entered isn't an
item in the list. Select an item from the list, or enter text that
matches one of the listed items."; also, with an OK button. I click on
it and the yellow hi-lite goes away and nothing else occurs. I go to
the form itself and the combo box has the focus but the form is not on
a new record. If I press Enter the process will repeat itself. I know
I could already be finished if I had made it an unbound form and used
code but from some of the posts I have read on the bound vs. unbound
issue I wanted to learn how to do this the bound way. That way I could
have flexibility in what approach to take in the future. Below is my
code for the "beforeupda te" and "notinlist" events (once I had it
working I was going to rename my controls). I would appreciate it if
someone could tell me what I am doing wrong and how to correct it.
Private Sub Combo23_BeforeU pdate(Cancel As Integer)
On Error GoTo Err_Routine
'variable captures procedure name that error occurs in
strProcName = "Combo23_Before Update"
Me.RecordsetClo ne.FindFirst "[EntryName] = '" & Me![Combo23] & "'"
Me.Bookmark = Me.RecordsetClo ne.Bookmark
Exit_Routine:
Exit Sub
Err_Routine:
Select Case Err
Case Else
Err_General
End Select
Resume Exit_Routine
End Sub
Private Sub Combo23_NotInLi st(NewData As String, Response As Integer)
On Error GoTo Err_Routine
'variable captures procedure name that error occurs in
strProcName = "Combo23_NotInL ist"
Dim ctl As Control
' Return Control object that points to combo box.
Set ctl = Me!Combo23
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctl.Undo
End If
Exit_Routine:
Exit Sub
Err_Routine:
Select Case Err
Case Else
Err_General
End Select
Resume Exit_Routine
End Sub
Thanks for any assistance,
Alex
