Help with Whereclause

[mrbboy]

I am using the following code to filter a form, frm_ExternalAudit_View, with a subform, fr_AuditDetails. The field that I want to filter is [Status] and this is found in the subform.

Dim stDocName As String, Whereclause As String, frm As Form, sfrm As Form

Set frm = Forms!frm_ExternalAudit_View
Set sfrm = frm!frm_AuditDetails.Form

stDocName = "frm_ExternalAudit_View"

If Not IsNull(cbo_Auditor) Then
Whereclause = "[Auditor Name] = '" & cbo_Auditor & "'"
End If

If Not IsNull(cbo_Department) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Agency] = '" & cbo_Department & "'"
End If

If Not IsNull(cbo_Agency) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Agency] = '" & cbo_Agency & "'"
End If

If Not IsNull(cbo_SingleDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Audit Date] = #" & Format(cbo_SingleDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cbo_StartDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Audit Date] >= #" & Format(cbo_StartDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cbo_EndDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Audit Date] <= #" & Format(cbo_EndDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cbo_Status) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "sfrm![Status] = '" & cbo_Status & "'"
End If

DoCmd.Close

DoCmd.OpenForm stDocName, , , Whereclause
DoCmd.Maximize

When I run the code, I get the error message "Microsoft access can't find the form 'frm_ExternalAudit_View' referred to in a macro expression or vb code." What does this mean?

Thanks.

 

[TheAceMan1]

How are ya mrbboy . . .

Your closing the current form before stDocName can be open. Try the following:

[blue]   DoCmd.OpenForm stDocName, , , Whereclause
   DoCmd.Maximize
   DoCmd.Close acForm, "CurrentFormName", acSaveNo[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mrbboy]

Aceman, I tried that but I still get the same error message.

 

[TheAceMan1]

[blue]The field that I want to filter is [Status] and this is [purple]found in the subform[/purple].[/blue]

I miss interpreted the part in [purple]purple[/purple]. Anyway, in your code the following line is causing the error:

[blue]   Set frm = Forms!frm_ExternalAudit_View[/blue]

You can't set a [blue]form object[/blue] before the form is open! Setting the objects has come after. Also, you can't send your criteria via the 4th arguement of the DoCmd.OpenForm as thats for the mainform. Since you already have your [blue]Set[/blue] lines, a little juggling should do. Try the following:

[blue]   Dim stDocName As String, Whereclause As String, frm As Form, sfrm As Form
          
   stDocName = "frm_ExternalAudit_View"
   
    [green]'Your WhereClause code here[/green]
          
   DoCmd.Close

   DoCmd.OpenForm stDocName
   DoCmd.Maximize
   DoEvents [green]'Allow time for the form to open[/green]

   Set frm = Forms!frm_ExternalAudit_View
   Set sfrm = frm!frm_AuditDetails.Form
   
   sfrm.Filter = Whereclause
   sfrm.FilterOn = True
   
   Set sfrm = Nothing
   Set frm = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mrbboy]

Aceman,

I modified the code as you suggested but now, a box prompting me for the "status" pops up. The code is listed below:

Dim stDocName As String, Whereclause As String, frm As Form, sfrm As Form

stDocName = "frm_ExternalAudit_View"

If Not IsNull(cbo_Auditor) Then
Whereclause = "[Auditor Name] = '" & cbo_Auditor & "'"
End If

If Not IsNull(cbo_Department) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Agency] = '" & cbo_Department & "'"
End If

If Not IsNull(cbo_Agency) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Agency] = '" & cbo_Agency & "'"
End If

If Not IsNull(cbo_SingleDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Audit Date] = #" & Format(cbo_SingleDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cbo_StartDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Audit Date] >= #" & Format(cbo_StartDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cbo_EndDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Audit Date] <= #" & Format(cbo_EndDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cbo_Status) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "sfrm![Status] = '" & cbo_Status & "'"
End If

DoCmd.Close

DoCmd.OpenForm stDocName
DoCmd.Maximize
DoEvents

Set frm = Forms!frm_ExternalAudit_View
Set sfrm = frm!frm_AuditDetails.Form

sfrm.Filter = Whereclause
sfrm.FilterOn = True

Set sfrm = Nothing
Set frm = Nothing

 

[TheAceMan1]

mrbboy . . .

Surely the calling [blue]form/subForm[/blue] doesn't have the same names as the form/subForm your opening!


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]