Trying to use a query in a report from a screen. 1

[skyhighe]

I have a screen [switchboard] which supplies a call to a screen which takes the reporting facility and division from the user by them selecting radio buttons. I then want to build a "where clause" for the particular report that has been chosen from the switchboard and put the filter on the query for that report. Below is my confused attempt:

Me.Visible = False
'Check to see what the value of the check boxes are and set the
'appropriate value in the hidden control to pass to the query
If Me!grpDivision = 1 Then
Me.txt_Division = 2
ElseIf Me.grpDivision = 2 Then
Me.txt_Division = 61
Else
Me.txt_Division = 99
End If
'Check to see what the value of the check boxes are and set the
'appropriate value in the hidden control to pass to the query
If Me!grpFacilities = 1 Then
Me.txt_Facility = 1
ElseIf Me.grpFacilities = 2 Then
Me.txt_Facility = 10
Else
Me.txt_Facility = 99
End If

Build_Where_Clause
ReportName_Hold = "Admit Labels Master"
DoCmd.SelectObject acReport, "Labels Admit Master"
DoCmd.ApplyFilter ReportName_Hold, wherecondition:=WhereClause
DoCmd.OpenReport Me.txt_ReportName

Exit_cmdReportOptions_Click:
Exit Sub

Err_cmdReportOptions_Click:
MsgBox Err.Description
Resume Exit_cmdReportOptions_Click

End Sub
'Function IsFormOpen(strForm As String)
' ' Is the form open?
' IsFormOpen = (SysCmd(acSysCmdGetObjectState, acForm, strForm) <> 0)
'End Function
Rem*
Rem*******************************************************************
Rem*
Rem* Build the Where clause for the report
Rem*
Rem*
Rem*
Rem*******************************************************************
Rem*
Private Sub Build_Where_Clause()
WhereClause = &quot; (((EVENT.EVENT_TYPE)=10 Or (EVENT.EVENT_TYPE)=30) AND ((EVENT.EVENT_CREATE_DT)>=[Enter Processing Date] And (EVENT.EVENT_CREATE_DT)<DateAdd(&quot;&quot;d&quot;&quot;,[Enter Processing Date],1)) AND ((EVENT.EVENT_SOURCE)=&quot;&quot;G&quot;&quot) &quot;
If Forms!sbfReport_Selection_Criteria.txt_Division = 1 Then
WhereClause = WhereClause & &quot; AND location_rprt_div = 1 &quot;
End If
If Forms!sbfReport_Selection_Criteria.txt_Division = 2 Then
WhereClause = WhereClause & &quot;location_rprt_div = 2 &quot;
End If
If Forms!sbfReport_Selection_Criteria.txt_Facility = 1 Then
WhereClause = WhereClause & &quot;locaton_rpt_fac_id = 1&quot;
End If
If Forms!sbfReport_Selection_Criteria.txt_Facility = 2 Then
WhereClause = WhereClause & &quot;locaton_rpt_fac_id = 2&quot;
End If
If Forms!sbfReport_Selection_Criteria.txt_Division = 99 Then
If Forms!sbfReport_Selection_Criteria.txt_Facility = 99 Then
WhereClause = WhereClause & &quot;1 = 1&quot;
End If
End If
End Sub


What I get is &quot;You can't use the applyfilter action on this window &quot; with a bunch of meaningless text after that.

 

[JerryDennison]

You apply the filter when you open the report. The ApplyFilter command can only be used on a report that is already open and would have to be applied, close and save the report and then reopen. A much more efficient and easier way is to use the following syntax when opening the report:

DoCmd.OpenReport &quot;ReportName&quot;,,,&quot;Where Clause&quot;

NOTE: the &quot;Where Clause&quot; is a SQL where statement without the word WHERE.