Get a Combo box to filter data in a Listbox

[SpiritedAway]

Hi,

On FrmTrack I have a listbox called [SearchResults] - the row source is:

SELECT [QRYTrack].[TrackID], [QRYTrack].[Status], [QRYTrack].[Percent], [QRYTrack].[System], [QRYTrack].[SystemID], [QRYTrack].[Title], [QRYTrack].[VNumber], [QRYTrack].[Protocol], [QRYTrack].[VersionN], [QRYTrack].[DraftOption] FROM [QRYTrack];

Also on this form, I have a combobox [cbostatusfilter] which gets its info from a lookup table [tblStatus].

I would like to be able to select a status from the combo box to filter data in the listbox only where the status column in my listbox matches.

So far I've used this bit of code to no avail:

Private Sub cbostatusfilter_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SearchResults"
stLinkCriteria = "[Status]=" & "'" & Me![cbostatusfilter] & "'"
Exit_cbostatusfilter_AfterUpdate:
Exit Sub

Could anyone give me some ideas how to do this?

Thanks

Spirited Away

 

[DCrake]

First copy the rowsource string into the tag property of the listbox. Then on the afterupdate event of the combo...

Me.Listbox.Rowsource = Me.Listbox.Tag & " Where ....."

 

[SpiritedAway]

Hi DCrake,

I've used on the combo Afterupdate -

Private Sub cbostatusfilter_AfterUpdate()
Me.SearchResults.RowSource = Me.SearchResults.Tag & "Me![cbostatusfilter] & " '"
End Sub

But the data in the listbox goes blank when i select from the combo.

Can you advise me where i'm going wrong with the code?

Thanks

SpiritedAway

 

[DCrake]

it should read

Me.SearchResults.RowSource = Me.SearchResults.Tag & " WHERE [Status]='" & Me![cbostatusfilter] & "'"

 

[SpiritedAway]

Hi DCrake

I've tried the code -

Me.SearchResults.RowSource = Me.SearchResults.Tag & " WHERE [Status]='" & Me![cbostatusfilter] & "'"

But listview is still going blank on selection of combo box.

I have uploaded an mdb version of the db so you can see the problem.

Also i seem to get a debug issue with the tag property when used in code.

Any further advice would be greatly appreciated.

 

[DCrake]

Main issue was that even though you are using a code and description for the status type. You are storing the description in the table. This is wrong you should be storing the statusID in the track table.

I have got the combo box to work now but you need to resolve this issue.

 

[SpiritedAway]

Thanks DCrake for looking into this.

I will look to do as you have advised.

Thanks Once Again

SpiritedAway