Limiting subreport (Access03)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Set the criteria for RequiredRN to

Like IIf([Reports]![rptMain]![txtCredential]="RN","-1","*")

Set the criteria for RequiredLPN to

Like IIf([Reports]![rptMain]![txtCredential]="LPN","-1","*")

Set the criteria for RequiredUC to

Like IIf([Reports]![rptMain]![txtCredential]="Unit Clerk","-1","*")

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.

Reply | Quote

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

Reply | Quote