CREATE QUERY AT RUNTIME

[LonnieJohnson]

I have a listbox with table names. I want to be able to double click on one and create a new query with that table.

I tried playing with the query def object, but it seems to save the query. I want to be able (via VBA) to open a new query in design view with a predetermined table name and no fields selected.

Am I asking too much or just to blind to see how? Is there like a NEW DATABASE OBJECT fuction?

Much thanks in advance.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[zevw]

There are many different ways how to run queries from code

1. DoCmd.RunSqL <sql statement>

2. Dim <variable name> As QueryDef

You know what look into these 2 for now you can create new queries replace queries etc.

 

[LonnieJohnson]

I am aware of both methods.

Thanks for your response.

As noted in my post, I have already tried the QueryDef object.

What I want to do is to open a NEW query in design view and select a particular table all from code.

When I use QueryDef, the object gets saved before I can open it in design view.

Thanks again.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[zevw]

if you want to chage SQL statement why do you need to open thev design view just modify your Query with the table you wnt to reference to.

But with code to open your query design you do the following.

DoCmd.OpenQuery &quot;<Query Name>&quot;, acViewDesign

DoCmd.SelectObject acQuery, &quot;<Query Name>&quot;

 

[LonnieJohnson]

This is a user request. A paying user. So we try to do what they want even if it sounds a little outrageous at times.

They have a listbox of tables. They do not want to give access to the database window. So they want to double click a table name on the list and a &quot;NEW query object&quot; in design view pops open.

Does anyone know how to do that?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!