469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Access 2007 / VBA subform syntax for criteria question

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Workout_Date_Click()
  2. DoCmd.OpenReport "rptWorkout_Header", acViewPreview, , [Workout_Date] = Forms.frmUsers.Workout_List.Form.Workout_Date And  [UserName] = Forms.frmUsers.Workout_List.Form.UserName
  3. 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.
Jul 25 '10 #1
2 1831
931 Expert 512MB
You should be submitting the filter criteria in OpenReport in the form of a string. So try:

Expand|Select|Wrap|Line Numbers
  1. 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.

Jul 25 '10 #2
32,181 Expert Mod 16PB
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 (#)).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Workout_Date_Click()
  2.     DoCmd.OpenReport "rptWorkout_Header", _
  3.                      acViewPreview, , _
  4.                      "([Workout_Date] = Forms.frmUsers.Workout_List.Form.Workout_Date) " & _
  5.                      "And ([UserName] = Forms.frmUsers.Workout_List.Form.UserName)"
  6. End Sub
Jul 29 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by wwwords | last post: by
4 posts views Thread by lupo666 | last post: by
8 posts views Thread by elias.farah | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.