Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limiting subreport (Access03)

    I have the basic report/subreport. There is a link using the department. But I also need to filter the subreport based on the text information in txtCredential on the main report. The problem is that the information on the subreport is based on a query that has yes/no. Lets see if I can be clearer.

    Main report: txtCredential is a text box that will be showing RN, LPN, or Unit Clerk.
    subreport: the query,qryOrientationChecklistComponents, has RequiredRN, RequiredLPN, and RequiredUC as yes/no fields. I will not be displaying this information on the report only the name of the component these fields ae associated with.

    If the txtCredential reads RN then I need from the query qryOrientationChecklistComponents only those components where RequiredRN equals yes to be displayed.

    I think I need to do a Select Case statement on txtCredential and do it in the open event of the main form. Is this correct or is there a better way?

    I get a 2427 runtime error message. You entered an expression that has no value.

    Private Sub Report_Open(Cancel As Integer)
    DoCmd.Maximize
    Dim strSQL As String

    Select Case txtCredential 'select appropriate components based on credential
    Case "RN"
    strSQL = " SSELECT tblComponents.Name, tblComponents.PresentationType, " & _
    "tblComponentDept.RequiredRN, tblComponentDept.RequiredLPN, tblComponentDept.RequiredAide, " & _
    "tblComponentDept.RequiredUC, tblComponentDept.Department " & _
    "FROM tblComponents INNER JOIN tblComponentDept ON tblComponents.ComponentID = tblComponentDept.ComponentID " & _
    "WHERE (((tblComponentDept.RequiredRN)=Yes)) "

    Case "LPN"
    strSQL = " SSELECT tblComponents.Name, tblComponents.PresentationType, " & _
    "tblComponentDept.RequiredRN, tblComponentDept.RequiredLPN, tblComponentDept.RequiredAide, " & _
    "tblComponentDept.RequiredUC, tblComponentDept.Department " & _
    "FROM tblComponents INNER JOIN tblComponentDept ON tblComponents.ComponentID = tblComponentDept.ComponentID " & _
    "WHERE (((tblComponentDept.RequiredLPN)=Yes)) "

    Case "Unit Clerk"
    strSQL = " SSELECT tblComponents.Name, tblComponents.PresentationType, " & _
    "tblComponentDept.RequiredRN, tblComponentDept.RequiredLPN, tblComponentDept.RequiredAide, " & _
    "tblComponentDept.RequiredUC, tblComponentDept.Department " & _
    "FROM tblComponents INNER JOIN tblComponentDept ON tblComponents.ComponentID = tblComponentDept.ComponentID " & _
    "WHERE (((tblComponentDept.RequiredUC)=Yes)) "
    End Select

    ' Execute it
    DoCmd.RunSQL strSQL

    End Sub

    Thank you. Fay

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

    Re: Limiting subreport (Access03)

    I don't understand what your code tries to accomplish:

    - DoCmd.RunSQL is meant to execute an action query, i.e. a make-table, append or update query. Your SQL statements only select records, they don't have an action, so you can't use DoCmd.RunSQL.
    - " SSELECT ..." is not valid anyway.
    - Opening or running a query does not cause records to be displayed in a report or subreport.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting subreport (Access03)

    I figured I was going in the wrong direction.

    I need to limit the subreport to the records that match the person's credentials. The subreport is attached to a table that has three fields that are yes/no one each for RequiredRN, RequiredLPN, and RequiredUC. These needs to match up with text that are in a textbox, txtCredential. So if the txtCredential reads "RN" the subreport should return only those components that the RequiredRN field equals yes.

    Thank you.
    Fay

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

    Re: Limiting subreport (Access03)

    Create a query based on the table behind the subreport. Add all fields you need, including RequiredRN etc.

    Set the criteria for RequiredRN to
    <code>
    Like IIf([Reports]![rptMain]![txtCredential]="RN","-1","*")
    </code>
    Set the criteria for RequiredLPN to
    <code>
    Like IIf([Reports]![rptMain]![txtCredential]="LPN","-1","*")
    </code>
    Set the criteria for RequiredUC to
    <code>
    Like IIf([Reports]![rptMain]![txtCredential]="Unit Clerk","-1","*")
    </code>
    Save this query, and set the Record Source of the subreport to the name of the query. The Link Master Fields and Link Child Fields properties of the subreport should remain blank.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting subreport (Access03)

    Good evening Hans. New trick this time around.

    Thank you it worked. I did have to use the link fields to limit to the person's department. But it seems to be working like I need it to.

    Fay

Posting Permissions

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