DLookUp Question

[Bonnie]

Using AXP. Need my subform to tell me on the BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox "That SSN is already in the DataBase. Are you
sure you need to create another record? Press the Escape
key to exit this new record and check existing records
before creating another."
Cancel = True
End If

I'm getting a 'syntax error (missing operator)on
Employee_SSN = Forms!Distribution Form-Main!Distribution
Form-Sub1-Base!txtEmployee_SSN'
I know it is something simple and derives from the fact
that I have spaces in my field and form names.

My field is Employee SSN
My table is Distribution Table
My mainform is Distribution Form-Main
My subform is Distribution Form-Sub1-Base

I received the code from Arvin Meyer who was so nice to
help me but it was days ago and I don't know if he has the
thread open to help me again on this and I need to figure
it out. I wasn't sure why the letters 'txt' are in front
of the field name but took it on faith. Also, what does
the Cancel=True do? Not allow the new entry? We rarely
have 2 distributions for one SSN but need to allow for it.

I hope someone out there is bored enough to want to help a
non-programmer with this. Thanks in advance for any help
or advice.

 

[Marshall Barton]

Using AXP. Need my subform to tell me on the BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox

Unlike in a query, a DLookup function (or in a VBA
procedure), you need to use the value of ssn, not a
reference to to it. Also, names with spaces or other funky
characters have to be enclosed in [ ]. Try this:

If the ssn field is a numeric type field, use

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = " & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN
"))) Then

Or if ssn is a Text type field

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = """ & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN " &
""""))) Then

 

[Bonnie]

Sorry it took me some time to get to your response. My SSN
field is a text field. I tried your code below and I'm
getting a Compile or Syntax Error. The ()'s look okay, is
there something wrong with the ""'s?

I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table","Employee_SSN = """ & Forms![Distribution Form-
Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN "
&"""")))Then
MsgBox "That SSN is already in the DataBase. Are you sure
you need to create another record? Press the Escape key to
exit this new record and check existing records before
creating another."
Cancel = True
End If


I made sure to remove the returns so it is a one-line
statement. Sorry to ask for more help, but this is
something I am trying to learn in VB and the nesting is
very confusing until the light bulb turns on.

Thanks again and I'll hope to see another reply from you.

-----Original Message-----

Using AXP. Need my subform to tell me on the BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox

Unlike in a query, a DLookup function (or in a VBA
procedure), you need to use the value of ssn, not a
reference to to it. Also, names with spaces or other funky
characters have to be enclosed in [ ]. Try this:

If the ssn field is a numeric type field, use

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = " & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN
"))) Then

Or if ssn is a Text type field

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = """ & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]! txtEmployee_SSN " &
""""))) Then

 

[Marshall Barton]

Sorry it took me some time to get to your response. My SSN
field is a text field. I tried your code below and I'm
getting a Compile or Syntax Error. The ()'s look okay, is
there something wrong with the ""'s?

I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table","Employee_SSN = """ & Forms![Distribution Form-
Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN "
&"""")))Then
MsgBox "That SSN is already in the DataBase. Are you sure
you need to create another record? Press the Escape key to
exit this new record and check existing records before
creating another."
Cancel = True
End If

It's a little hard to tell with the line wrapping done by
the mail programs, but I see an extra quote above. To avoid
those ridiculously long lines in your code, you can use the
line continuation sequence: blank-underscore. You should
be able to just copy/paste this code:

If Not IsNull(DLookup("Employee_SSN", _
"Distribution Table","Employee_SSN = """ _
& Forms![Distribution Form-Main]! _
[Distribution Form-Sub1-Base]!txtEmployee_SSN _
& """")) Then

I made sure to remove the returns so it is a one-line
statement. Sorry to ask for more help, but this is
something I am trying to learn in VB and the nesting is
very confusing until the light bulb turns on.

The steps I use to get the nested quotes straight is to
enter the string as I want it to end up then double up any
quotes in the string and add the ouside quotes. Then I add
the line continuation sequence in strategic locations so I
don't have to add any more quotes. It's kind of like waving
a magic wand over a brewing potion while adding hair of newt
under a full moon - works every time ;-)

COoomme Oonnn light bulb, whack, bang, thump . . .

Did that help ;-)
--
Marsh
MVP [MS Access]

-----Original Message-----

Using AXP. Need my subform to tell me on the BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox

Unlike in a query, a DLookup function (or in a VBA
procedure), you need to use the value of ssn, not a
reference to to it. Also, names with spaces or other funky
characters have to be enclosed in [ ]. Try this:

If the ssn field is a numeric type field, use

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = " & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN
"))) Then

Or if ssn is a Text type field

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = """ & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]! txtEmployee_SSN " &
""""))) Then

 

[Bonnie]

Thanks bunches for replying so quickly, I was able to
compile but now am getting Error 2465: DistribLog can't
find the field 'txtEmployee_SSN' referred to in your
expression. I suspect I need to define it somewhere?
Sorry, the juice is there but my bulb is sputtering like a
candle in the wind. This is new for me and I'm anxious to
make the leap into making my forms check requirements and
make suggestions. Thanks for the whimsey you added but I'm
pretty much muttering over the brew and learning bits and
pieces as the frogs jump out and I run autopsies on the
results of someone else's work. Ribbit...Why isn't there a
book for the transition from design to DESIGN? Again, I
truly appreciate your help and don't mean to tax your
patience but I'm sooooo close to getting this to work.

-----Original Message-----

Sorry it took me some time to get to your response. My SSN
field is a text field. I tried your code below and I'm
getting a Compile or Syntax Error. The ()'s look okay, is
there something wrong with the ""'s?

I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table","Employee_SSN = """ & Forms![Distribution Form-
Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN "
&"""")))Then
MsgBox "That SSN is already in the DataBase. Are you sure
you need to create another record? Press the Escape key to
exit this new record and check existing records before
creating another."
Cancel = True
End If

It's a little hard to tell with the line wrapping done by
the mail programs, but I see an extra quote above. To avoid
those ridiculously long lines in your code, you can use the
line continuation sequence: blank-underscore. You should
be able to just copy/paste this code:

If Not IsNull(DLookup("Employee_SSN", _
"Distribution Table","Employee_SSN = """ _
& Forms![Distribution Form-Main]! _
[Distribution Form-Sub1-Base]!txtEmployee_SSN _
& """")) Then

I made sure to remove the returns so it is a one-line
statement. Sorry to ask for more help, but this is
something I am trying to learn in VB and the nesting is
very confusing until the light bulb turns on.

The steps I use to get the nested quotes straight is to
enter the string as I want it to end up then double up any
quotes in the string and add the ouside quotes. Then I add
the line continuation sequence in strategic locations so I
don't have to add any more quotes. It's kind of like waving
a magic wand over a brewing potion while adding hair of newt
under a full moon - works every time ;-)

COoomme Oonnn light bulb, whack, bang, thump . . .

Did that help ;-)
--
Marsh
MVP [MS Access]

-----Original Message-----
Bonnie wrote:

Using AXP. Need my subform to tell me on the BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup ("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox

Unlike in a query, a DLookup function (or in a VBA
procedure), you need to use the value of ssn, not a
reference to to it. Also, names with spaces or other funky
characters have to be enclosed in [ ]. Try this:

If the ssn field is a numeric type field, use

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = " & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN
"))) Then

Or if ssn is a Text type field

If (Not IsNull(DLookup("Employee_SSN", "Distribution Table",
"Employee_SSN = """ & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]! txtEmployee_SSN " &
""""))) Then

.

 

[Marshall Barton]

txtEmployee_SSN is supposed to be the name of the text box
in the subform that's bound to the Employee_SSN field.
Check to make sure the name of the text box and the control
name in the DLookup are the same.

Sorry, I forgot to say abracadabra backwards during that
incantantion. I don't know what it will look like by the
time you look at it, but the part

Forms![Distribution Form-Main]![Distribution
Form-Sub1-Base]!txtEmployee_SSN

must be all on one line.
--
Marsh
MVP [MS Access]

Thanks bunches for replying so quickly, I was able to
compile but now am getting Error 2465: DistribLog can't
find the field 'txtEmployee_SSN' referred to in your
expression. I suspect I need to define it somewhere?
Sorry, the juice is there but my bulb is sputtering like a
candle in the wind. This is new for me and I'm anxious to
make the leap into making my forms check requirements and
make suggestions. Thanks for the whimsey you added but I'm
pretty much muttering over the brew and learning bits and
pieces as the frogs jump out and I run autopsies on the
results of someone else's work. Ribbit...Why isn't there a
book for the transition from design to DESIGN? Again, I
truly appreciate your help and don't mean to tax your
patience but I'm sooooo close to getting this to work.

-----Original Message-----

Sorry it took me some time to get to your response. My SSN
field is a text field. I tried your code below and I'm
getting a Compile or Syntax Error. The ()'s look okay, is
there something wrong with the ""'s?

I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table","Employee_SSN = """ & Forms![Distribution Form-
Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN "
&"""")))Then
MsgBox "That SSN is already in the DataBase. Are you sure
you need to create another record? Press the Escape key to
exit this new record and check existing records before
creating another."
Cancel = True
End If

It's a little hard to tell with the line wrapping done by
the mail programs, but I see an extra quote above. To avoid
those ridiculously long lines in your code, you can use the
line continuation sequence: blank-underscore. You should
be able to just copy/paste this code:

If Not IsNull(DLookup("Employee_SSN", _
"Distribution Table","Employee_SSN = """ _
& Forms![Distribution Form-Main]! _
[Distribution Form-Sub1-Base]!txtEmployee_SSN _
& """")) Then

I made sure to remove the returns so it is a one-line
statement. Sorry to ask for more help, but this is
something I am trying to learn in VB and the nesting is
very confusing until the light bulb turns on.

The steps I use to get the nested quotes straight is to
enter the string as I want it to end up then double up any
quotes in the string and add the ouside quotes. Then I add
the line continuation sequence in strategic locations so I
don't have to add any more quotes. It's kind of like waving
a magic wand over a brewing potion while adding hair of newt
under a full moon - works every time ;-)

COoomme Oonnn light bulb, whack, bang, thump . . .

Did that help ;-)
--
Marsh
MVP [MS Access]

-----Original Message-----
Bonnie wrote:

Using AXP. Need my subform to tell me on the
BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup ("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox

Unlike in a query, a DLookup function (or in a VBA
procedure), you need to use the value of ssn, not a
reference to to it. Also, names with spaces or other
funky
characters have to be enclosed in [ ]. Try this:

If the ssn field is a numeric type field, use

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table",
"Employee_SSN = " & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN
"))) Then

Or if ssn is a Text type field

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table",
"Employee_SSN = """ & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!
txtEmployee_SSN " &
""""))) Then

.

 

[Bonnie]

IT WORKS!!!!! Do you understand the simple joy a novice
feels when they step beyond their means but figure out
WHAT they're reading and know WHY it's written like
that??? I put a field on my form named txtEmployeeSSN (and
remembered to remove the _ from the code name) and IT
WORKS!!! Thanks VERY much for sticking with this thread.

-----Original Message-----
txtEmployee_SSN is supposed to be the name of the text box
in the subform that's bound to the Employee_SSN field.
Check to make sure the name of the text box and the control
name in the DLookup are the same.

Sorry, I forgot to say abracadabra backwards during that
incantantion. I don't know what it will look like by the
time you look at it, but the part

Forms![Distribution Form-Main]![Distribution
Form-Sub1-Base]!txtEmployee_SSN

must be all on one line.
--
Marsh
MVP [MS Access]

Thanks bunches for replying so quickly, I was able to
compile but now am getting Error 2465: DistribLog can't
find the field 'txtEmployee_SSN' referred to in your
expression. I suspect I need to define it somewhere?
Sorry, the juice is there but my bulb is sputtering like a
candle in the wind. This is new for me and I'm anxious to
make the leap into making my forms check requirements and
make suggestions. Thanks for the whimsey you added but I'm
pretty much muttering over the brew and learning bits and
pieces as the frogs jump out and I run autopsies on the
results of someone else's work. Ribbit...Why isn't there a
book for the transition from design to DESIGN? Again, I
truly appreciate your help and don't mean to tax your
patience but I'm sooooo close to getting this to work.

-----Original Message-----
Bonnie wrote:

Sorry it took me some time to get to your response. My SSN
field is a text field. I tried your code below and I'm
getting a Compile or Syntax Error. The ()'s look okay, is
there something wrong with the ""'s?

I wrote:

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table","Employee_SSN = """ & Forms![Distribution Form-
Main]![Distribution Form-Sub1-Base]!txtEmployee_SSN "
&"""")))Then
MsgBox "That SSN is already in the DataBase. Are you sure
you need to create another record? Press the Escape

key
to

exit this new record and check existing records before
creating another."
Cancel = True
End If

It's a little hard to tell with the line wrapping done by
the mail programs, but I see an extra quote above. To avoid
those ridiculously long lines in your code, you can use the
line continuation sequence: blank-underscore. You should
be able to just copy/paste this code:

If Not IsNull(DLookup("Employee_SSN", _
"Distribution Table","Employee_SSN = """ _
& Forms![Distribution Form-Main]! _
[Distribution Form-Sub1-Base]!txtEmployee_SSN _
& """")) Then


I made sure to remove the returns so it is a one-line
statement. Sorry to ask for more help, but this is
something I am trying to learn in VB and the nesting is
very confusing until the light bulb turns on.

The steps I use to get the nested quotes straight is to
enter the string as I want it to end up then double up any
quotes in the string and add the ouside quotes. Then I add
the line continuation sequence in strategic locations so I
don't have to add any more quotes. It's kind of like waving
a magic wand over a brewing potion while adding hair of newt
under a full moon - works every time ;-)

COoomme Oonnn light bulb, whack, bang, thump . . .

Did that help ;-)
--
Marsh
MVP [MS Access]



-----Original Message-----
Bonnie wrote:

Using AXP. Need my subform to tell me on the
BeforeUpdate
event of SSN field if it already exists. I wrote:

If (Not IsNull(DLookup ("Employee_SSN", "Distribution
Table", "Employee_SSN = Forms!Distribution Form-Main!
Distribution Form-Sub1-Base!txtEmployee_SSN "))) Then
MsgBox

Unlike in a query, a DLookup function (or in a VBA
procedure), you need to use the value of ssn, not a
reference to to it. Also, names with spaces or other
funky
characters have to be enclosed in [ ]. Try this:

If the ssn field is a numeric type field, use

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table",
"Employee_SSN = " & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]! txtEmployee_SSN
"))) Then

Or if ssn is a Text type field

If (Not IsNull(DLookup("Employee_SSN", "Distribution
Table",
"Employee_SSN = """ & Forms![Distribution
Form-Main]![Distribution Form-Sub1-Base]!
txtEmployee_SSN " &
""""))) Then
--
Marsh
MVP [MS Access]
.


.

.

 

[Marshall Barton]

IT WORKS!!!!! Do you understand the simple joy a novice
feels when they step beyond their means but figure out
WHAT they're reading and know WHY it's written like
that??? I put a field on my form named txtEmployeeSSN (and
remembered to remove the _ from the code name) and IT
WORKS!!! Thanks VERY much for sticking with this thread.

We are all novices at the beginning of any new endeavor ;-)

Glad to hear that you got it working. Even gladder(?) that
you understand how you got there. It'll get easier as you
go forward.

Good luck,