Update ListBox with Filtered Records

[Joe Was]

I have a forn with Fields, a DropDown and a ListBox. Ithe ListBox shows the Key field for the table only. If you select a key [Word] from the ListBox the form shows that records fields.

The DropDown lists the possible values for a non-key field in the table [Type]. If you select a [Type] from the DropDown only records containing that [Type] are selected with the "Next" button.

The problem is the ListBox still shows the complete Table when a Filter is on but only go's to record of the selected [Type]. I would like the ListBox to only show the Key field records where that record contains the Filtered [Type] record rather that the complete table?

Any ideas?

This is a sample of the code that filters the Table, for one [Type] based upon the forms selected DropDown [Type]:

Private Sub ShowDropDown_AfterUpdate()

If Me.ShowDropDown = "Temperature" Then
DoCmd.ShowAllRecords
DoCmd.ApplyFilter , "Type = 'Temperature'"
End If

DoCmd.Requery "ListBox_Lookup"
Me.Repaint
DoCmd.Restore
End Sub

 

[Joe Was]

Does anyone know how to set up a dynamic querry to update a list box based upon the field selected from a ComboBox DropDown or any other way of doing the above: ListBox UpDate and still preserve the ListBox's LookUp ability?

 

[bat17]

You set the listbox scource as a query that use the combobox as criteria. then on the after update event of the cobobox you requery the Listbox

HTH

Peter

 

[Joe Was]

Thanks I did that before I posted.
Its a complicated application and I must have another event stepping in or something?

But, I will try your suggestion again, Thanks.

 

[Joe Was]

Thanks Peter,
Along your lines, I had to set and re-set the RowSource for the ListBox to a ComboBox value or use the Default All-Records in several Events each time pulling the proper Query.

It now works like a charm.

Thank You for the pointer.