Selecting multiple options/Or collapsable recordsets

[jskillz25 via AccessMonster.com]

Hello all. I've got a situation I'd like to run across and see what input you
guys have. Here is my setup. I have reports for managers that I'd like them
to be able to access. There will be a summary report/page, and also 3
different kind of detail sections which have recordsets. My original idea was
to possibly have radio buttons for each of the 4 reports, and make it
possible (not sure that it is) for them to choose multiple buttons and then
click print or preview. Anyone know if this setup is doable?

The other idea that I came across was actually in another DB within my
company. I saw that they had a feature where you could double click on a
group in a summary page and it would automatically drill down to its
recordset. Basically, a collapsable/expandable summary page. However, I'm
unable to get a hold of the designer of this database and was wondering if
anyone else knows about this feature.

Let me know which of the two options you guys think is the most feasible.
Thanks all

Also, I'd like the reports to be based off of 3 choices in 3 cascading combo
boxes for manager, Agent, and groupcode

Thanks again this would be a lifesaver.

 

[OfficeDev18 via AccessMonster.com]

First things first. You can have as many radio buttons as you want. As long
as you don't put them into a single option group, you can choose an many as
you want. Radio buttons, though, are not the convention for multiple options;
checkboxes are use instead. Radio buttons are usually only used for single
options in an option group.

What you want to do, then, is to have 4 checkboxes, each labeled with a
seperate report. Also, the three combos you mentioned come here into play.
The user may check off any report(s), and may/must make choices from the
combos. If you want them to have a choice between preview and print, put this
choice into an option group with radio buttons. (Different combo choices for
different reports isn't doable. If they need to make separate choices for
different reports, they should run the routine several times.) Now put a new
command button on your form, maybe called cmdConfirm, labeled "Confirm
Choices". In the OnClick event, you want to have the following code:

Dim WClause As String

WClause = "fieldname1= '" & Me.combobox1.Value & "' And fieldname2 = '" & Me.
combobox2.Value & "' And fieldname3 = '" & combobox3 & "'"

If Me.Checkbox1 = True Then DoCmd.OpenReport "FirstReportName",
OptionGroupValue , , WClause
If.Me.Checkbox2 = True Then etc., etc.

At the end of the routine, VBA should uncheck all checkboxes, the Output
option group should be initialized to Preview, and the text of all combos
should be initialized to "".

Hope this helps,

Sam

 

[jskillz25 via AccessMonster.com]

Hey Sam.

Thanks for the advice. I'm going to go w that checkbox setup.

I just had a quick related question. The three cascading combo boxes need to
be the parameters for all of the 4 reports. Is there anyway I can make this
dialog the basis of all reports? As of right now, I have the form just
running off one of the reports.

I hope that makes sense. Basically, the combo boxes restrict all 4 reports
and I want them to apply to all reports that I have on that list.

Thanks again for the help.

 

[OfficeDev18 via AccessMonster.com]

Justin,

Certainly. WClause, in my previous posting, contains the 3 parameters, or as
you call them, restrictions, for all 4 reports. Just be sure to reference
WClause in your .OpenReport statements as I showed you.

Also, be sure to substitute your real field names, object names, etc., for my
made-up names.

Sam

 

[jskillz25 via AccessMonster.com]

Hello again Sam,

Think I've got the code with the Wclause up and running smoothly. However,
I'd like to incorporate an option group for print preview or print reports as
choices. Setting that up is easy enough, but I'm not sure where these values
get incorporated in the coding scheme. If you could point me in the right
direction, I'd greatly appreciate it and I'll get out of your hair for good.

Thanks again for all the help.

 

[OfficeDev18 via AccessMonster.com]

Sure thing, Justin, and I don't consider you in my hair either. I enjoy this.

Your reports will be opened with the following statement:

DoCmd.OpenReport "Report Name", Some Value Here.

Some Value Here can be as follows: ACViewNormal or ACViewPreview, which are
both constants. ACViewNormal has the value 0, and ACViewPreview has the value
2.

In your option group, you might have 3 radio buttons, labeled "Print",
"Preview", and "Skip." You will need to give the radios values, so give
"Print" the value 0 and the "Preview" the value 2. I have this exact setup,
by the way, in my own databases, and I give the "Skip" a value of 3. I then
cycle through my reports one by one by VBA code, and in each instance I cycle
through the OpenReport statement, which control code now looks like this:

If frmOptionGroup.Value <> 3 Then
DoCmd.OpenReport "Report Name", frmOptionGroup.Value
End If

This allows me to skip any report I don't need at the moment, and believe me
it comes in extremely handy.

You really shouldn't have any trouble implementing any of this, especially if
you're an adventurous experimenter and like to try new things.

I'm still here, though; I'm not going anywhere.

Sam

 

[jskillz25 via AccessMonster.com]

Sam,

Got the option group working. That setup is great, blows my mind that its
that easy. Just one quick question. I didn't incorporate the 'skip' in my
group. What was that for?

 

[jskillz25 via AccessMonster.com]

Also Sam,

I'd like to add an "all reports" check box. My solution in my head is to
just take all the docmd.open report statements and put them in an if
statement for that check box. Sound feasible? Thanks

 

[OfficeDev18 via AccessMonster.com]

Justin,

I'll answer them both together.

The 'skip' was in case you didn't want to print one or more reports, you can
cycle through the report names and select which ones you want to print, which
ones to only display, and which ones you wanted to ignore. Of course, you
could select print for all 4 of them.

If you want to output all 4 of them to the same device (printer or screen),
just lose the cycle code and put each report name into the same variable one
at a time in VBA, and output it automatically with the .OpenReport statement.
Like this:

(In the Option Group's OnUpdate event, or however you confirm the value of
the option group)
Dim RepName As String

RepName = "Report1"
PrRep RepName
RepName = "Report2"
PrRep RepName
RepName = "Report3"
PrRep RepName
RepName = "Report4"
PrRep RepName

Now a new subroutine:

Private Sub PrRep(RName As String)

DoCmd.OpenReport RName, Me.frmOptionGroup.Value

End Sub

It really is as easy at it sounds. Just don't be scared of it.

You'll be just fine,

Sam