Runtime error 2448 You can't assign a value to this object.

[NeilPattison]

Hi all,

I have a form that runs from a query which combines data from 2 tables. I am trying to set up a filter on the form using a number of combo boxes and text boxes and a command button to fire it but I keep getting the above error (on the .Filter = strFilter line) and I can't see what the problem is with my code.

Private Sub cmdApplyFilter_Click()

Dim strSSType As String
Dim strArea As String
Dim strDepot As String
Dim strStatus As String
Dim strRisk As String
Dim strZone As String
Dim strContractor As String
Dim strFilter As String

If IsNull(Me.cboSSType.Value) Then
strSSType = "'Like '*'"
Else
strSSType = "='" & Me.cboSSType.Value & "'"
End If

If IsNull(Me.cboArea.Value) Then
strArea = "'Like '*'"
Else
strArea = "='" & Me.cboArea.Value & "'"
End If

If IsNull(Me.cboDepot.Value) Then
strDepot = "'Like '*'"
Else
strDepot = "='" & Me.cboDepot.Value & "'"
End If

If IsNull(Me.cboStatus.Value) Then
strStatus = "'Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

If IsNull(Me.cboRisk.Value) Then
strRisk = "'Like '*'"
Else
strRisk = "='" & Me.cboRisk.Value & "'"
End If

If IsNull(Me.cboZone.Value) Then
strZone = "'Like '*'"
Else
strZone = "='" & Me.cboZone.Value & "'"
End If

If IsNull(Me.cboContractor.Value) Then
strContractor = "'Like '*'"
Else
strContractor = "='" & Me.cboContractor.Value & "'"
End If

strFilter = "[subSubstationType] " & strSSType & "AND [subArea] " & strArea & "AND [subDepot] " & strDepot & "AND [subStatus] " & strStatus & "AND [subRiskLevel] " & strRisk & "AND [subZone] " & strZone & "AND [subContractor] " & strContractor

With Forms![frmSubInfo]
.Filter = strFilter
.FilterOn = True
End With

End Sub


Any help on this would be greatly appreciated.

 

[TheAceMan1]

How are ya NeilPattison . . .

Try cutting the filter off 1st!:

[blue]        With Forms![frmSubInfo]
            [purple][b].FilterOn = False[/b][/purple]
            .Filter = strFilter
            .FilterOn = True
        End With[/blue]

If the above doesn't work, post the SQL of the forms recordsource.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

I'd put a space before each AND:
strFilter = "[subSubstationType] " & strSSType & " AND [subArea] " & strArea & " AND [subDepot] " & strDepot & " AND [subStatus] " & strStatus & " AND [subRiskLevel] " & strRisk & " AND [subZone] " & strZone & " AND [subContractor] " & strContractor

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

 

[TheAceMan1]

PHV . . .

Good Eyes! . . . I sure missed that one.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

NeilPattison . . .

If you'd like to trim down your code, perform the following:
[ol][li]In the [blue]Tag[/blue] property of the [blue]combo's of interest[/blue], enter the associated fieldname in brackets [purple][][/purple] (no quotations please!).

Example: In the tag property of [blue]cboSSType[/blue], enter [purple][subSubstationType][/purple].[/li]
[li]Then replace your code with the following:

[blue]   Dim ctl As Control, Pack As String

   For Each ctl In Me.Controls
      If ctl.Tag <> "" Then
         If Trim(ctl & "") <> "" Then
            If Pack <> "" Then
               Pack = Pack & " AND (" & ctl.Tag & " = '" & ctl & "')"
            Else
               Pack = "(" & ctl.Tag & " = '" & ctl & "')"
            End If
         End If
      End If
   Next

   If Pack <> "" Then
      With Forms![frmSubInfo]
         .FilterOn = False
         .Filter = Pack
         .FilterOn = True
      End With
   End If[/blue]

[/li]
[li]Done![/li][/ol]
[blue]Your Thoughts? . . .[/blue]

BTW: be sure to go over one of the links at the bottom of my or [blue]PHV's[/blue] post. It concerns etiquette here in the forums.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886