Function to return name of current query

[Harry Shmedlap]

Does anyone have a function that, when called from a query, will return the name of that query?
I need this for the following reason:

I have about 20 queries that I run sequentially, each one adding its own records to a single common target table. If I have one column that shows the name of the query that the corresponding records came from, then I know which query to suspect when there are problemmatic records.

 

[ajetrumpet]

If I have one column that shows the name of the query that the corresponding records came from, then I know which query to suspect when there are problemmatic records.

One comment....


If you had one COLUMN to show the name of the query from which the appended records came from, and you're appending from more than one table, how would you know which records are associated with the query name?? Query Name = Column Name AND records listed VERTICALLY??? Columns describe / name fields, not records....???

 

[petehilljnr]

You could just alter your 20 queries to include an extra field with the name of the query in it (which you would have to enter manually). e.g.

SELECT MyQuery.*, "MyQuery" As QueryName
FROM MyQuery;

Not a function, but a solution at least?

 

[The_Doc_Man]

For forms and reports, there is a short-cut called ME that allows you to find names, as

x = me.name

This works because there is a code context associated with forms and reports, the class module. Me doesn't work correctly in a general module because there is no scaffolding associated with the document. General modules just sort of "hang out" on their own.

For queries, there is no code context in which a scaffold can be erected. They directly call the general or system-library modules without going through a document-like context. There is no "hook" on which to base this name function unless you wanted to write some code that tried to analyze your context stack in Windows or in Access. I highly DO NOT recommend this.

Pete Hill's solution is far easier and technically viable. Plus, 20 queries = 20 fairly simple edits and you are done. Since there is no query code context other than general modules, you would have to write your own function from scratch and THAT should be really ugly. I'd take the easy way out if I were you.

 

[Harry Shmedlap]

Thanks for all the input.
Pete's suggestion is where I began with all this. That was the obvious way to do this but I was looking for an alternative so that renaming of one of the queries would automatically result in the string getting updated.
(The_DOC_Man's explanation is very insightful. Perhaps the next time I have insomnia I'll write a windows hook...)

Actually I have a pretty simple solution. Rather than defining the queries "outside" in the Access query grid, I'll do it all in VBA.
One subroutine will contain the sequence of all 20 query definitions, each one preceeded by its SQL string. In the string I'll put the QueryName as a variable which will be initiallized to the proper name just before that query is executed. If I decide to change a name, it will be there right in front of the SQL string.