Re: Need to use 2 fields in link criteria

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Bonnie (Hambrick_at_discussions.microsoft.com)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 11:49:03 -0700

Wow...it starts to make sense. (And, it works great!) It
threw me cause 2 & 3 weren't 'grouped' but 4-6 is like a
separate group not really linked to the first group
(single criteria).

I've already gotten brave and tried to use it to satisfy
a parameter but get 'too few parameters - expected 2'
error. (I have [EntryOrder] and [SSN] in my query's
criteria lines. If I remove them, I get the item not
found in this collection error. What am I doing wrong? I
figure there must be a shift in the logic somewhere...

Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qCopyRecordToArchiveTable")

    With qdf
    'resolve the parameter
    .Parameters("EntryOrder") = [EntryOrder] & " and SSN
=""" & Me.SSN & """"
    .Execute
    MsgBox "You appended " & .RecordsAffected & " record!
Decide now if you need to also delete the record from the
current form."
    End With

    Set qdf = Nothing
    Set db = Nothing

Thank you TRULY for your help on this, you have been a
font on information today. You have always been one of my
favorite MVP's to read.

>-----Original Message-----
>I meant to also say that all of that was simply to get
the final closing
>quote around SSN so that the full SQL string has the SSN
as a literal
>string.
>
>If you break down the whole thing, the pieces are:
>
>1) stLinkCriteria =
>2) "EntryNum="
>3) Me![EntryNum]
>4) " AND SSN="""
>5) me.SSN
>6) """"
>
>Look at the 4th line and you'll see that there are also
4 quotes there but
>it is more evident that the first and fourth are VBA
string delimiters since
>there is intervening text within the quotes.
>
>Hope this helps!
>
>--
>Sandra Daigle [Microsoft Access MVP]
>Please post all replies to the newsgroup.
>
>
>Sandra Daigle wrote:
>> Hi Bonnie,
>>
>> The two sets after SSN are actually the following:
>>
>> - First quote - VBA String Delimiter
>>
>> - Second and Third quotes - Doubled up to convert to
single to leave
>> in final string
>>
>> - Fourth quote - VBA String Delimiter
>>
>> It looks like a mess but just like with parentheses in
a math
>> equation, you eventually get the hang of it and learn
to work with it
>> in small pieces first then put it all together.
>>
>>
>> Bonnie wrote:
>>> Sandra,
>>>
>>> Thank you for the wordage and a special thanks for the
>>> explanation you included. It was so well put that
light
>>> bulbs came on!!! I think I'm beginning to understand.
I
>>> am confused on one point: Why add one set of double
>>> quotes before & me.SSN & and two sets after? (Yes, my
SSN
>>> field is text to avoid losing leading zeros so I need
the
>>> extra quotes.) Thanks again.
>>>
>>>> -----Original Message-----
>>>> Hi Bonnie,
>>>>
>>>> Basically you just the second condition just like
the first except
>>>> that you need to join the two conditions with the
AND operator.
>>>>
>>>> stLinkCriteria = "[EntryNum]=" & Me![EntryNum] & "
AND SSN=" &
>>>> me.SSN
>>>>
>>>> Note that the AND is inside of a VB string and the
reference to the
>>>> SSN control is outside of the string to be resolved
by VBA. Also, if
>>>> SSN is text don't forget to wrap the test value in
quotes by adding
>>>> a pair of double quotes on either side of the test
value:
>>>>
>>>> stLinkCriteria = "EntryNum=" & Me![EntryNum] & " AND
SSN=""" &
>>>> me.SSN & """"
>>>>
>>>> FWIW the brackets around fieldnames are not
necessary unless the
>>>> fieldname contains embedded spaces or special
characters. Yours do
>>>> not (congratulations!) and you can therefore omit
the brackets.
>>>>
>>>>
>>>> --
>>>> Sandra Daigle [Microsoft Access MVP]
>>>> Please post all replies to the newsgroup.
>>>>
>>>>
>>>> Bonnie wrote:
>>>>> Hello all! Using A02 on XP. Recently learned how to
use
>>>>> linking criteria. LUV IT! How can I make the one
below
>>>>> link to [EntryNum] AND [SSN]? Can't quite figure
out the
>>>>> quotes and such in the stLinkCriteria line. That's
the
>>>>> only place to make the changes, right?
>>>>>
>>>>> Private Sub Address_Click()
>>>>> On Error GoTo Err_Address_Click
>>>>>
>>>>> Dim stDocName As String
>>>>> Dim stLinkCriteria As String
>>>>>
>>>>> stDocName = "DistribArchivedForm-Sub2-Addr"
>>>>>
>>>>> stLinkCriteria = "[EntryNum]=" & Me![EntryNum]
>>>>> DoCmd.OpenForm stDocName, , , stLinkCriteria
>>>>>
>>>>> Exit_Address_Click:
>>>>> Exit Sub
>>>>>
>>>>> Err_Address_Click:
>>>>> MsgBox Err.Description
>>>>> Resume Exit_Address_Click
>>>>>
>>>>> End Sub
>>>>>
>>>>> Thanks in advance for any help or advice!
>>>>
>>>>
>>>> .
>
>
>.
>

Relevant Pages

  • Re: DLookUp Question
    ... in the subform that's bound to the Employee_SSN field. ... >>>MsgBox "That SSN is already in the DataBase. ... >>line continuation sequence: blank-underscore. ... >>quotes in the string and add the ouside quotes. ...
    (microsoft.public.access.formscoding)
  • Re: DLookUp Question
    ... >MsgBox "That SSN is already in the DataBase. ... The steps I use to get the nested quotes straight is to ... >>Bonnie wrote: ... >>If the ssn field is a numeric type field, ...
    (microsoft.public.access.formscoding)
  • Re: Need to use 2 fields in link criteria
    ... quotes before & me.SSN & and two sets after? ... (Yes, my SSN ... >control is outside of the string to be resolved by VBA. ... >> Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: Need to use 2 fields in link criteria
    ... quote around SSN so that the full SQL string has the SSN as a literal ... there is intervening text within the quotes. ... > Hi Bonnie, ...
    (microsoft.public.access.formscoding)