Filtering a filtered list...

[sTrongFuse]

I have three linked tables [Regulator], [RuleBook] and [Rules] (each has a corresponding form for data entry).

In the "Rules" form, when you select name of the regulator, the rule books dropdown down is filtered to show only the rulebooks for that regulator. the code I use is:

Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator

Me.[ShortReg] = Me.Regulator.Column(3)
End Sub

Basically, if I select the FSA as the regulator, The Rule Books drop down is filtered to only show the FSA Rule Books. If I pick OFT, I get the OFT list etc. This all works fine.

In the [RuleBook] table, each entry has a yes/no tick box called "Active".

The reason for this being that the regulators change their rule books quite regularly, but from an audit perspective, I can't delete or overwrite the old ones once they are no longer valid.

What I want to do, is further filter the rule book list to those where Active=True so that when I select FSA, I can only choose from their current rule books and not the ones that are no longer relevant.

Again, it probably should be easy, but the mechanics of it are eluding me and the air around my desk is turning particularly blue.

Any tips or suggestions?

 

[boblarson]

Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator [B][COLOR=red]& " AND [Active]=True"[/COLOR][/B]

Me.[ShortReg] = Me.Regulator.Column(3)
End Sub

 

[pr2-eugin]

Try this code..

Private Sub Regulator_Change()
    Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
                            " [RuleBook] WHERE Regulator = " & Me.Regulator[B][COLOR=Red] & " AND Active = True"[/COLOR][/B]
    Me.[ShortReg] = Me.Regulator.Column(3)
End Sub

 

[sTrongFuse]

Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator [B][COLOR=red]& " AND [Active]=True"[/COLOR][/B]

Me.[ShortReg] = Me.Regulator.Column(3)
End Sub

Thanks. It was the " between the & and AND that was throwing me... :banghead:

 

[sTrongFuse]

Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator [B][COLOR=red]& " AND [Active]=True"[/COLOR][/B]

Me.[ShortReg] = Me.Regulator.Column(3)
End Sub

Turns out my thanks were premature, when I reopen the "Rules" Form, the Rule Book field has been reset back to blank...

 

[boblarson]

First off. I didn't notice before but it shouldn't be in the CHANGE event, it should be in the combo's AFTER UPDATE event.

Second, code will only change it for the immediate time frame. When you close the form and reopen, it will use what has been set as the row source in the combo's row source property.

 

[sTrongFuse]

First off. I didn't notice before but it shouldn't be in the CHANGE event, it should be in the combo's AFTER UPDATE event.

Second, code will only change it for the immediate time frame. When you close the form and reopen, it will use what has been set as the row source in the combo's row source property.

OK, I get that. Next question I guess is, how do I make it stick? I have other forms where a similar approach with a single filter criteria works.

 

[sTrongFuse]

OK, I get that. Next question I guess is, how do I make it stick? I have other forms where a similar approach with a single filter criteria works.

Actually, ignore that, the caffeine has kicked in and I've managed to fix the row source setting myself.

Thanks again.

T