Wild Cards in Queries?

  • Thread starter Thread starter Grog
  • Start date Start date
G

Grog

does anyone know how to use a single make table query to
work across multiple nearly identical forms when it has to
specify a certain data field common to all? Specifically
what I want to do is to change the criteria in the make
table query for this common field to say something like
[Forms]![name of active form]![the value in this field]

Can this be done? How?
 
Hi,


Nope. The table has to be known for the query plan to be "compute"...
since it may depends of the table size, the presence of indexes, etc.

I suspect you have a design that can be improved. If your tables are
like Year2000, Year2001 and Year2002, then, may be you could merge the
three tables into just one. To do what you actually do JUST WITH one of the
table, add the appropriate WHERE clause (or, but less desirable, the
appropriate filter).



Hoping it may help,
Vanderghast, Access MVP
 
does anyone know how to use a single make table query to
work across multiple nearly identical forms when it has to
specify a certain data field common to all? Specifically
what I want to do is to change the criteria in the make
table query for this common field to say something like
[Forms]![name of active form]![the value in this field]

Can this be done? How?
Click to expand...

I'm uncomfortable with both ends of this question. In my experience
MakeTable queries are *very* rarely necessary; given that you can base
a Form, a Report, an Export, or another Query on a select query and
avoid the overhead and bloating caused by MakeTable queries, may I ask
why you're doing this at all?

And secondly, "multiple nearly identical forms" sounds very suspicious
as well; how do these forms differ?

That said... you could put code behind a button on each form. If you
make the (single) MakeTable query a Parameter query you could use code
to resolve the parameter before executing it. Let's say you put
[PARAMETER1] as the parameter in the query; you could have click vent
code like

Private Sub cmdRunQ_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef ' you do need DAO here I think, not ADO
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("YourMakeTableQuery")
qd.Parameters("[PARAMETER1]").Value = Me!controlname
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
<error handling here>
Resume Proc_Exit
End Sub
 
Back
Top