Query works, but SQL statment doesn't when pasted into a procedure

Liz Malcolm

Hello and Thanks in advance for any help. I am using Access 2000.

I have a data entry form that opens a main form (using the On Click
event of the combo box [cboDESeledct]) with tab controls and 1 subform
on the last tab control. The main form is opened using a where clause
to restrict the contact type. The subform is built on a separate
table that lists other contact types that the contact could be a
member of. Example a volunteer could also be a donor or a vendor,
etc. I would like to open the form where the contact type in either
the main form or the subform is the same.

I can get correct results using a query, but when I paste the SQL
statement into the procedure I receive an error "Syntax error. in
query expression...

Here is the code (the strLinkCriteria is all on one line):

Private Sub cboDESelect_Click()
On Error GoTo Err_cboDESelect_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContacts"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]))
OR (((tblMulti_Contact_type.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelect_Click:
Exit Sub

Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click

End Sub

I tried to remove the parens and join the parts of the statement with
"&" (as per other posts on the subject) but received an error of
Object Required.

Again, Thanks for any and all help. Liz

Nov 13 '05 #1

Helgardh

AS [member of] in your statement returns a Syntax error for me.

em******@bellsouth.net (Liz Malcolm) wrote in message news:<ae**************************@posting.google. com>...

Hello and Thanks in advance for any help. I am using Access 2000.

I have a data entry form that opens a main form (using the On Click
event of the combo box [cboDESeledct]) with tab controls and 1 subform
on the last tab control. The main form is opened using a where clause
to restrict the contact type. The subform is built on a separate
table that lists other contact types that the contact could be a
member of. Example a volunteer could also be a donor or a vendor,
etc. I would like to open the form where the contact type in either
the main form or the subform is the same.

I can get correct results using a query, but when I paste the SQL
statement into the procedure I receive an error "Syntax error. in
query expression...

Here is the code (the strLinkCriteria is all on one line):

Private Sub cboDESelect_Click()
On Error GoTo Err_cboDESelect_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContacts"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]))
OR (((tblMulti_Contact_type.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelect_Click:
Exit Sub

Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click

End Sub

I tried to remove the parens and join the parts of the statement with
"&" (as per other posts on the subject) but received an error of
Object Required.

Again, Thanks for any and all help. Liz

Nov 13 '05 #2

Shuffs

Hi Malcolm,

Try this as your SQL string - watch for word wrap!

stLinkCriteria = "SELECT tblContacts.*, " & _
"tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] " & _
"FROM tblContacts LEFT JOIN tblMulti_Contact_type ON " & _
"tblContacts.ID_contact = tblMulti_Contact_type.ID_contact " & _
"WHERE ((tblContacts.ID_contact_type)='" & _
[forms]![frmDataEntryMenu]![cboDESelect] & _
"')) OR (((tblMulti_Contact_type.ID_contact_type)='" & _
[forms]![frmDataEntryMenu]![cboDESelect] & "'));"

The above would take the value of your combo boxes and use the value
as criteria.

Hope this helps.

Cheers,

em******@bellsouth.net (Liz Malcolm) wrote in message news:<ae**************************@posting.google. com>...

Hello and Thanks in advance for any help. I am using Access 2000.

I have a data entry form that opens a main form (using the On Click
event of the combo box [cboDESeledct]) with tab controls and 1 subform
on the last tab control. The main form is opened using a where clause
to restrict the contact type. The subform is built on a separate
table that lists other contact types that the contact could be a
member of. Example a volunteer could also be a donor or a vendor,
etc. I would like to open the form where the contact type in either
the main form or the subform is the same.

I can get correct results using a query, but when I paste the SQL
statement into the procedure I receive an error "Syntax error. in
query expression...

Here is the code (the strLinkCriteria is all on one line):

Private Sub cboDESelect_Click()
On Error GoTo Err_cboDESelect_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContacts"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]))
OR (((tblMulti_Contact_type.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelect_Click:
Exit Sub

Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click

End Sub

I tried to remove the parens and join the parts of the statement with
"&" (as per other posts on the subject) but received an error of
Object Required.

Again, Thanks for any and all help. Liz

Nov 13 '05 #3

Liz Malcolm

Hello Again

I used the sample suggested, but it won't go past the OR statement.
If I strip out just the OR statement I get an "Enter Parameter Value"
for tblMulti_Contact_type.ID_Contact_type. This is the 2nd table that
has 3 fields; ID, ID_Contact_type as a lookup field and contact ID.
What could I be missing or doing wrong?

Thanks, Liz
ph****@globalnet.co.uk (Shuffs) wrote in message news:<54**************************@posting.google. com>...

Hi Malcolm,

Try this as your SQL string - watch for word wrap!

stLinkCriteria = "SELECT tblContacts.*, " & _
"tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] " & _
"FROM tblContacts LEFT JOIN tblMulti_Contact_type ON " & _
"tblContacts.ID_contact = tblMulti_Contact_type.ID_contact " & _
"WHERE ((tblContacts.ID_contact_type)='" & _
[forms]![frmDataEntryMenu]![cboDESelect] & _
"')) OR (((tblMulti_Contact_type.ID_contact_type)='" & _
[forms]![frmDataEntryMenu]![cboDESelect] & "'));"

The above would take the value of your combo boxes and use the value
as criteria.

Hope this helps.

Cheers,

em******@bellsouth.net (Liz Malcolm) wrote in message news:<ae**************************@posting.google. com>...

Hello and Thanks in advance for any help. I am using Access 2000.

I have a data entry form that opens a main form (using the On Click
event of the combo box [cboDESeledct]) with tab controls and 1 subform
on the last tab control. The main form is opened using a where clause
to restrict the contact type. The subform is built on a separate
table that lists other contact types that the contact could be a
member of. Example a volunteer could also be a donor or a vendor,
etc. I would like to open the form where the contact type in either
the main form or the subform is the same.

I can get correct results using a query, but when I paste the SQL
statement into the procedure I receive an error "Syntax error. in
query expression...

Here is the code (the strLinkCriteria is all on one line):

Private Sub cboDESelect_Click()
On Error GoTo Err_cboDESelect_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContacts"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]))
OR (((tblMulti_Contact_type.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelect_Click:
Exit Sub

Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click

End Sub

I tried to remove the parens and join the parts of the statement with
"&" (as per other posts on the subject) but received an error of
Object Required.

Again, Thanks for any and all help. Liz

Nov 13 '05 #4

Tim Marshall

Liz Malcolm wrote:

Hello Again

I've experienced exactly the same problem. For example, I write a query
in the query builder and it works fine. But when I take the SQL from
the SQL view and paste it into a procedure, carefully making sure the
double quotes are double quoted where necessary, the procedure doesn't work.

I found the problem to lie in the use of the query by form. That's
where you have
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect],
ie, you want the tblContacts.ID_contact_type to be the value of whatever
is in the form frmDataEntryMenu in the control cboDESelect.

What I do now is make sure I have a variable for anything that I use the
query by form method for in an Access query.

So here's what I would do with what you supplied on your initial post -
I assumed the value of cboDESelect was a text versus a number. note the
addition and treatment of stValue. This works all the time for me:

Dim stDocName As String
Dim stLinkCriteria As String

dim stValue as string

stDocName = "frmContacts"

stValue = forms!frmDataEntryMenu.cboDESelect

stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=""" & stValue & """ ))
OR (((tblMulti_Contact_type.ID_contact_type)=""" & stValue & """ ));"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelect_Click:
Exit Sub

Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click

End Sub

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #5

Liz Malcolm

Tim,

The values are numbers (autonumbers to be exact) so I replaced the
triple double quotes with a single double quote. But I still get a
Syntax error. in query expression.... OR (((tblMu'.

The first WHERE clause works by itself.

The second WHERE clause by itself gives the "Enter Parameter Value"

I followed another suggestion to have the hidden field in the combox
box displayed on the form but that didn't solve the problem (and it
isn't user friendly). The second criteria is from a subform linked by
contact ID to the main form. Not all contacts have a record in this
subform, only those that have two contact types, ex: Volunteer and
Donor, etc. I tried to throw in a Not IsNull but that gave me all
records. I tried to put in the column number but that doesn't work
either.

I tried Allen Brownes suggestion to debug.print the criteria and put
that into a new query which works and gives me the records I'm looking
for. I've been reading everything I could find in the NG for the past
3 days, but cannot seem to find my mistake.

Hoping someone can help with this.

Again Thanks Liz
em******@bellsouth.net (Liz Malcolm) wrote in message news:<ae**************************@posting.google. com>...

Hello Again

I used the sample suggested, but it won't go past the OR statement.
If I strip out just the OR statement I get an "Enter Parameter Value"
for tblMulti_Contact_type.ID_Contact_type. This is the 2nd table that
has 3 fields; ID, ID_Contact_type as a lookup field and contact ID.
What could I be missing or doing wrong?

Thanks, Liz
ph****@globalnet.co.uk (Shuffs) wrote in message news:<54**************************@posting.google. com>...

Hi Malcolm,

Try this as your SQL string - watch for word wrap!

stLinkCriteria = "SELECT tblContacts.*, " & _
"tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] " & _
"FROM tblContacts LEFT JOIN tblMulti_Contact_type ON " & _
"tblContacts.ID_contact = tblMulti_Contact_type.ID_contact " & _
"WHERE ((tblContacts.ID_contact_type)='" & _
[forms]![frmDataEntryMenu]![cboDESelect] & _
"')) OR (((tblMulti_Contact_type.ID_contact_type)='" & _
[forms]![frmDataEntryMenu]![cboDESelect] & "'));"

The above would take the value of your combo boxes and use the value
as criteria.

Hope this helps.

Cheers,

em******@bellsouth.net (Liz Malcolm) wrote in message news:<ae**************************@posting.google. com>...

Hello and Thanks in advance for any help. I am using Access 2000.

I have a data entry form that opens a main form (using the On Click
event of the combo box [cboDESeledct]) with tab controls and 1 subform
on the last tab control. The main form is opened using a where clause
to restrict the contact type. The subform is built on a separate
table that lists other contact types that the contact could be a
member of. Example a volunteer could also be a donor or a vendor,
etc. I would like to open the form where the contact type in either
the main form or the subform is the same.

I can get correct results using a query, but when I paste the SQL
statement into the procedure I receive an error "Syntax error. in
query expression...

Here is the code (the strLinkCriteria is all on one line):

Private Sub cboDESelect_Click()
On Error GoTo Err_cboDESelect_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContacts"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]))
OR (((tblMulti_Contact_type.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelect_Click:
Exit Sub

Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click

End Sub

I tried to remove the parens and join the parts of the statement with
"&" (as per other posts on the subject) but received an error of
Object Required.

Again, Thanks for any and all help. Liz

Nov 13 '05 #6

Tim Marshall

Liz Malcolm wrote:

The values are numbers (autonumbers to be exact) so I replaced the
triple double quotes with a single double quote. But I still get a
Syntax error. in query expression.... OR (((tblMu'.

Did you dim the variable as an integer or other appropriste number format?
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #7