B
Bayou BoB
Hi Guys;
Using all of your excellent suggestions, I have developed a form, and
now need to change it already. It's a form that generates reports
based on option group selections. The code I'm using behind the
generate report button came from all of you with wonderful solutions
to make it all possible. I'm hoping you'll now be able to help me
modify it accordingly.
As it stands, it is laid out like so:
Report Duration (3 options Month, Quarter, Year)
Report Type (2 options Detailed, Summary)
Client (2 options All Clients, Single Client)
If you select Single Client on this last option group, a combo box
appears and you select your client. Upon completion, you press the
"Generate Report" button, and up comes the preview of the report.
Essentially I have this form duplicated twice....once as listed here
for generating clients with staff hours reports. A second time to
generate the reverse report...staff with clients hours report. It
works fine having two forms, but the suggestion is now that a single
form lay this all out for us. Trouble is, I'm not sure how to code the
button with these options. The new form needs to look like such:
Report of (2 options Staff Hours, Client Hours)
Report Duration (3 options Month, Quarter, Year)
Report Type (2 options Detailed, Summary)
Client/Staff (4 options All Clients, Single Client, All Staff, Single
Staff)
From there, the dual set of combo boxes....one for Clients, one for
Staff if you chose "single client" or "single staff".
The existing code as it currently stands is as such:
Private Sub Command57_Click()
On Error GoTo Err_Command57_Click
Dim stDocName As String
Dim stWhere As String
Select Case [Frame50]
Case 1 'Detailed Report
stDocName = "CLI_DetailedAttendance_Rep"
Case 2 ' Summary Report
stDocName = "CLI_SummaryAttendance_Rep"
End Select
Select Case [Frame58]
Case 1 'All Clients
stWhere = ""
Case 2 'Single Client
If Not IsNull(Combo30) Then
stWhere = "ClientID=" & Me!Combo30
Else
MsgBox "Select a Single Client First"
Me.Combo30.SetFocus
Exit Sub
End If
End Select
If Len(stWhere) > 0 Then
stWhere = stWhere & " AND "
End If
Select Case [Frame0]
Case 1 'Current month
stWhere = stWhere & "Month([ActDate]) = " & Month(Date)
stWhere = stWhere & "And Year([ActDate]) = " & Year(Date)
Case 2 'Current quarter
stWhere = stWhere & "Year([ActDate])=&Year(Date)"
stWhere = stWhere & " And DatePart('q',[ActDate])= " &
DatePart("q", Date)
Case 3 'Current year
stWhere = stWhere & "Year([ActDate]) = " & Year(Date)
End Select
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_Command57_Click:
Exit Sub
Err_Command57_Click:
MsgBox Err.Description
Resume Exit_Command57_Click
End Sub
The two staff reports that it would need to select are as such:
Empl_DetailedProgram_Rep
and
Empl_SummaryProgram_Rep
If you can help, that would be tremendous. Thank you so much! I have
been in deep since starting this with zero knowledge of Access. With
your help I have a great project rounding itself out now.
Regards,
Kevin
Using all of your excellent suggestions, I have developed a form, and
now need to change it already. It's a form that generates reports
based on option group selections. The code I'm using behind the
generate report button came from all of you with wonderful solutions
to make it all possible. I'm hoping you'll now be able to help me
modify it accordingly.
As it stands, it is laid out like so:
Report Duration (3 options Month, Quarter, Year)
Report Type (2 options Detailed, Summary)
Client (2 options All Clients, Single Client)
If you select Single Client on this last option group, a combo box
appears and you select your client. Upon completion, you press the
"Generate Report" button, and up comes the preview of the report.
Essentially I have this form duplicated twice....once as listed here
for generating clients with staff hours reports. A second time to
generate the reverse report...staff with clients hours report. It
works fine having two forms, but the suggestion is now that a single
form lay this all out for us. Trouble is, I'm not sure how to code the
button with these options. The new form needs to look like such:
Report of (2 options Staff Hours, Client Hours)
Report Duration (3 options Month, Quarter, Year)
Report Type (2 options Detailed, Summary)
Client/Staff (4 options All Clients, Single Client, All Staff, Single
Staff)
From there, the dual set of combo boxes....one for Clients, one for
Staff if you chose "single client" or "single staff".
The existing code as it currently stands is as such:
Private Sub Command57_Click()
On Error GoTo Err_Command57_Click
Dim stDocName As String
Dim stWhere As String
Select Case [Frame50]
Case 1 'Detailed Report
stDocName = "CLI_DetailedAttendance_Rep"
Case 2 ' Summary Report
stDocName = "CLI_SummaryAttendance_Rep"
End Select
Select Case [Frame58]
Case 1 'All Clients
stWhere = ""
Case 2 'Single Client
If Not IsNull(Combo30) Then
stWhere = "ClientID=" & Me!Combo30
Else
MsgBox "Select a Single Client First"
Me.Combo30.SetFocus
Exit Sub
End If
End Select
If Len(stWhere) > 0 Then
stWhere = stWhere & " AND "
End If
Select Case [Frame0]
Case 1 'Current month
stWhere = stWhere & "Month([ActDate]) = " & Month(Date)
stWhere = stWhere & "And Year([ActDate]) = " & Year(Date)
Case 2 'Current quarter
stWhere = stWhere & "Year([ActDate])=&Year(Date)"
stWhere = stWhere & " And DatePart('q',[ActDate])= " &
DatePart("q", Date)
Case 3 'Current year
stWhere = stWhere & "Year([ActDate]) = " & Year(Date)
End Select
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_Command57_Click:
Exit Sub
Err_Command57_Click:
MsgBox Err.Description
Resume Exit_Command57_Click
End Sub
The two staff reports that it would need to select are as such:
Empl_DetailedProgram_Rep
and
Empl_SummaryProgram_Rep
If you can help, that would be tremendous. Thank you so much! I have
been in deep since starting this with zero knowledge of Access. With
your help I have a great project rounding itself out now.
Regards,
Kevin