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/AlbertKal.../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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

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


"KWhamill" wrote:

> 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

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.
"roger" wrote:

> 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
>
>
> "KWhamill" wrote:
>
> > 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

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" wrote:

> 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/AlbertKal.../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
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

"KWhamill" <(E-Mail Removed)> wrote in message
news:2E791261-96BD-429B-BC25-(E-Mail Removed)...
> 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

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)

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.

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

> 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 (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)