Not in list 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

 

[Klatuu]

So what is it not doing?
If you are getting an error, please give the error number and description
and identifiy which line it is failing on.

If it is not throwing an error, describe what is and is not happening.

We also need your shoe size, your birthday, and your mother's maiden name

 

[allie357]

Ok, when I try to add a person not in the list the message pops up
asking if I want to add a record, then I press the yes button and it
pops again and I hit yes again. Then I get Run-Time error '2105' You
can't go to the the specified record. Then I hit the End Button and
the standard not in list error pops up.
As I said before I need this code to open the New Violator Form(which I
created already but it is not opening) and allow the usero add the Last
Name into a field and the first name into a field toadd them to the
table and requery the list to add the new addition.

So what is it not doing?
If you are getting an error, please give the error number and description
and identifiy which line it is failing on.

If it is not throwing an error, describe what is and is not happening.

We also need your shoe size, your birthday, and your mother's maiden name

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

 

[Albert D. Kallal]

Violator's Name: [Violator's Last Name] & ", " &

tbl_Violators![Violator's First Name]

I can tell you one thing, you REALLY REALLY REALLY REALLY want to avoid
spaces in field names

select LastName from tblTest

Looking at the above, is the field name called:

"lastname from tblTest",

or is the field name

"LastName"

Truly a effort in confusing people!!!! Maybe my field name really is called
"[lastname from]"

Further, you also have used ' in your field name (I did not even realize
that was possible)

select LastName from tblCustomers where city = 'Edmonton'

It should be noticed that ' (single quote) is a legal delimiter for the
above, and yet you are using it in a field name. I just can't see how you
can read your sql, and how the computer can know where a field name ends, or
begins.

Ok, now that I given you the heat on the above, you should be able to
continue to using what you have, but you do introduce the issues of
difficulty when you do the above.

Let fix this code:

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

I would check the above, and place [] around your field name..such as

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


Response = acDataErrAdded
Me.cbo.Violator_ID

What does me.cob.viloator_id do in the above? You should not need it...

 

[allie357]

I took out all spaces and the apostrophe out of the field names. How
does that change things. The Me.cbo.Violator_ID refered to the combo
box on the form that held the Violator Name.

Violator's Name: [Violator's Last Name] & ", " &
tbl_Violators![Violator's First Name]

I can tell you one thing, you REALLY REALLY REALLY REALLY want to avoid
spaces in field names

select LastName from tblTest

Looking at the above, is the field name called:

"lastname from tblTest",

or is the field name

"LastName"

Truly a effort in confusing people!!!! Maybe my field name really is called
"[lastname from]"

Further, you also have used ' in your field name (I did not even realize
that was possible)

select LastName from tblCustomers where city = 'Edmonton'

It should be noticed that ' (single quote) is a legal delimiter for the
above, and yet you are using it in a field name. I just can't see how you
can read your sql, and how the computer can know where a field name ends, or
begins.

Ok, now that I given you the heat on the above, you should be able to
continue to using what you have, but you do introduce the issues of
difficulty when you do the above.

Let fix this code:

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

I would check the above, and place [] around your field name..such as

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


Response = acDataErrAdded
Me.cbo.Violator_ID

What does me.cob.viloator_id do in the above? You should not need it...

 

[Albert D. Kallal]

I took out all spaces and the apostrophe out of the field names. How
does that change things.

Try reading my post a bit closer

I said:

Ok, now that I given you the heat on the above, you should be able to
continue to using what you have, but you do introduce the issues of
difficulty when you do the above.

I stated that you *should be able to continue* to use what you have.

I suggested that you place [] brackets around the field name, and that
should work. It might be a LOT of work, and you might have to change a LOT
of code, or forms if you change the field name. So, you *might* be stuck
with that field name, and will have to suffer the consequences of using that
field name with spaces. I kind of assumed it was clear that for the future,
as a *good* rule, you want to avoid spaces in field names, since they can be
confusing to both humans AND the computer. The solution in most cases is to
use [] brackets (in fact, your error is likely due to you forgetting to use
brackets. If you had no spaces, then likely the code would work. I was
giving you a choice here, since if you have 27,000 lines of code, and 160
forms like my last appcation, you DO NOT DARE change a field name, as it
will break 100's of things. So, you bite your lip, and stick with using []
around the field name....

The Me.cbo.Violator_ID refered to the combo

box on the form that held the Violator Name.

yes, but what are you tying to do with that line of code? I don't see why
you need it?, and thus asked what are you trying to do with that code???

If it serves no obvious purpose, then remove it. I am simply asking what you
need it there for. I can't see why it is there, but that might just be my
shortcomings. That reference should not be needed for the not in list event.
So, if either it is there for some reason that I can't see, or it is
*wrongly* is placed there in an attempted to accomplish something. I am
sure we can get to the bottom of this, but the way it looks now, you don't
need that line of code...

 

[allie357]

I did realize changing that would result in some redesign. My
application is much, much smaller than your last one so I decided it
was wise. I actually inherited this db and did not design it, so I had
accidently overlooked some of the naming conventions. I changed all
forms and reports, etc.

I took out all spaces and the apostrophe out of the field names. How
does that change things.

Try reading my post a bit closer

I said:

Ok, now that I given you the heat on the above, you should be able to
continue to using what you have, but you do introduce the issues of
difficulty when you do the above.

I stated that you *should be able to continue* to use what you have.

I suggested that you place [] brackets around the field name, and that
should work. It might be a LOT of work, and you might have to change a LOT
of code, or forms if you change the field name. So, you *might* be stuck
with that field name, and will have to suffer the consequences of using that
field name with spaces. I kind of assumed it was clear that for the future,
as a *good* rule, you want to avoid spaces in field names, since they can be
confusing to both humans AND the computer. The solution in most cases is to
use [] brackets (in fact, your error is likely due to you forgetting to use
brackets. If you had no spaces, then likely the code would work. I was
giving you a choice here, since if you have 27,000 lines of code, and 160
forms like my last appcation, you DO NOT DARE change a field name, as it
will break 100's of things. So, you bite your lip, and stick with using []
around the field name....

The Me.cbo.Violator_ID refered to the combo

box on the form that held the Violator Name.

yes, but what are you tying to do with that line of code? I don't see why
you need it?, and thus asked what are you trying to do with that code???

If it serves no obvious purpose, then remove it. I am simply asking what you
need it there for. I can't see why it is there, but that might just be my
shortcomings. That reference should not be needed for the not in list event.
So, if either it is there for some reason that I can't see, or it is
*wrongly* is placed there in an attempted to accomplish something. I am
sure we can get to the bottom of this, but the way it looks now, you don't
need that line of code...