Syntax error in query expression

[Guest]

I created a command button that allows the users to go to the general ledger
form for a specific check ID #. It works for records that beginnings with an
alphabet in the pub ID field but doesn't work in the records that beginning
with a numerial in the pub ID field.
This is the error message I receive: Syntax error (missing operator) in
query expression '[CheckID]=4CLJ'.

Here is the code:

Private Sub Command198_Click()
On Error GoTo Err_Command198_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "General Ledger"

stLinkCriteria = "[CheckID]=" & Me![PubID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command198_Click:
Exit Sub

Err_Command198_Click:
MsgBox Err.Description
Resume Exit_Command198_Click

End Sub

Can someone tell me what I need to change in order for this command to work?

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?

 

[Stuart McCall]

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'

 

[Guest]

Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'

 

[Stuart McCall]

Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was
cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote,
then
at the end of the line type another apostrophe surrounded by double
quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it
looks
like:

[CheckID]='4CLJ'

In that case the problem lies elsewhere, most likely in the Load event of
your 'General Ledger' form.

 

[Guest]

Hi Carmen,

the field CheckID exists in the recordsource of the form you're trying to
open?

Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'

 

[Guest]

It's actually a subform in my main contact form.

Hi Carmen,

the field CheckID exists in the recordsource of the form you're trying to
open?

Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'

 

[Guest]

The general ledger is actually a subform in my main contact form. Could that
be the reason why it doesn't work?

Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was
cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote,
then
at the end of the line type another apostrophe surrounded by double
quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it
looks
like:

[CheckID]='4CLJ'

In that case the problem lies elsewhere, most likely in the Load event of
your 'General Ledger' form.

 

[Stuart McCall]

The general ledger is actually a subform in my main contact form. Could
that
be the reason why it doesn't work?

Very possible. You're attempting to open a form that is already open. AFAIK
Access sometimes allows this by opening a new instance, but this is not
happening in your case. Is there any code in 'General Ledger's Open or Load
events?