DoCmd.RunSQL syntax help

[jketcher]

I am attempting to create a form that uses multiple combo boxes to provide selection criteria for a query that is run when a button is submitted. I am stuck with syntax errors and other types of errors when I create a DoCmd.RunSQL "SQL code copied from a query". I am using this as the base to build the selection criteria. I am using a subform to display the data and then plan to create an export file with the query results.

Private Sub btnSubmit_Click()
On Error GoTo Err_btnSubmit_Click
DoCmd.RunSQL "SELECT tblMktBase.*, tblMktBase.Title, tblMktBase.T_YPEINSTI" & _
FROM tblMktBase;"

Would one of you please take a look at this and advise on how to fix? Also any other help with setting this up correctly will be much appreciated. Examples etc.

Thanks, Jketcher

 

[ajetrumpet]

Private Sub btnSubmit_Click()
On Error GoTo Err_btnSubmit_Click
DoCmd.RunSQL "SELECT tblMktBase.*, tblMktBase.Title, tblMktBase.T_YPEINSTI" & _
FROM tblMktBase;"

it is a good idea to shy away from copying and pasting into modules when you've written queries with normal SQL for Access. Most of the time, it will result in very tiny syntax errors, but even the smallest error will cause your code not to work. Your syntax also needs a quick fix. This should work fine:

Private Sub btnSubmit_Click()
On Error GoTo Err_btnSubmit_Click
   DoCmd.RunSQL "SELECT tblMktBase.* " & _
"FROM tblMktBase;"

Also, why do you have 2 fields in the query when you have already selected all the fields using the asterik (*)? That is not necessary to do. Another thing too - when you extend string statements to more than one line, you have to use the double quote at the beginning of the second line too....and remember to include a space at the end of the first line, inside the quote marks.

 

[SOS]

FYI:

DoCmd.RunSQL does not work with SELECT queries. It is for ACTION queries (like INSERT, UPDATE, DELETE).

 

[SOS]

You can build the form filter and then export the filtered recordset to Excel. You don't need a query for that.

Me.SubformContainerNameHere.Form.Filter = "[YourField1]=" & Me!Whatever & " And [Field2]=" & Chr(34) & Me!WhateverTextValue & Chr(34)
Me.SubformContainerNameHere.Form.FilterOn = True

And then you can use code like this to export the form's recordsource.

 

[ajetrumpet]

FYI:

DoCmd.RunSQL does not work with SELECT queries. It is for ACTION queries (like INSERT, UPDATE, DELETE).

Laughs. Oops! Thanks for the correction.

 

[jketcher]

Thanks to both of you! Just got back from vacation. Am back at working on your suggestions today. Will let you know how it goes. I have multiple selection criteria so this could be tedious.

jketcher

 

[jketcher]

This is now working !!! but have a little extra problem I need help with. One of the combo boxes has a selection of 'All'. I don't know how to express this to get it to work in the filter. Do I use a 'Dim' statement to declare a variable? I am basically selecting "CEO" or "DFA". There are only two choices but the user could ask for both. Thanks!

 

[jketcher]

Still needing guidance. One of the combo boxes has a selection of 'All'. I don't know how to express this to get it to work in the filter. Do I use a 'Dim' statement to declare a variable?

Combo Box: CEO
DFA
All (this will be either CEO or DFA)

How do I get 'All' to work in a filter. I am having syntax issues.

Thanks for your expedient help!