writing code to create a query

[bluesage]

hi, im quite new to microsoft access. I have been trying to figure out (searched the web, read books) how to create a query through VBA based on the choices in a listbox but so that the next time the choice is chosen, it creates a query without having to delete the old one. so in other words making it so it creates a new name but the same code. if thats possible?

i want to create code to create a query which is saved as a different name each time it is launched. or if its easier creating code that changes the name of the previous one.

thanks

 

[SBendBuckeye]

Not sure exactly what you are trying to do, but you want to look at the DAO.QueryDef object. You may need to set a reference to it if you are using later versions of Access.

Your SQL code goes in the SQL property. You would do something like the following:

Dim qdf As DAO.QueryDef
Set qdf = New DAO.QueryDef
qdf.Name = YourQueryName
qdf.SQL = YourSQLString
qdf.OtherProperties as Needed
CurrentDB.QueryDefs.Append qdf
Set qdf = Nothing

After you have done the above, your query should show up in the list. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[bluesage]

thanks,

if i link this code to an afterupdate of a listbox with lets say three choices, bob, frank, and susan.

is it possible to choose bob, and so the query that is created contains information related to bob but so that i can click again the next time on frank which will create the same query for his information without causing conflicts.

lets say i saved the query as "userqry", if i choose frank like i said, the second time, it will say, "userqry" already exists.

i know how to make it so it deletes each query each time and re-creates it, but i would like for each query to be saved because i want to use the information later. i don't know if this is a bit clearer?

 

[MoLaker]

First, I'm a retired programmer and a little out of practice, so bear with me, please.

You could name the queries based on the name chosen in the listbox (i.e. bobqry, frankqry, etc.). That would make them unique to each choice and avoid already exists error.

But... Creating and deleting objects can quickly bloat a database requiring it to be compacted often. There is another way which might work better for you. Instead of creating and saving query objects, try creating SQL statements and save the SQL string to a table. Save the user name, too and you can have a permanent record of each. Overwrite if need be, delete if need be. Whenever you want to display the query results, you open a recordset with the SQL string stored under the user's name. Store the date/time when the SQL was created if you need to know when the user created the SQL. And you'll not have to create even one query object.