How to use combo box and client selection

[Bayou BoB]

Hello!

I have a form that is set up to allow users to choose reports to
print. I have an option group that allows a user to select the
duration of the report period. By Month, By Quarter and By Year. I
have two seperate buttons on this form. The first allows the user to
simply select the duration of the report, and it prints the summary
for every client that's still active in the database. A second button
is designed to print just a single client's summary when the client is
selected by their ClientID from a dropdown box, and when a report
period is picked via the option group. Here is the code behind the
button. At the moment it is not allowing the individual client to be
printed. Where have I written this incorrectly? For the button that
just prints all of the clients, everything seems to work as wanted.

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "CLI_DetailedAttendance_Rep"
Select Case [Frame0]
Case 1 'Current month
stWhere = "Month([Date]) = " & Month(Date) & "And Year([Date])
= " & Year(Date)

Case 2 'Current quarter
stWhere= (the code I have on current quarter is incorrect and
does not function properly. Perhaps you might suggest some proper code
for this option)

Case 3 'Current year
stWhere = "Year([Date]) = " & Year(Date)

End Select
DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!ClientID,
stWhere

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click



Many Thanks!!

Kevin

 

[SteveS]

Kevin,

FYI - using 'Date' as a field name in a table will cause
you major headaches! Date is an Access reserved word;
Access gets confused when you use a reserved word for a
field name, form name, control name, etc.
I'm use dteDate for the table field name.

The OpenReport statement has too many arguments:

DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!
ClientID, stWhere

The "ClientID=" & Me!ClientID IS the where argument.

And I don't think Me!ClientID can be used. Try using this
statement to see if the right client is selected:

DoCmd.OpenReport stDocName, acPreview, , "ClientID=" &
Forms![yourFormName]!ClientID

If the right client is selected, you can add the date
selection. After the End Select, you could use:
stWhere = "ClientID=" & Forms![yourFormName]!ClientID & "
And " & stWhere

For the Current quarter, try:

stWhere = "Year([dteDate ])= " & Year(Date) & " And
DatePart('q',[dteDate ])=" & DatePart("q",Date)


HTH

Steve