Not in List Event Problem

[allie357]

Here is the code I have for my not in list event:
It is giving me an error at the If IsNull(DLookup) line. My combo box
runs of a query that pulls the Violator's Last Name and First Name from
the Violator's table and displays them Violator's Last Name, Violator's
Last Name
with
Violator's Name: [Violator's Last Name] & ", " &
tbl_Violators![Violator's First Name]

Now I need this code to open the New Violator Form and allow the user
to add the Last Name into a field and the first name into a field to
add them to the table and requery the list to add the new addition. My
code is not working now. Please help.

Private Sub Combo111_NotInList(NewData As String, Response As
Integer)
If MsgBox("That violator is not in the list." & _
"Would you like to add a new violator?", vbYesNo) = vbYes
Then
' Open the form to add the new violator.
DoCmd.OpenForm "frmNewViolator", , , , acFormAdd, acDialog,
NewData

If IsNull(DLookup("Violator_ID", "tblViolators", _
"Violator's Last Name = """ & NewData & """")) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.cbo.Violator_ID
End If
Else
Response = acDataErrContinue
End If
End Sub

 

[Guest]

Wrap the field name in brackets:

If IsNull(DLookup("Violator_ID", "tblViolators", _
"[Violator's Last Name] = """ & NewData & """")) Then

Ken Sheridan
Stafford, England

 

[Jerry Porter]

Allie,

A better name for the field would be ViolatorLastName, or just
LastName. Easier to type, and less chance of error, since the brackets
aren't required if there are no spaces or non-standard characters in
the field name.

You have another line that doesn't look valid to me:

Me.cbo.Violator_ID

If you want to select the new record, then you should restructure that
section to get the new ID and assign it to the combo box:

Dim vNewViolator_ID as variant 'put this at top of procedure
...

vNewViolater_ID = DLookup("Violator_ID", "tblViolators", _
"[Violator's Last Name] = """ & NewData & """")
If IsNull(vNewViolater_ID ) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me!Combo111 = vNewViolater_ID
End If
...

Jerry