Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Sep 2008
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code to open form (2002)

    I am trying to write code to open a report from a form. The report opens from a query.
    I am using the Select case procedure to open a report based on the contents of one field. Each case places a phrase in a textbox on the form called Division that the query then uses to open the report correctly.

    Select Case optDivision
    Case Is = 1
    Me.Division = "Lower School"
    Case Is = 2
    Me.Division = "Middle School"
    Case Is = 3
    Me.Division = "Upper School"
    Case Is = 4
    Me.Division =

    I can make it work when I want to select case 1 -3 when it is selecting some but not all records. Case 4 is supposed to print all records but using this approach I do not know what to put in the field to make all records print. I tried

    Case Is = 4
    Me.Division = "Is Not Null"

    Didn't work. Any ideas or another approach?

    Thanks

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to open form (2002)

    I assume that you have a condition like the following in the Criteria row of the query:

    [Forms]![NameOfForm]![Division]

    where NameOfForm is the name of the form. Change this to

    Like [Forms]![NameOfForm]![Division]

    and use the following for case 4:

    Me.Division = "*"

    BTW, you can use

    Case 1

    instead of

    Case Is = 1

  3. #3
    Lounger
    Join Date
    Sep 2008
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to open form (2002)

    That worked.

    Thanks Hans

    Paul

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Code to open form (2002)

    While the solution Hans gave you is perfect for this particular situation, in the long run you will need to understand how to implement a more comprehensive solution. This involves modifying the query behind the report prior to call the report.

    Let's say you have a report "rptMyReport", which is based on a query "qryrptMyReport". From your form, you first build the SQL for the query (incorporating all the conditions you have selected on the form), change the query, then call the report. Something like this:

    strSQL = "SELECT ... FROM .... WHERE ...."
    currentdb.querydefs("qryrptMyReport").SQL = strSQL
    docmd.openreport "rptMyReport"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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