Changing the Name of a Query from a form

[gblack]

OK…procedurally I know I am probably doing this wrong, but that is the way the database is set up…so let me try to explain.

Query A has the same fields as Query B. Query B has two fields which are parameter values (i.e. when you run the query, Access prompts you to enter two values). Query A gets these values from a table.

The process I have for getting a report depends upon whether I wish to hard code the values of the report in, or whether I wish to use the information from the table that Query A uses.

Depending upon my choice I end up renaming Query A or Query B to Query C.

All of my subsequent queries utilize the information in Query C to make the necessary calculations and data massaging in order to spit out a Report.

What I would like to do is have a toggle control/radio button (on a form), which will rename Query A to Query C or Query B to Query C, depending upon which choice I click on the toggle button.

My issue is I do not know how to change the name of a Query in VBA. I tried to look up how to do it using [DoCmd.RunCommand acCmdRename], but I couldn’t find any examples which would help and don’t even know if this is how I should go about renaming these queries.

I hope I’ve explained myself correctly… Does anyone have a clue as to how I could do this?

Thanks in advance…
Gary

 

[grnegsndhm]

Hmm. Have you looked into writing the query dynamically? You could use the QueryDef Object to write the SQL behind the scenes so that you do not need.

Will need to import the Microsoft DAO library in VB referances. 'Help' should have more information on applying this in VB.

 

[gblack]

Ya I thought about that, but the queries are big and messy...I'd rather just change the names if I can...it seems like this is the best way at the moment...

G

 

[grnegsndhm]

Just and Idea.

You can store the queries in a query table and use DLOOKUP to retrieve the text of those queries.

IE
TableQry
Fields: QueryName
Fields: QuerySQL


Query A | SELECT * FROM YO
Query B | SELECT * from B WHERE B.ID = 'XIDINX'

VarSQL = DLOOKUP(QuerySQL, TableQry, QueryName = 'Query A')
VarSQL = Replace(VarSQL, "XIDINX", 8)

QUERYC.SQL = VarSQL

 

[gblack]

That's a thought...