Microsoft Access DoCmd.SaveAs to create another query based on existing query

I have a select query called 'qryDefineGrid'

On Click Event of a form button I wish to create another query based on 'qryDefineGrid'  as though I had physically done a Save As, and then renamed it.  Once done I would then want the user to be taken to Design View of this new query.

The new query would be named as the text that would be entered in a form a control[frmExport]![txtGridQueryName]

There does not appear to be a DoCmd.SaveAS ?

Developing in 2007 that is also used in 2010 environment.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
You can determine the SQL behind a query with syntax similar to:


So, to create a new query based on that query you would use:

Dim strQueryName as string
Dim qdf as dao.querydef

strQueryName = forms("frmExport").txtGridQueryName
set qdf = currentdb.CreateQueryDef(strQueryName, currentdb.QueryDefs("qryDefineGrid").SQL)

Open in new window

Then, to open that query in design view, via code:
docmd.openquery strQueryName, acViewDesign

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul-bbcAuthor Commented:
Does what it says on the tin
Dale FyeCommented:
"Does what it says on the tin "

Not sure what this means, must be a colloquialism.  Glad I could help.

If you are going to allow users to create queries like this on the fly, you might also create a table to store the names of these queries and allow the user to provide a description of the query.  Then, you could provide a form for them to select a query that they have already created.
Paul-bbcAuthor Commented:
'Does what it says on the tin' old English expression for 'it does exactly what it says it will', taken from an old Dulux tv paint advert.

Already have a table for them to use. There are over 50 customers who want an Excel output of the underlying data, but some only want some columns, some want them in a different order etc., and customers added all the time.  The same user interface then also does an output to command for Excel, referencing the same query name that was created for the other step.
Dale FyeCommented:
Guess I probably could have looked that up, couldn't I.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.