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!
.
.
