Automatically cycle through a list of Parameters

  • Thread starter Thread starter KWhamill
  • Start date Start date
K

KWhamill

I know this sounds LAZY, But I have this one parameter query to produce these
reports and I always use the same parameters. so what i would like, rather
than recreating this query 10 or 20 times with all the possible Combinations
of parameters. I would like to find a way to this in VBA. Right now i have to
type the parameters as the macro goes along and I often forget where i left
off. So my question is, is there a way to have VBA cycle through the
Parameters automatically or do i need to create the search in VBA for each
report? I'm sure someone has done this before.
Thank you,
K
 
If you build a form, and enter in the values, then in the query, you can
type in:


City = forms!MyPromptFormName!City

So, those parameters can point to a form, and you just type in the
values...and then run your code/queries.....

If you want to write some code...then you can do the follwing:

Simply use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..

So, you can build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typically designed for user interface
face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar controls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere
 
I'd:
Make a table of the parameters
make a form based on the table
add a field to the form wth the parameter
change the criteria in the qry to point to the field on the form
write code that steps through each record (docmd.gotorecord next)
and runs the query (docmd.openquery)

you'd have to add some code to stop before you error on EOF but there you go.

might be easier to write ten queries. But my way you can change or add (add
even hundreds) of params later.
hth
 
This sounds like a good idea but for a different reason. In This case the
parameters do not change. They are lists of transactions, the first parameter
wants to know what to exclude and there are only two choices, we'll call them
A and B. The second parameter also never changes and has five choices, we'll
call them A through E. so you can see I'm just going through all the possible
combinations of Parameters one and two and it takes so long that i get lost
if i don't write down where i'm at as i go along. You're right it may just be
easier to write all the queires.
The reason your idea sounds promising: My boss likes to produce this
cumulative batch file based on the transactions previously reffered to. This
thing gets bigger all the time, and its too big for Excel even though that's
how he wants to keep it. I 've been using Access to break it down into small
enough chuncks. So what I have to do is Filter by a parameter and see how
many rows i have if i have more than excell will hold on a single sheet I
filter by another parameter, and so on. I think the Method you suggested
could automate that very well if i could get it to count the rows itself.
 
Thank you,
OK That's alot to absorb but if i understand you correctly you are
suggesting that I:
1 write the Query without parameters
2 make a form for launching the job ( would actually be a spreadsheet export)
3 and code the button on the form to run the query with all the permutations
of paramteters I want.
That's what I'm going to go try and do now. But i'm fairly novice when it
comes to VBA so Wish me luck.
Thanks,

Albert D. Kallal said:
If you build a form, and enter in the values, then in the query, you can
type in:


City = forms!MyPromptFormName!City

So, those parameters can point to a form, and you just type in the
values...and then run your code/queries.....

If you want to write some code...then you can do the follwing:

Simply use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..

So, you can build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typically designed for user interface
face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar controls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere
Click to expand...
 
KWhamill said:
Thank you,
OK That's alot to absorb but if i understand you correctly you are
suggesting that I:
1 write the Query without parameters
Click to expand...

yes, but I also 1st suggested that if you do NOT want to write code, then in
the query builder, for a parameter, simple go:


City = forms!FormReportPrompt!txtCity

In other words, **if** you don't want to write any code, then use a form
+ parameters as above.
2 make a form for launching the job ( would actually be a spreadsheet
export)
Click to expand...

In the above, we now not sending the results to a report, or form, so the
"where" clause code suggestion(s) are not going to work very well. This
means your problem is more difficult since we cant use the "where" clause.
Thus, I would suggest you go back to suggestion #1.....
3 and code the button on the form to run the query with all the
permutations
of paramteters I want.
Click to expand...

Ah, see, the problem with suggestion #1 is when you want to "ignore" one of
the several parameters.....
 
Back
Top