Access 2007 / VBA subform syntax for criteria question

[HSXWillH]

I'm working in Access 2007 and coming to something that's always caused me problems. I have a form titled frmUsers. There are 3 subforms that list exercise dates and exercise types for each user. My goal is for whenever you click on any of the subform listings that a report will pull up with that date's activity.

Main Form = frmUsers
Subform item = Workout_List
Subform name = frmList_Workout
OnClick field = Workout_Date in the subform
Other criteria field = UserName in the subform

Private Sub Workout_Date_Click()
DoCmd.OpenReport "rptWorkout_Header", acViewPreview, , [Workout_Date] = Forms.frmUsers.Workout_List.Form.Workout_Date And  [UserName] = Forms.frmUsers.Workout_List.Form.UserName
End Sub

What's happening is it doesn't appear to be using the Where criteria on anything, I'm getting the full rptWorkout_Header report.

What should I be doing to get this to work?


Updated --- In doing some more searching for assistance, I found the answer to treat the WHERE statement as I would any other code situation minus the WHERE statement. I've since solved this.

[patjones]

You should be submitting the filter criteria in OpenReport in the form of a string. So try:

DoCmd.OpenReport "rptWorkout_Header", acViewPreview, , "[Workout_Date] = #" & Forms.frmUsers.Workout_List.Form.Workout_Date & "# And [UserName] = '" & Forms.frmUsers.Workout_List.Form.UserName & "'"

I'm assuming that your dates are actually formatted as dates, and so enclosed the date criteria in #'s.

Pat

[NeoPa]

The parameter does indeed, need to be a string, but the values needn't be. Date values, if they are to be formatted as literals within the strings should be properly formatted as SQL dates ("\#m/d/yyyy\# - See Literal DateTimes and Their Delimiters (#)).

Private Sub Workout_Date_Click()
    DoCmd.OpenReport "rptWorkout_Header", _
                     acViewPreview, , _
                     "([Workout_Date] = Forms.frmUsers.Workout_List.Form.Workout_Date) " & _
                     "And ([UserName] = Forms.frmUsers.Workout_List.Form.UserName)"
End Sub