Re: DLookUp Question
From: Marshall Barton (marshbarton_at_wowway.com)
Date: 02/13/04
- Next message: Marshall Barton: "Re: Disable color"
- Previous message: Betacon: "Re: After return to same form Access thinks I am 2nd user"
- In reply to: Bonnie: "Re: DLookUp Question"
- Next in thread: Bonnie: "Re: DLookUp Question"
- Reply: Bonnie: "Re: DLookUp Question"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Feb 2004 12:00:15 -0600
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]
>>.
>>
- Next message: Marshall Barton: "Re: Disable color"
- Previous message: Betacon: "Re: After return to same form Access thinks I am 2nd user"
- In reply to: Bonnie: "Re: DLookUp Question"
- Next in thread: Bonnie: "Re: DLookUp Question"
- Reply: Bonnie: "Re: DLookUp Question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
