By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Access 2007 / VBA subform syntax for criteria question

P: 57
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
Share this Question
Share on Google+
2 Replies


patjones
Expert 100+
P: 931
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.

Pat
Jul 25 '10 #2

NeoPa
Expert Mod 15k+
P: 31,275
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.