Show a filtered recordset in a ListBox

  • Thread starter Thread starter Bradley C. Hammerstrom
  • Start date Start date
B

Bradley C. Hammerstrom

A2K

I need to set the RowSource of my ListBox to match the recordset on another
form, with a filter applied. How?

Currently the code below works to match the recordset, except when a filter
is applied. It still displays all records.
(Curiously, the RecordCount correctly shows the filtered number of records.)

Set db = CurrentDb
Set rs = Forms!frmPhotosPerObs.frmListObsPlaces.Form.Recordset
Me.lstImages.RowSource = "SELECT [qryListObsPlaces].[FilePath],
[qryListObsPlaces].[FileName] FROM qryListObsPlaces));"
Me.txtCount = rs.RecordCount

Brad H.
 
Have you tried setting the listbox recordset to the form recordset? I see
you're picking it up, I should think something like

Me.lstImagesRowSource = rs

might work.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress
 
Rebecca,

Close, but not quite. I get a type mismatch error with:
Me. lstImages.RowSource = rs
And if I set the RowSource to the same Recordset as the form the long way,
it doesn't pick up the filter. . .

I need help with the syntax, but wouldn't something like this help--with the
present filter concantenated to the SQL:
Me.lstImages.RowSource = "SELECT [qryListObsPlaces].[FilePath],
[qryListObsPlaces].[FileName] FROM qryListObsPlaces WHERE " & strFilter &
";"

The problem with the above is that I get prompted for the parameters of
whatever the filter fields are, which the new form doesn't "know".

Brad
 
Brad,

You may have to pick up the filter manually to build the selection criteria.
The Filter property of the original form will return a string in the correct
syntax, and you can simply append it to your select statement:

If <formName>.FilterOn then
Me.lstImages.RowSource = "SELECT....WHERE" & <formName>.Filter
Else
Me.lstImages.RowSource = "SELECT..."
End If

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress
 
Back
Top