Filter with If then else statement does not work

[puppy39]

I have a form by Primary Companies and a subform with the many locations of the Primary Company. These locations are by state. I added a unbound combo box that is using a state table. I would like the user to select the state and then filter the subform based on the state selected. If the company does not have a location in the state then it does a message pops up "State Not Found". If there is a match then Filter the subform and message "Filter Applied" I can't get the else to work. It only executes the first if.

Private Sub cboState_Click()

Dim scboState
Dim sState

scboState = Me.cboState
sState = Me.State

If scboState <> sState Then
FilterOn = False
MsgBox "State Not Found"
Me.cboState.value = ""
ElseIf scboState = sState Then
Filter = "sState =" & scboState
FilterOn = True
MsgBox "Filter Applied"
End If

End Sub

 

[dick606]

puppy,

you do not need a filter to do this. All you need in your subform is an SQL statement with a WHERE clause in it as your recordsource, and then one line of code when you select the combo value. Like this:

SELECT [whatever] FROM table
   WHERE [state] = forms!mainformname!comboname;

Then on the afterupdate of the combo, write:

me.subformContainerName.requery

you can add in your IF() statement as you wish.

I like doing things this way because filters are such a pain in the "you know what".

 

[TheAceMan1]

How are ya puppy39 . . .

Does the combobox reside in the [blue]mainform[/blue] or [blue]subform?[/blue]

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

See Ya! . . . . . .

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

 

[puppy39]

The combo box resides in the subform. I don't know a lot about SQL Statements. Should the code look like this?

Private Sub cboState_Click()

SELECT * FROM table
WHERE [state] = forms!mainformname!cboState;

MsgBox "Filter Applied"

End Sub

 

[TheAceMan1]

puppy39 . . .

Since yo appear to understand filters already, I'll take that path. Just be sure to study up on [blue]Queries[/blue] and [blue]SQL[/blue] ... the top method for extracting records with criteria.

Remove the [blue]On Click[/blue] event code completely [purple](its the wrong event to use for a combobox)[/purple], then in the combo's [blue]After Update[/blue] event, copy/paste the following:

[blue]   Dim rst As DAO.Recordset, Cri As String
   
   Me.FilterOn = False
   Set rst = Me.RecordsetClone
   
   Cri = "[State] = '" & Me.cboState & "'"
   rst.FindFirst Cri
   
   If rst.NoMatch Then
      MsgBox "State Not Found!", _
             vbInformation + vbOKOnly, _
             "State Search Failed! . . ."
      If Trim(Me.Filter & "") <> "" Then Me.FilterOn = True
   Else
      Me.Filter = Cri
      Me.FilterOn = True
      
      MsgBox "Filter Applied!", _
             vbInformation + vbOKOnly, _
             "State Found! . . ."
   End If
      
   Set rst = Nothing[/blue]

Perofrm your testing!

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

See Ya! . . . . . .

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

 

[puppy39]

I get an error message saying

Run time error 13
Type Mismatch

When I click debug it is parked
on Set rst = Me.RecordsetClone

What am I doing wrong?

 

[TheAceMan1]

puppy39 . . .

Hmmm ... The code works for me in 2K.

Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Try again ...

See Ya! . . . . . .

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