Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Report code

    I am trying to open a report in code when one of two fields in the report query are equal to a field on a form.

    This is the basic code I have been using.

    DoCmd.OpenReport "rptStudentActivitiesListStudents", acViewPreview, , "[Activity1] =forms!frmStudentActivitiesInformation.cbowhichcla ss" Or "[Activity2] =forms!frmStudentActivitiesInformation.cbowhichcla ss"

    I have tried several versions with no luck. Can someone help?

    Thanks.

    Paul

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Try:
    DoCmd.OpenReport "rptStudentActivitiesListStudents", acViewPreview, , "[Activity1] =forms![frmStudentActivitiesInformation]![cbowhichcla ss] Or [Activity2] =forms![frmStudentActivitiesInformation]![cbowhichcla ss]"

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    DoCmd.OpenReport "rptStudentActivitiesListStudents", acViewPreview, , "[Activity1] =forms![frmStudentActivitiesInformation]![cbowhichclass] Or [Activity2] =forms![frmStudentActivitiesInformation]![cbowhichclass]"

    It says I have a syntax error.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What about

    Code:
    DoCmd.OpenReport "rptStudentActivitiesListStudents", acViewPreview, ,  "[Activity1] =" & forms![frmStudentActivitiesInformation]![cbowhichclass] &" Or [Activity2] =" & forms![frmStudentActivitiesInformation]![cbowhichclass]
    The References to the form controls are outside the double quotes, so it is the value of the control that is used in the where clause.

    Assumes that the Activities are numbers. If they were text values we would need to use chr(34) in several places.

    Once the Where starts to get a bit messy, it is a good idea to use a string variable.
    It makes it possible to debug.
    Code:
    Dim strwhere as string
    strwhere = "[Activity1] =" &  forms![frmStudentActivitiesInformation]![cbowhichclass] &" Or  [Activity2] =" &  forms![frmStudentActivitiesInformation]![cbowhichclass]
    debug.print strwhere
    DoCmd.OpenReport "rptStudentActivitiesListStudents", acViewPreview, , strwhere
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your code did not seem to work.

    Let me explain what I am trying to do. See Sample.

    I have a table with three fields - Activity1, Activity2, Activity3.

    From a form I want to select an activity from a drop downbox and if that activity is listed in any of the three Activity Fields I want it to print the LNFN associated with the Activity in a report.

    I can get the report to print for one field. I can not get the code correct to print the information from all three fields.

    Thanks.

    Paul
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In my previous post post, I said that I was assuming that Activities were actually numbers. They are not numbers, they are text, so they need to be surrounded by quotes. Chr(34) does that.
    This code works for me.
    Code:
    Dim strwhere As String
    strwhere = "([Activity1] =" & Chr(34) & Forms![frmStudentActivitiesInformation]![cboWhichclass] & Chr(34) & ") Or  ([Activity2] =" & Chr(34) & Forms![frmStudentActivitiesInformation]![cboWhichclass] & Chr(34) & ") Or  ([Activity3] =" & Chr(34) & Forms![frmStudentActivitiesInformation]![cboWhichclass] & Chr(34) & ")"
    Debug.Print strwhere
    DoCmd.OpenReport "rptStudentActivitiesListStudents", acViewPreview, , strwhere
    Last edited by johnhutchison; 2011-02-01 at 20:08.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your code works great!

    Thank you very much for the help.

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •