Combo box to select printing reports for all clients or just one...

[Bayou BoB]

Hi Everyone.

I'm still working on building the forms that house all of the reports
that we print in a day, week, month, year. To this point I've been
using the easier way out, and opting for several buttons, when a combo
button could do the job just as nicely. As it stands, I have 4
buttons, and likely only need 1, as well as a pair of combo boxes.

I need to be able to choose either a detailed report, or a summary
report....as well as choosing for either a single client or all of the
database clients. Here is the code I've been using, and I've just put
it on two different buttons to load 2 different reports, one a
summary, one a detailed report. Buttons three and four are the same as
below without the stWhere statement. I'd lke 2 option boxes...each
with two options. One that has "all clients" and "single
client".....and another that has "detailed" and "summary". What's the
best way to do this and how would I code the Print button's onclick
property? Thanks.

Kevin

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "CLI_DetailedAttendance_Rep"
stWhere = "ClientID=" & Me!ClientID & " AND " & stWhere

DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

 

[SteveS]

Kevin,

(Did you check your post on 11/30/03 4:56:49 PM?)

Use the wizard to create an option group. Set the first
option to 'Detail' and the second option to 'Summary'. I
named the option group 'optReport'.

Create a second option group, with the first option 'All'
and the second option to 'Single Client'. I named this
option group 'optNumClient'.

You could also create a third option group for the report
period: first option 'Current Month', second
option 'Current Quarter' and the third option 'Current
Year'.

You need an unbound combo box to select a client ID.
If you want to get fancy, you can hide and unhide the
Client ID combo box using the after update event of
the 'optNumClient' option group.

You need a button to view the report. Add this code to the
OnClick event:

(WARNING: Watch for line wrap....)
(WARNING: This is air code.......)

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stWhere As String

' This selects Detail or Summary report
Select Case optReport
Case 1
stDocName = "DetailedAttendance_Rep"
Case 2
stDocName = "SummaryAttendance_Rep"
End Select


Select Case optNumClient
Case 1
stWhere = ""
Case 2
If Not IsNull(cboClientID) Then
stWhere = "ClientID=" & Me!cboClientID
Else
MsgBox "Select Client ID"
Me.cboClientID.SetFocus
Exit Sub
End If
End Select

' Add this if you have a Date Option Group
'=========================================
' add the AND if ClientID was selected
If Len(stWhere) > 0 Then
stWhere = stWhere & " AND "
End If

'change the field name in the table
' from Date to dteDate
Select Case optDate
Case 1 'Current Month
stWhere = stWhere & "Month([dteDate]) = " &
Month(Date)
stWhere = stWhere & " And Year([dteDate]) = "
& Year(Date)
Case 2 'Current Quarter
stWhere = stWhere & "Year([dteDate ])= " & Year
(Date)
stWhere = stWhere & " And DatePart('q',
[dteDate])= " & DatePart("q", Date)
Case 3 'Current Year
stWhere = stWhere & Year([dteDate]) = " & Year
(Date)"
End Select
'=========================================

DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub



This is the code to hide and unhide the Client ID combo box

Private Sub optNumClient_AfterUpdate()
Select Case optNumClient
Case 1
Me.cboClientID.Visible = False
Me.cboClientID = Null
Case 2
Me.cboClientID.Visible = True
End Select
End Sub

HTH

Steve