Where condition on a query in VBA

[gmazza via AccessMonster.com]

Hey there,
Is it possible to have a where condition on a query in VBA?
I am opening a recordset based on my query and wanted to add a where
condition on the product type as we have many products.

So here is my statement:
Set rs = CurrentDb.OpenRecordset("qryPromotions", dbOpenDynaset, dbSeeChanges)

I want to add "where ProdType = DI"
Then after I am done the processing needed for this recordset, I want to open
another one, based on the same query, but for a different ProdType.
I don't want to have to create 10 different queries for 10 different
ProdTypes, instead, I have my main query, just wanted to add a condition on
it.
Please let me know, thanks!!

 

[Bob Quintal]

Hey there,
Is it possible to have a where condition on a query in VBA?
I am opening a recordset based on my query and wanted to add a
where condition on the product type as we have many products.

So here is my statement:
Set rs = CurrentDb.OpenRecordset("qryPromotions", dbOpenDynaset,
dbSeeChanges)

I want to add "where ProdType = DI"
Then after I am done the processing needed for this recordset, I
want to open another one, based on the same query, but for a
different ProdType. I don't want to have to create 10 different
queries for 10 different ProdTypes, instead, I have my main query,
just wanted to add a condition on it.
Please let me know, thanks!!

You will need to either copy the SQL from the saved query to a string
variable in the code, and concatenate the filter criteria, or modify
the query to include a parameter that reads a textbox from the form

so the code for the first option would look like

Dim strSQL as string
Dim strWhere as string
strSQL = "Select * from Sometable"
strWhere = "where ProdType = DI"
Set rs = CurrentDb.OpenRecordset(strSQL & " " & strWhere, _
dbOpenDynaset, dbSeeChanges)

and for the second option, you need an open form with a textbox
(which may be not visible), and criteria under the query's ProdType
field which
is Forms!formName!textboxname (with appropriate changes)

 

[Dorian]

use something like this:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryPromotions where ProdType
= 'DI'", dbOpenDynaset, dbSeeChanges)

put single quotes around D1.
also if you are just reading, you should open recordset with forward and
readonly parameters.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[gmazza via AccessMonster.com]

Thanks Dorian!!

use something like this:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryPromotions where ProdType
= 'DI'", dbOpenDynaset, dbSeeChanges)

put single quotes around D1.
also if you are just reading, you should open recordset with forward and
readonly parameters.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Hey there,
Is it possible to have a where condition on a query in VBA?

[quoted text clipped - 11 lines]

it.
Please let me know, thanks!!