Storing SQL Queries to a table

[RobWulf]

[Solved] Storing SQL Queries to a table

I can't seem to find it and this has to be the last bit of stuff I need to get my Database up and running for my boss using an EXE front end.

What I am trying to do is something similar to what I did for my reports

I have a table with the following fields

ID = Autonumber
Discrip = Text (Discription of the Report)

The combo box on the On Change event has this code

Private Sub ReportCombo_AfterUpdate()
DoCmd.OpenReport ReportCombo, acViewPreview
End Sub

Can I set something like this for SQL commands?

 

[Galaxiom]

Private Sub QueryCombo_AfterUpdate()
    DoCmd.OpenQuery Me.QueryCombo
End Sub

However users should not directly view queries.

This alternative can be used for action queries to avoid warnings:

Private Sub QueryCombo_AfterUpdate()
    CurrentDb.Execute Me.QueryCombo, dbFailOnError
End Sub

(Me is a reference to the current object and should be included when referring to controls on the form.)

 

[Galaxiom]

If you would prefer to store the actual SQL text in the table then put this field in a hidden column of the combo and refer to that column in the Execute command.

 

[RobWulf]

I just don't understand what I am doing wrong.

I have a test table set up with 3 columns An ID colum with Auto number, a column for the Description and one for the SQL


I set up a form with the field set to Text for the SQL command and put SELECT * FROM Customers in that field, but when I run the form with the drop down it gives me an error saying it can't find the table for the querry

 

[Galaxiom]

Execute only works on action queries (eg INSERT, UPDATE, DELETE).

To open a SELECT from code you would need to write the SQL text to the SQL Property of a QueryDef and then OpenQuery on that.

 

[CJ_London]

or docmd.runsql myCombo.column(2) - based on your dropdown having three columns