Using forms for selection criteria

G

Guest

Hello,

I'm trying to create a form that will allow end-users to pick and choose
their selection criteria to generate a report. (This includes date range.)
What would be the best way to make this work? I'm trying to avoid making
multiple macros with multiple reports. Is this a far fetched idea? I just
want to make this as easy as possible for the end users to generate reports
without having a form full of macro buttons.

Any help will be greatly appreciated.

Thanks,
Maria
 
A

Albert D. Kallal

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution is actayll to use your idea..and make a nice prompt form!!

You then build your own where clause in code. That way, you simply design
the reports (or forms), and attached them to the query, BUT 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.

Take a look at the following screen shots to see what I mean:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

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

and, for a date range...

if strWhere <> "" then
strWhere = strWhere " and "
end if

strWhere = "(InvoiceDate >= #" & format(me.startDate,"mm/dd/yyyy") & "#)" &
_
" and (InvoiceDate <= #" & format(me.endDate,"mm/dd/yyyy")
& "#)"


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.
 
H

hermie

Albert D. Kallal said:
As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution is actayll to use your idea..and make a nice prompt form!!

You then build your own where clause in code. That way, you simply design
the reports (or forms), and attached them to the query, BUT 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.

Take a look at the following screen shots to see what I mean:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

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

and, for a date range...

if strWhere <> "" then
strWhere = strWhere " and "
end if

strWhere = "(InvoiceDate >= #" & format(me.startDate,"mm/dd/yyyy") & "#)" &
_
" and (InvoiceDate <= #" & format(me.endDate,"mm/dd/yyyy")
& "#)"


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.



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Click to expand...
 
H

hermie

Albert FYI
The page cannot be displayed???
Herman
Albert D. Kallal said:
As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution is actayll to use your idea..and make a nice prompt form!!

You then build your own where clause in code. That way, you simply design
the reports (or forms), and attached them to the query, BUT 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.

Take a look at the following screen shots to see what I mean:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

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

and, for a date range...

if strWhere <> "" then
strWhere = strWhere " and "
end if

strWhere = "(InvoiceDate >= #" & format(me.startDate,"mm/dd/yyyy") & "#)" &
_
" and (InvoiceDate <= #" & format(me.endDate,"mm/dd/yyyy")
& "#)"


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.



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Click to expand...
 
C

Con Giacomini

You could use the 'Filter by Form' technique to make the selections for your
report. Then a command button to print the report per your selections. The
command button code would be:

Private Sub Command349_Click()
DoCmd.OpenReport "Filter by Form Report", acViewPreview, , Me.Filter
End Sub


Con Giacomini
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting multiple criteria in a report in Acc 2007 using a form 1
multiple selection for report criteria 2
two questions on exporting from a form 3
Problem with Report and Forms 2
Form to Enter Report Criteria works, but Query won't run from Macr 1
Date parameters for report 2
Criteria Form Question 4
Create a UserQueryForm 1

Top