SQL Code error in VBA

[wizardRahl]

Hi,
I'm trying to display my query results onto an Access subform. I have written a very simple SQL statement to preform a query depending on a user's choice of a drop down box in an attempt to display the resultset on the subform.

Example:
If the user selects 10 from the combobox, then it does a query saying "SELECT * from tblD10;"

No matter what the SQL statement is, (i've tried simple ones such as this and more complex queries that I've written before and are known-working) I get the VBA error:
'Run-time error 2342
A RunSQL action requires an argument consisting of an SQL statement.'

I tried using both annotations of the 'all' object as you can see in the code I have: "SELECT *" and "SELECT tblD10.*"

Below is the short snippet of code that gives the error in VB. I'm not sure if having the 'DoCmd.RunSQL' in an 'if statment' matters or not... I wouldn't imagine so.

I'm using Office 2003 w/ SP2 on XP machine...

Any insight much appreciated.
Thanks in advance.

Private Sub cboSearchDept_AfterUpdate()
Dim rs As Recordset
Dim db As Database
Dim frm2 As SubForm
Set frm2 = Forms!frmSearch!subSearch
Dim strSQL10 As String
Dim strSQL11 As String
Set db = CurrentDb


[B]
strSQL10 = "SELECT * FROM tblD10;"
strSQL11 = "SELECT tblD11.* FROM tblD11;"
[/B] 


If cboSearchDept = 10 Then
DoCmd.RunSQL strSQL10
End If

If cboSearchDept = 11 Then
DoCmd.RunSQL strSQL11
End If

Set rs = frm2
Forms!frmSearch!subSearch.Requery

End Sub

[MikeTheBike]

Hi,
I'm trying to display my query results onto an Access subform. I have written a very simple SQL statement to preform a query depending on a user's choice of a drop down box in an attempt to display the resultset on the subform.

Example:
If the user selects 10 from the combobox, then it does a query saying "SELECT * from tblD10;"

No matter what the SQL statement is, (i've tried simple ones such as this and more complex queries that I've written before and are known-working) I get the VBA error:
'Run-time error 2342
A RunSQL action requires an argument consisting of an SQL statement.'

I tried using both annotations of the 'all' object as you can see in the code I have: "SELECT *" and "SELECT tblD10.*"

Below is the short snippet of code that gives the error in VB. I'm not sure if having the 'DoCmd.RunSQL' in an 'if statment' matters or not... I wouldn't imagine so.

I'm using Office 2003 w/ SP2 on XP machine...

Any insight much appreciated.
Thanks in advance.

Private Sub cboSearchDept_AfterUpdate()
Dim rs As Recordset
Dim db As Database
Dim frm2 As SubForm
Set frm2 = Forms!frmSearch!subSearch
Dim strSQL10 As String
Dim strSQL11 As String
Set db = CurrentDb


[B]
strSQL10 = "SELECT * FROM tblD10;"
strSQL11 = "SELECT tblD11.* FROM tblD11;"
[/B] 


If cboSearchDept = 10 Then
DoCmd.RunSQL strSQL10
End If

If cboSearchDept = 11 Then
DoCmd.RunSQL strSQL11
End If

Set rs = frm2
Forms!frmSearch!subSearch.Requery

End Sub

Hi

I believe DoCmd.RunSQL requires, as the error states, an ACTION query (that does not return records). You are supplying a select query ??

To display records in a form you need to set the form RecordSource = "SQL Statement" (SELECT) or create a recordset and assign the fields to the form controls in code. Is that what you want?

MTB

[wizardRahl]

Hi
I believe DoCmd.RunSQL requires, as the error states, an ACTION query (that does not return records). You are supplying a select query ??

To display records in a form you need to set the form RecordSource = "SQL Statement" (SELECT) or create a recordset and assign the fields to the form controls in code. Is that what you want?
MTB

Are you implying that a SELECT statement isn't
Yes, I would like to display the results of a query on a subform. The problem with putting it as the record source is that I have to define a query. The query will change depending on what the user selects in a combobox on the parent form. Acutally, the query will stay the same, just changing the FROM table.

Example:
User selects 11 from combobox (cboSearchDept)
I would like to display results from
SELECT * FROM tblD11;

User selects 12 from combobox (cboSearchDept)
I would like to display results from
SELECT * FROM tblD12;

and so on and so forth...

I can create a fixed query (ex. qryDept10) and it will display the results that would be expected. I just need to figure out how to get the SELECT statement to work when I choose from cboSearchDept.



I have thought about just writing one long SQL statement and saving it as a query, but I don't think I can write IF statements in SQL view when designing a query in Access. It forces you to begin with a SQL keyword (SELECT, INSERT etc)

Thanks for prompt reply!

[wizardRahl]

That first sentence is supposed to say:
"Are you implying that the SELECT statement isn't an ACTION query?"

I would have to agree with you. However, the error says that the 'DoCmd.RunSQL' action requires an argument consisting of a SQL statement, not an action query. This tells me that the statement that I have written isn't recognized by VBA as a valid SQL statement.

Any other insight appreciated.
Sorry for the double post.

[MikeTheBike]

That first sentence is supposed to say:
"Are you implying that the SELECT statement isn't an ACTION query?"

I would have to agree with you. However, the error says that the 'DoCmd.RunSQL' action requires an argument consisting of a SQL statement, not an action query. This tells me that the statement that I have written isn't recognized by VBA as a valid SQL statement.

Any other insight appreciated.
Sorry for the double post.

Hi

Well, we agree that SELECT isn't an action query, however RunSQL does require a valid action query SQL, as the eror message suggest.

Perhaps the solution to your problem would be something like this code in the After Update event of the combo box

Private Sub cboSearchDept_AfterUpdate()
    Me.RecordSource = "SELECT * FROM " & cboSearchDept
    Me.Requery
End Sub

The bound column of the cboSearchDept combo would list all the table names to select from.

This also assumes that all the fields are the same in all the tables !

???

MTB

[wizardRahl]

Hi

Well, we agree that SELECT isn't an action query, however RunSQL does require a valid action query SQL, as the eror message suggest.

Perhaps the solution to your problem would be something like this code in the After Update event of the combo box

Private Sub cboSearchDept_AfterUpdate()
    Me.RecordSource = "SELECT * FROM " & cboSearchDept
    Me.Requery
End Sub

The bound column of the cboSearchDept combo would list all the table names to select from.

This also assumes that all the fields are the same in all the tables !

???

MTB

Thanks again for the reply!

It didn't like the RecordSource statement that you provided. It or any variation of it produced the same error: "Syntax error in FROM clause".

I may have found a different way. I'll just write a query for each SELECT statement and tell it:

If 'cboSearchDept' = 10 Then
DoCmd.OpenQuery qryD10
End If

All I need now is to figure out how to get the results to display on the subform.
The subform is named 'subSearch'. When I update the combo, I see a quick message on status bar (bottom left) saying "Calculating... " , so it seems that it is actually running the query. Just gotta get it to display to resultset from the query.

I've taken the liberty of uploading the the db file. If you think you'd like to look at it, please feel free to do so. There is no sensitive data, only names and the structure of the db.

http://5.download-3.files-upload.com/150/2007/08/13/13-30/TM.mdb

Any further insight much appreciated.

[MikeTheBike]

Thanks again for the reply!

It didn't like the RecordSource statement that you provided. It or any variation of it produced the same error: "Syntax error in FROM clause".

I may have found a different way. I'll just write a query for each SELECT statement and tell it:

If 'cboSearchDept' = 10 Then
DoCmd.OpenQuery qryD10
End If

All I need now is to figure out how to get the results to display on the subform.
The subform is named 'subSearch'. When I update the combo, I see a quick message on status bar (bottom left) saying "Calculating... " , so it seems that it is actually running the query. Just gotta get it to display to resultset from the query.

I've taken the liberty of uploading the the db file. If you think you'd like to look at it, please feel free to do so. There is no sensitive data, only names and the structure of the db.

http://5.download-3.files-upload.com/150/2007/08/13/13-30/TM.mdb

Any further insight much appreciated.

Hi

If you want to have the new table data to show in the sub form then you will have to change the RecoedSource on the sub form and requery it.

As I mentioned before, if you have the names on the table (or query if it is a sub set or multiple table query) in the combo box (as a Value List!) in the bound column, then you can use this

Private Sub cboSearchDept_AfterUpdate()
    Me.SubFormControlName.Form.RecordSource = "SELECT * FROM " & cboSearchDept
    Me.SubFormControlName.Requery
End Sub

where SubFormControlN ame is the name of the control containing the subform.

As mention the tables need the same fields.


MTB

[wizardRahl]

Access will not allow me to put table names in the bound column. It does not allow text to be entered. I can select the ValueList to be the RowSource of all the table names, so that the combo is the selection that I want to be there. I'm thinking this is what you meant.

Also, after I do the said steps above, I get the error:
"Method or data member not found." and it has cboSearchDept selected.

I've triplechecked the spelling in both places (code and control properties) and both are correct. I'm not sure why VB thinks that the object doesn't exist.

Here's the updated file:

http://8.download-1.files-upload.com/150/2007/08/15/13-20/TM.mdb

It's the little things like this that are frustrating. I know that it's a little change that will make the app work like it should.

Any further insight appreciated.
Thanks in advance.

[MikeTheBike]

Access will not allow me to put table names in the bound column. It does not allow text to be entered. I can select the ValueList to be the RowSource of all the table names, so that the combo is the selection that I want to be there. I'm thinking this is what you meant.

Also, after I do the said steps above, I get the error:
"Method or data member not found." and it has cboSearchDept selected.

I've triplechecked the spelling in both places (code and control properties) and both are correct. I'm not sure why VB thinks that the object doesn't exist.

Here's the updated file:

http://8.download-1.files-upload.com/150/2007/08/15/13-20/TM.mdb

It's the little things like this that are frustrating. I know that it's a little change that will make the app work like it should.

Any further insight appreciated.
Thanks in advance.

Hi

In addition to my previous code, try this (suitable Modified to you table/query names) in the main form load event

With  cboSearchDept 
    .RowSourceType = "Value List"
    .BoundColumn = 1
    .ColumnCount = 2
    .ColumnWidths = "0cm;2.54cm"
    .RowSource = "qryD10;Query D10 Data;qryD11;Query D11 Data;qryD12;Query D12 Data"
End With

and see if that works, assuming qryD10 etc. are stored queries, althought table names will also work.

I'm sorry but IT policy does not permit me to down load anything, so cannot look at file.

MTB