Before and after filter ...

[action]

I have a great Search form from this forum (Mile-o-phile) which is working very well except the fields in the display change after the filter is applied.

It is a listbox with the Row source as:
SELECT Members.ID, Members.Surname, Members.FirstName, Members.PhoneHome, Members.Fax FROM Members;

A filter button with the code:

Private Sub cmdSearch_Click()

If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Member Search"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM members WHERE [Surname] Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
.Requery
End With

lblResults.Caption = "Members Search Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)

This works well,

Except the fields displayed after the filter in the listbox are other the feilds in the table, not the orginal rowsource. The fields displayed are in order of the orginal table

How can I limit the after filter display to the fieilds listed in the orginal rowsource? Note, I can not change the table's format as it is a linked table.

Thanks

 

[dcx693]

The part of your code that controls the fields that display in lstResults is this:
.RowSource = "SELECT * FROM members...

The * in that statement chooses all the fields from the table called members. However, you just want the fields called ID, Surname, FirstName, PhoneHome, Fax so try replacing the * in that statement. Use something this:
.RowSource = "SELECT Members.ID, Members.Surname, Members.FirstName, Members.PhoneHome, Members.Fax FROM members WHERE [Surname] Like '" & _

 

[action]

To obvious for me! cheers