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_Cli ck()
On Error GoTo Err_cboDESelect _Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContact s"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_ contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON
tblContacts.ID_ contact = tblMulti_Contac t_type.ID_conta ct WHERE
(((tblContacts. ID_contact_type )=[forms]![frmDataEntryMen u]![cboDESelect]))
OR (((tblMulti_Con tact_type.ID_co ntact_type)=[forms]![frmDataEntryMen u]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelec t_Click:
Exit Sub

Err_cboDESelect _Click:
MsgBox Err.Description
Resume Exit_cboDESelec t_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

[Helgardh]

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

em******@bellso uth.net (Liz Malcolm) wrote in message news:<ae******* *************** ****@posting.go ogle.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_Cli ck()
On Error GoTo Err_cboDESelect _Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContact s"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_ contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON
tblContacts.ID_ contact = tblMulti_Contac t_type.ID_conta ct WHERE
(((tblContacts. ID_contact_type )=[forms]![frmDataEntryMen u]![cboDESelect]))
OR (((tblMulti_Con tact_type.ID_co ntact_type)=[forms]![frmDataEntryMen u]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelec t_Click:
Exit Sub

Err_cboDESelect _Click:
MsgBox Err.Description
Resume Exit_cboDESelec t_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

[Shuffs]

Hi Malcolm,

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

stLinkCriteria = "SELECT tblContacts.*, " & _
"tblContacts.ID _contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] " & _
"FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON " & _
"tblContacts.ID _contact = tblMulti_Contac t_type.ID_conta ct " & _
"WHERE ((tblContacts.I D_contact_type) ='" & _
[forms]![frmDataEntryMen u]![cboDESelect] & _
"')) OR (((tblMulti_Con tact_type.ID_co ntact_type)='" & _
[forms]![frmDataEntryMen u]![cboDESelect] & "'));"

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

Hope this helps.

Cheers,

em******@bellso uth.net (Liz Malcolm) wrote in message news:<ae******* *************** ****@posting.go ogle.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_Cli ck()
On Error GoTo Err_cboDESelect _Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContact s"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_ contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON
tblContacts.ID_ contact = tblMulti_Contac t_type.ID_conta ct WHERE
(((tblContacts. ID_contact_type )=[forms]![frmDataEntryMen u]![cboDESelect]))
OR (((tblMulti_Con tact_type.ID_co ntact_type)=[forms]![frmDataEntryMen u]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelec t_Click:
Exit Sub

Err_cboDESelect _Click:
MsgBox Err.Description
Resume Exit_cboDESelec t_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

[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_Contac t_type.ID_Conta ct_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****@globalne t.co.uk (Shuffs) wrote in message news:<54******* *************** ****@posting.go ogle.com>...

Hi Malcolm,

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

stLinkCriteria = "SELECT tblContacts.*, " & _
"tblContacts.ID _contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] " & _
"FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON " & _
"tblContacts.ID _contact = tblMulti_Contac t_type.ID_conta ct " & _
"WHERE ((tblContacts.I D_contact_type) ='" & _
[forms]![frmDataEntryMen u]![cboDESelect] & _
"')) OR (((tblMulti_Con tact_type.ID_co ntact_type)='" & _
[forms]![frmDataEntryMen u]![cboDESelect] & "'));"

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

Hope this helps.

Cheers,

em******@bellso uth.net (Liz Malcolm) wrote in message news:<ae******* *************** ****@posting.go ogle.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_Cli ck()
On Error GoTo Err_cboDESelect _Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContact s"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_ contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON
tblContacts.ID_ contact = tblMulti_Contac t_type.ID_conta ct WHERE
(((tblContacts. ID_contact_type )=[forms]![frmDataEntryMen u]![cboDESelect]))
OR (((tblMulti_Con tact_type.ID_co ntact_type)=[forms]![frmDataEntryMen u]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelec t_Click:
Exit Sub

Err_cboDESelect _Click:
MsgBox Err.Description
Resume Exit_cboDESelec t_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

[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]![frmDataEntryMen u]![cboDESelect],
ie, you want the tblContacts.ID_ contact_type to be the value of whatever
is in the form frmDataEntryMen u 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 = "frmContact s"

stValue = forms!frmDataEn tryMenu.cboDESe lect

stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_ contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON
tblContacts.ID_ contact = tblMulti_Contac t_type.ID_conta ct WHERE
(((tblContacts. ID_contact_type )=""" & stValue & """ ))
OR (((tblMulti_Con tact_type.ID_co ntact_type)=""" & stValue & """ ));"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelec t_Click:
Exit Sub

Err_cboDESelect _Click:
MsgBox Err.Description
Resume Exit_cboDESelec t_Click

End Sub

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

[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******@bellso uth.net (Liz Malcolm) wrote in message news:<ae******* *************** ****@posting.go ogle.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_Contac t_type.ID_Conta ct_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****@globalne t.co.uk (Shuffs) wrote in message news:<54******* *************** ****@posting.go ogle.com>...

Hi Malcolm,

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

stLinkCriteria = "SELECT tblContacts.*, " & _
"tblContacts.ID _contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] " & _
"FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON " & _
"tblContacts.ID _contact = tblMulti_Contac t_type.ID_conta ct " & _
"WHERE ((tblContacts.I D_contact_type) ='" & _
[forms]![frmDataEntryMen u]![cboDESelect] & _
"')) OR (((tblMulti_Con tact_type.ID_co ntact_type)='" & _
[forms]![frmDataEntryMen u]![cboDESelect] & "'));"

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

Hope this helps.

Cheers,

em******@bellso uth.net (Liz Malcolm) wrote in message news:<ae******* *************** ****@posting.go ogle.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_Cli ck()
On Error GoTo Err_cboDESelect _Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmContact s"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_ contact_type, tblMulti_Contac t_type.ID_conta ct_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contac t_type ON
tblContacts.ID_ contact = tblMulti_Contac t_type.ID_conta ct WHERE
(((tblContacts. ID_contact_type )=[forms]![frmDataEntryMen u]![cboDESelect]))
OR (((tblMulti_Con tact_type.ID_co ntact_type)=[forms]![frmDataEntryMen u]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cboDESelec t_Click:
Exit Sub

Err_cboDESelect _Click:
MsgBox Err.Description
Resume Exit_cboDESelec t_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

[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