Queries in VBA without SQL

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I posted something similar yesterday in the report section, trying to figure
out how to re-use queries. It seems wasteful to me to write a SQL statement
several times throughout a program, changing it slightly each time.

I am therefore looking for a way to use a pre-defined query instead of the
SQL or I need to use the SQL in its own module or procedure and then figure
out how to make the changes outside of the procedure.

Here is what prompted this line of thought:

With Reports![rptRosters]
.Filter = "ID = Name of pre-defined Query"
.FilterOn = True
End With

I could easily use a select statement and get what I need, but then I would
be redoing something that is already done.

Thanks for any help,
 
Dan said:
I posted something similar yesterday in the report section, trying to figure
out how to re-use queries. It seems wasteful to me to write a SQL statement
several times throughout a program, changing it slightly each time.

I am therefore looking for a way to use a pre-defined query instead of the
SQL or I need to use the SQL in its own module or procedure and then figure
out how to make the changes outside of the procedure.

Here is what prompted this line of thought:

With Reports![rptRosters]
.Filter = "ID = Name of pre-defined Query"
.FilterOn = True
End With

I could easily use a select statement and get what I need, but then I would
be redoing something that is already done.
Click to expand...


Where/How is this query going to be used?

What "slight" changes do you want to make?

If all you want to do is open a form or report with a
different set of criteria, you can use the WhereCondition
argument in the OpenForm or OpenReport methods.

Just base the form/report on the query without any criteria
and then use a statement like:

DoCmd.OpenReport "nameofreport", . . . ,
WhereCondition:= "somefield = " & somevalue
 
What I have in this case is an equipment ID that I will need to use several
values such as 05538B, or 05538C, or 05538D. This will change depending on
what type of equipment. This may also need to be updated in the future as
new equipment is added. So what I am looking to do is use:
.Filter = ID = strFilter

Where strFilter is the pre-defined query.

I have successfully done this on a form by useing the RowSource property of
a ComboBox. You can directly call a query here.

I have not been able to call a query on the filter property.

Thanks again for any assistance,


Marshall Barton said:
Dan said:
I posted something similar yesterday in the report section, trying to
figure
out how to re-use queries. It seems wasteful to me to write a SQL
statement
several times throughout a program, changing it slightly each time.

I am therefore looking for a way to use a pre-defined query instead of the
SQL or I need to use the SQL in its own module or procedure and then
figure
out how to make the changes outside of the procedure.

Here is what prompted this line of thought:

With Reports![rptRosters]
.Filter = "ID = Name of pre-defined Query"
.FilterOn = True
End With

I could easily use a select statement and get what I need, but then I
would
be redoing something that is already done.
Click to expand...


Where/How is this query going to be used?

What "slight" changes do you want to make?

If all you want to do is open a form or report with a
different set of criteria, you can use the WhereCondition
argument in the OpenForm or OpenReport methods.

Just base the form/report on the query without any criteria
and then use a statement like:

DoCmd.OpenReport "nameofreport", . . . ,
WhereCondition:= "somefield = " & somevalue
Click to expand...
 
AFAIK, you can not use a query in a form's Filter property.
However, you can use a criteria such as:

strFilter = "ID IN(Select ID FROM table Where status = 3)"

Which is usually best employed this way:

DoCmd.OpenForm "Formname", , , strFilter

The Filter property accepts the same things, but it has been
known to cause other problems. Besides, it's frequently
better to specify the criteria is the place that opens the
form/report than it is to make the form do it itself (but
you haven't provided any details so I can't be sure what's
appropriate).
--
Marsh
MVP [MS Access]


What I have in this case is an equipment ID that I will need to use several
values such as 05538B, or 05538C, or 05538D. This will change depending on
what type of equipment. This may also need to be updated in the future as
new equipment is added. So what I am looking to do is use:
.Filter = ID = strFilter

Where strFilter is the pre-defined query.

I have successfully done this on a form by useing the RowSource property of
a ComboBox. You can directly call a query here.

I have not been able to call a query on the filter property.
Dan said:
I posted something similar yesterday in the report section, trying to
figure
out how to re-use queries. It seems wasteful to me to write a SQL
statement
several times throughout a program, changing it slightly each time.

I am therefore looking for a way to use a pre-defined query instead of the
SQL or I need to use the SQL in its own module or procedure and then
figure
out how to make the changes outside of the procedure.

Here is what prompted this line of thought:

With Reports![rptRosters]
.Filter = "ID = Name of pre-defined Query"
.FilterOn = True
End With

I could easily use a select statement and get what I need, but then I
would
be redoing something that is already done.
Click to expand...

"Marshall Barton" wrote
Where/How is this query going to be used?

What "slight" changes do you want to make?

If all you want to do is open a form or report with a
different set of criteria, you can use the WhereCondition
argument in the OpenForm or OpenReport methods.

Just base the form/report on the query without any criteria
and then use a statement like:

DoCmd.OpenReport "nameofreport", . . . ,
WhereCondition:= "somefield = " & somevalue
Click to expand...
Click to expand...
 
Back
Top