Mr. Bud wrote:
> Hi and TIA. What I'm wondering is can you use aquery name in place
> of a table name when building an sql statement in code? Example
> below can I replace MyTable with MyQuery.
Yes. A saved query looks like a table to the query engine.
> It bombs out on me when I run the code.
:-)
We're not looking over your shoulder at your screen. What is the error
message you get?
> My goal is to creat several Union queries in my db.
? Why several union queries? Or do you mean to say you have created a union
query that combines the data from several tables?
> Have the user select tables on my form to use in the results and
> depending on what they select I will use that saved Union query in
> place of the table name (MyTable) below.Thanks for your time.
>
>
> Set db = CurrentDb()
> Set qdf = db.QueryDefs("qryMultiSelect")
> If Me!lstUIC_ID.ItemsSelected.Count > 0 Then
> For Each varItem In Me!lstUIC_ID.ItemsSelected
> strCriteria = strCriteria & "MyTable.UIC = " & Chr(34) _
> & Me!lstUIC_ID.ItemData(varItem) & Chr(34) &
> "OR " Next varItem
> strCriteria = Left(strCriteria, Len(strCriteria) - 3)
> Else
> strCriteria = "MyTable.UIC Like '*'"
> End If
> strSQL = "SELECT * FROM MyTable" & _
> "WHERE " & strCriteria & ";"
> qdf.SQL = strSQL
This sql statement will raise a syntax error because you have failed to put
a space between MyTable and WHERE. While debugging, you should look at the
results of your concatenation. You can do that by using msgbox or
debug.print.
debug.print strSQL
> DoCmd.OpenQuery "9h1_qryMultiSelect"
Do you have a saved query called "9h1_qryMultiSelect"? if not, this last
line will raise an "object not found" error.
Does qryMultiSelect contain your union query? And does that union query
return a field called UIC? If so, your code is replacing that union query
with a simple select statement.
Or is there another saved query that does the union? And qryMultiSelect is
intended just to be used in your OpenQuery statement? If so, then you should
be able to use the name of that saved union query (whatever its name is) in
your sql. Like this:
strSQL = "SELECT * FROM qryUnion " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
My preference is not to display the results of a query in a query window.
Given that this is read-only data, I would create a report whose data source
is qryMultiSelect and use DoCmd.OpenReport instead. Alternatively, you can
use a subform whose form's data source is qryMultiSelect and use
subformname.Requery
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|
Similar Threads
