Syntax question about stLinkCriteria

  • Thread starter Thread starter BrettS
  • Start date Start date
B

BrettS

I am currently using the following code for a button to go from one form to
the other, while remaining on the same Job #:

Private Sub OpenFile_Click()
On Error GoTo Err_OpenFile_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FileData"
stLinkCriteria = "[RE Job #]=" & "'" & Me![RE Job #] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenFile_Click:
Exit Sub

Err_OpenFile_Click:
MsgBox Err.Description
Resume Exit_OpenFile_Click
End Sub

I would like to do the same thing, only with not only with[RE Job #],
[Sample #] matching Me![Sample #], but I can't seem to figure out the syntax
to get it to work correctly. Something like:

Private Sub OpenFile_Click()
On Error GoTo Err_OpenFile_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FileData"
stLinkCriteria = "[RE Job #]=" & "'" & Me![RE Job #] & "'" and "[Sample
#]=" & "'" & Me![Sample #] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenFile_Click:
Exit Sub

Err_OpenFile_Click:
MsgBox Err.Description
Resume Exit_OpenFile_Click
End Sub



However, when I try this I get a "Type mismatch" error message. Anything
thanks would be appreciated.
 
It's supposed to be a valid Where clause, without the word Where in front.

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
"' and [Sample #]='" & Me![Sample #] & "'""

Exagerated for clarity, that's


stLinkCriteria = "[RE Job #]= ' " & Me![RE Job #] & _
" ' and [Sample #]= ' " & Me![Sample #] & " ' ""
 
BrettS said:
I am currently using the following code for a button to go from one
form to the other, while remaining on the same Job #:

Private Sub OpenFile_Click()
On Error GoTo Err_OpenFile_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FileData"
stLinkCriteria = "[RE Job #]=" & "'" & Me![RE Job #] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenFile_Click:
Exit Sub

Err_OpenFile_Click:
MsgBox Err.Description
Resume Exit_OpenFile_Click
End Sub

I would like to do the same thing, only with not only with[RE Job #],
[Sample #] matching Me![Sample #], but I can't seem to figure out the
syntax to get it to work correctly. Something like:

Private Sub OpenFile_Click()
On Error GoTo Err_OpenFile_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FileData"
stLinkCriteria = "[RE Job #]=" & "'" & Me![RE Job #] & "'" and
"[Sample #]=" & "'" & Me![Sample #] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenFile_Click:
Exit Sub

Err_OpenFile_Click:
MsgBox Err.Description
Resume Exit_OpenFile_Click
End Sub



However, when I try this I get a "Type mismatch" error message.
Anything thanks would be appreciated.
Click to expand...

Concatenation of strings, try

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & "' and [Sample
#]='" & Me![Sample #] & "'"

Anyway, when doing string concatenation, you should be able to do a

Debug.Print stLinkCriteria

in the code/immediate pane, and see the results in the immediate pane
(ctrl+g), which should become a valid SQL WHERE clause, without the
keyword "Where"

[RE Job #]='42' and [Sample#]='42'"

Single quotes are necessary only for criteria on fields defined as
text.
 
I would like to do the same thing, only with not only with[RE Job #],
[Sample #] matching Me![Sample #], but I can't seem to figure out the syntax
to get it to work correctly. Something like:
Click to expand...


Sorry but I completely butchered the first sentence of this part when I
first wrote this up. I meant to say, "I would like to do the same thing, but
not only with [RE Job #], but also [Sample #] matching Me![Sample #]..."
 
Thanks for your responses. Both answers were slightly different. Douglas'
still gave me a syntax error, while Roy's just brings up a "OpenForm command
was canceled" message box.
 
BrettS said:
Thanks for your responses. Both answers were slightly different.
Douglas' still gave me a syntax error, while Roy's just brings up a
"OpenForm command was canceled" message box.
Click to expand...

Drop off the last quote in Douglas Steel's suggestion, and see if that
works, if not, please post what are the datatypes of your fields.

In case the last one is numeric, you need to strip off the single
quotes

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
" and [Sample#]='" & Me![Sample #]


Perhaps also, post the result of Debug.Print stLinkCriteria

Debug.Print stLinkCriteria
 
RoyVidar said:
In case the last one is numeric, you need to strip off the single
quotes

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
" and [Sample#]='" & Me![Sample #]
Click to expand...

Oups - typo

In case the last one is numeric, you need to strip off the single
quotes

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
" and [Sample#]=" & Me![Sample #]
 
RoyVidar said:
Oups - typo

In case the last one is numeric, you need to strip off the single
quotes

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
" and [Sample#]=" & Me![Sample #]
Click to expand...



Yes - you are correct, the sample # field is numeric. When I used that
code, I get the error message:
Syntax error in string in query expression '[RE Job #]='A1226 and [Sample
#]=2'.

(With A1226 and 2 being the values in the appropriate fields.)
 
BrettS said:
RoyVidar said:
Oups - typo

In case the last one is numeric, you need to strip off the single
quotes

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
" and [Sample#]=" & Me![Sample #]
Click to expand...



Yes - you are correct, the sample # field is numeric. When I used
that code, I get the error message:
Syntax error in string in query expression '[RE Job #]='A1226 and
[Sample #]=2'.

(With A1226 and 2 being the values in the appropriate fields.)
Click to expand...

'nother typo

stLinkCriteria = "[RE Job #]='" & Me![RE Job #] & _
"' and [Sample#]=" & Me![Sample #]
 
Back
Top