How to filter form using field on subform

[mrbboy]

I have a form called frm_Mainform that is used to enter general information regarding the service of an instrument.

frm_MainForm
MainFormID <PK>
PersonPerformingService <Text>
Department <text>
ServiceDate <Date>

I now have a subform that contains:

sfrm_Instrument
InstrumentID <PK>
MainFormID <FK>
Instrument Name <text>

sfrm_Instrument is embedded in frm_MainForm

I have a search form with the following code behind a command button:

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

stDocName = "review_frm_mainform"

If Not IsNull(Me.cbo_Department) Then
Whereclause = "[Department] = '" & Me.cbo_Department & "'"
End If

If Not IsNull(Me.cbo_Person) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "PersonPerformingService = '" & Me.cbo_Person & "'"
End If

If Not IsNull(Me.cbo_Instrument) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Instrument] = '" & Me.cbo_Instrument & "'"
End If


DoCmd.OpenForm stDocName
DoCmd.Maximize
DoEvents 'Allow time for the form to open

Set frm = Forms!Review_frm_MainForm
Set sfrm = frm!frm_Instrument

sfrm.Filter = Whereclause
sfrm.FilterOn = True

Set sfrm = Nothing
Set frm = Nothing

When I run this code, I get a "Type Mismatch" error. Can anyone tell me where this error is coming from?

Thanks

Rene

 

[PHV]

Which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya mrbboy . . .

Be aware ... when accessing a combobox with a line of code such as:

[blue]Whereclause = Whereclause & "[Instrument] = '" & [purple][b]Me.cbo_Instrument[/b][/purple] & "'"[/blue]

returns the 1st column [blue](column index starts at zero)[/blue] of a combobox. I'm betting that not only is this not the column you want, but its a numeric field! [blue]Probably the reason for the error![/blue] ... as your formatting for text ... or one of the comboboxes returns numeric.

To nail this down, use the [blue]Column[/blue] property of the comboboxes. I'm betting its [blue]column(1)[/blue] for all. Since there are other errors in your code I've rewritten ... mainly due to wrong concatenation of the combo's. So perform the following:
[ol][li]In the [blue]Tag[/blue] property of the combo's, enter a question mark [blue]?[/blue] [red](no quotations please!)[/red].[/li]
[li]In the code module of the search form, copy paste the following:

[blue]Public Sub FilteredReview()
   Dim Cri As String, frm As Form, sfrm As Form, ctl As Control, fldName As String
   
   For Each ctl In Me.Controls
      If ctl.Tag = "[purple][b]?[/b][/purple]" Then
         If Trim(ctl & "") <> "" Then
            fldName = FieldName(ctl)
            
            If Cri <> "" Then
               Cri = Cri & " AND [" & fldName & "] = '" & ctl[purple][b].Column(1)[/b][/purple] & "'"
            Else
               Cri = "[" & fldName & "] = '" & ctl[purple][b].Column(1)[/b][/purple] & "'"
            End If
            
            Debug.Print Cri
         End If
      End If
   Next

   DoCmd.OpenForm "review_frm_mainform"
   DoCmd.Maximize
   DoEvents
   
   Set frm = Forms!Review_frm_MainForm
   Set sfrm = frm!frm_Instrument[purple][b].Form[/b][/purple]
   
   sfrm.Filter = Whereclause
   sfrm.FilterOn = True
   
   Set sfrm = Nothing
   Set frm = Nothing
   
End Sub

Public Function FieldName(CBx As ComboBox) As String
   FieldName = Switch(CBx.Name = "cbo_Department", "DepartMent", _
                      CBx.Name = "cbo_Person", "PersonPerformingService", _
                      CBx.Name = "cbo_Instrument", "Instrument")
End Function[/blue]

[/li]
[li]Replace the code in the command button with:

[blue]Call FilteredReview[/blue]

[/li]
[li][blue]Save the search form and perform your testing![/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

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