I feel really stupid, you guys. I've been noodling around with this for 2 days and searching all sorts of help. All I want to do is return the SQL string from a saved query for manipulation within VBA. I'm sure there's a perfectly simple way to do it, but I can't quite figure it out. All the help I've seen with the .sql property has the string provided in the VBA, not the other way around (pulling it from somewhere else).
I'm trying to create a search form that has a subform (2, actually, depending on the table) and want to get its last recordsource for refining results using the main search form. To do that, I need the SQL string. When I use the line below, it works great if the recordsource is a SQL statement, but if it's a query, it just hates me.

Can someone tell me the proper logic so I can get this string?
Thanks, you guys ALWAYS rock!
db

I think you need to get the RecordSource and then inspect it for the possible alternatives:

1. If it is a saved Query name, get the SQL String of the QueryDef that represents the saved Query.

2. If it starts with "SELECT" (and provided that you follow some naming convention, e.g. using prefix "qry" or "qsel" for SELECT queries), then you know it is a SQL String.

Check out this previous thread.

Hope that helps...

Well, that's just it, see....SFE is the variable for Forms!frmFilter.sfmEmpl.Form and it gives me "qryTempEmpl" which is the name of the query. I tried the recordset but it still didn't return anything but the query name, and what I'm trying to get is your #1, the SQL string of the query.

Oops, I was answering Van, not the DB Guy...hey, that's probably what I need (your code from the other thread)! Thanks! I'll try it and see if it works.

What's wrong with

CurrentDb.QueryDefs("qryTempEmpl").SQL

once the RecordSource Property returns "qryTempEmpl" and you identify (by code) though your naming convention that it is a Query name?

That's exactly what I explained in my previous post.

Since I must use a variable for the recordsource (either SQL statement or query name), it tells me it's an "invalid use of property." It doesn't like ".sql" at the end, do you know why? I'm still trying the other way.

??? Not sure what you are trying to describe here.

Even if you previously assigned the RecordSource by code with a statement like:

Forms("YourForm").RecordSource = strVariable

and the value of the strVariable is either "SELECT..." or "qryName"

When you refer to the RecordSource later, it will return either "SELECT..." or "qryName" as appropriate and never "strVariable".

Place a Debug.Print Forms("YourForm").RecordSource just after the assignment statement to see what I mean...

Yes, I understand, but when it returns the variable as a query name, that's where I can't get it to respond with a ".sql" property (resulting in a string). Do you mean that I have to dim the variable SFE.recordset as a query?? It isn't always a query, it can also be a string ("SELECT..."). I must be missing a step here, sorry! Now I'm more confused than I was initially.

After looking again at your response, your code "Forms("YourForm").RecordSource = strVariable" is the opposite of what I'm trying to do, maybe that's our disconnection. I'm trying to go the other way. I have a subform, which may have a query or a SQL statement as its recordsource, and I don't want to change that recordsource, I want to retrieve it as a SQL statement string so I can apply revisions to that statement in code. Does that help?

The (first) assignment statement is to set up the scenario only. I knew you want to get the SQL String.

Do it this way:

1. Open your Form
2. In the Immediate / Debug window, type:

?Forms("YourForm").YourSubformControl.Form.RecordSource

This should returns either "SELECT..." or "qryName". If it is "SELECT...", you are there.

3. If it is "qryName", type in the Immediate / Debug window:

?CurrentDb().QueryDefs("qryName").SQL

This will return the SQL String of the Query being used as the RecordSource of the Subform.

The above is the "manual" process but the code for the automatic processing is nearly the same. All you need is an If statement to decide whether the value return by RecordSource is an SQL or a Query name.

I suspect that you assign whatever returns by the RecordSource into a VBA Variable but you don't know how to refer to the QueryDef whose name is stored in the Variable? If that's the case:

CurrentDb.QueryDefs(Variable) to refer to the required QueryDef

and

CurrentDb.QueryDefs(Variable).SQL to get the SQL String

Van:

Okay, so my problem is definitely in variable types. All the manual process worked as you described it.

To get the (Variable) to work as it should, is it a string or a query def? It seems I tried both to no avail, but I am trying it again both ways. I'll let you know what happens next. I do appreciate your patience!

db

Van:

Okay, I think I have it. This statement seems to work:



Thanks for all of your help and I hope you didn't pull out too much hair in frustration trying to explain it to me!

db

You're welcome... Glad we could help...

Part of the problem was the mis-named variable strSQL, I think. Since this can be either an SQL String or a Query name, this may mislead us when we try to analyze the code. Since it refer to RecordSource, I think the more meaningful name would be "strRecordSource" or something similar (but don't use "RecordSource"!)