User Selectable Report Printing

[Brian via AccessMonster.com]

I have created a query that collects and a report that desplays all of the
collected information from the query.

I would like to be able to view the data pulled from that query using
different fields (these option buttons enable a textbox for which the user
can enter what Date, what Route Number, etc.):

Invoice Date - usually Date()

Route Number - number assigned to a specific delivery route

Invoice Number - to see a specific customers order

Now I would like to display that same information in different formats:

Product Labels - little stickers that are placed on the units for delivery

Weight Sheets - printed after the Product Labels and used to record the
weights of the items being delivered (our product is sold $/lb)

Run Sheets - printed before the drivers load the trucks, so they can mark
which item was loaded (we don't have UPS type scanners, etc.)

The office employees are picky, they also want to be able to sort this
information by:

Category - all of the same type of product will be grouped together

Route Number - each route will be grouped together

 

[Brian via AccessMonster.com]

I realized that I don't need to have multiple "sort" filters, each format
only will have one way to be sorted. Because this is the case, will I need
9 queries (which is ok w/me), one for each of the possible:

Invoice Date & Run Sheets
Invoice Date & Weight Sheets
Invoice Date & Product Labels
Route Number & Run Sheets
Route Number & Weight Sheets
Route Number & Product Labels
Invoice Number & Run Sheets
Invoice Number & Weight Sheets
Invoice Number & Product Number

Also, I'm not sure how to call up the report once everything is chosen.
Should I use "if statements"?

IE: if (optInvoiceDate.Checked = True & optRunSheets.Checked = True)

I can't use that though, there's no "Checked" in the list that pops up when
you hit the "." HELP! Why is that not here? I thought it was based on
VB.NET?

Thanks guys and dolls,
Brian

 

[kelly draper via AccessMonster.com]

if you are referring to checkbox's, checkbox's dont actually have a
..checked / .unchecked property. they just go straight into the true/false
or yes/no or -1/0 (all the same)
example:
if NameOfCheckbox = true .... (this would check to see if the checkbox is
checked)

i guess in your particular example it would be:
if optInvoiceDate= true then....

as far as your query question, you dont necessarily have to create
different queries for different reports. just create 1 report with your
entire table as the recordsource and then in your form use if/thens to open
that report with different where conditions. such as:

if (optInvoiceDate=true and optRunSheet=true) then SomeVariableName="
[SomeField=SomeThing] and [SomeOtherField]=Something"

docmd.openreport "YourReportName",AcViewPreview,,SomeVariableName

or if you need it to be ordered by a certain field then you could use in
your if/then:
SomeVariable="SELECT * FROM YourTable WHERE ((([yourtable].[somefield])
=somecriteria) AND (([yourtable].[someotherfield])=somecriteria)) ORDER BY
[yourtable].[FieldToBeSortedBy];"

 

[Brian via AccessMonster.com]

Thanks Kelly.

Sorry about that, I wasn't referring to checkboxes. I am using
option/radio buttons.
Also, the query I created is pulling information from 4 tables, I haven't
actually created any tables (I am modifying a MS Access based program). I
want this form to "call up" the query and display the information that was
selected on the form. My thought was along the lines of changing the
"Criteria" of a field in the query so that it would display only what is
wanted/needed at that time - which is what you gave examples of.
Do Radio Buttons and Check Boxes both have the "= True"?

Can anyone explain what the differences between what I'm used to (VB.NET)
and whatever it is I'm working in now? Everything seems so foreign to me!
This program is telling me it's VB 6.3, is this why I am getting confused?
VB6 and VB.NET?

 

[kelly draper via AccessMonster.com]

since you cant re-query a query so-to-speak, you might have to create those
9 queries you spoke of programmatically and assign them to the forms
recordsource. like: if 'user selects one thing' then querystring="copy and
paste the query of your base form from SQL view but change the parts to
reflect a query for this set of user options"

if "user selects another set of options' then querystring="copy and paste
and change to reflect this set of options"
etc.
docmd.openReport "TheReportYouWantToOpen",,,querystring

as far as radio buttons, yeah. they use the RadioButton=true/false as well.
if your using them inside an option group, then i personally like to use
SELECT CASE to process option group selections.
such as:
select case NameOfOptionGroup
case 1
querystring="copy and paste and change to fit"
case 2
querystring= etc...
end select
docmd.openReport "TheReportYouWantToOpen",,,querystring

as far as the difference between VB.net and access's VB, I'm not to sure as
I am not well versed in VB.net. perhaps one of the MVP's could answer that
one and we can both get educated.

 

[Brian via AccessMonster.com]

I remember the select case method, but I am using two option groups, can I
set it up like so?

---------------------------------------------
Select Case optGroup1
Case 1
If optButton1 = True Then 'optButton1 and 2 would be in optGroup2
querystring = qryButton1 'qryButton1 and 2 are the corresponding_
queries
If optButton2 = True Then
querystring = qryButton2
Case 2
If optButton1 = True 'etc
---------------------------------------------

Basically, my two questions now are can I "nest" option groups and how do I
pick the query I want to use? I dont have an SQL view, or I don't know how
to get there - sometimes I get the feeling I learned nothing in school...

Thanks for your help, Kelly.