Access Filter Listbox

Murray Brown
Murray Brown used Ask the Experts™
on
Hi. I have an Access ListBox with the following RowSource. I want 5to filter by specific  ArmPK on opening the form. Do I reset the Row Source or is there a better way to do this?

SELECT t_Arms.ArmPK, t_Arms.ArmType, t_Arms.ActionType, t_Arms.Manufacturer, t_Arms.Calibre1, t_Arms.SerialNo, t_Arms.SerialNo2
FROM t_Arms
WHERE (((t_Arms.ArmType) Like "*" & [forms]![f_List_Arm]![txtSearchArm].[Text] & "*")) OR (((t_Arms.ActionType) Like "*" & [forms]![f_List_Arm]![txtSearchArm].[Text] & "*")) OR (((t_Arms.Manufacturer) Like "*" & [forms]![f_List_Arm]![txtSearchArm].[Text] & "*")) OR (((t_Arms.Calibre1) Like "*" & [forms]![f_List_Arm]![txtSearchArm].[Text] & "*")) OR (((t_Arms.SerialNo) Like "*" & [forms]![f_List_Arm]![txtSearchArm].[Text] & "*")) OR (((t_Arms.SerialNo2) Like "*" & [forms]![f_List_Arm]![txtSearchArm].[Text] & "*"))
ORDER BY t_Arms.ArmType, t_Arms.ActionType;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
On a list or combo, there are only two ways to push data in. One is to reset the record source and the other is to use a callback function. A callback function in this case is probably not a good idea because it’s a straight SQL  statement that you have.

The callback function is meant to be used when you have a calculation or something to that effect that you have to do on the fly. It’s usually slower overall to execute.

 With that said, you’re not gonna get great performance on that SQL statement if there are a lot of records with all those like clauses.

Jim
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks Jim. There will be no more than around 1000 records
John TsioumprisSoftware & Systems Engineer
Commented:
You could use the Open event to store the RowSource to a form-scoped string variable ...this would be the default
e.g.
Dim strLstBoxOriginalRowSource as String

Open in new window

On the Open event
Private Sub Form_Open(Cancel As Integer)
strLstBoxOriginalRowSource  = Me.YourListBox.RowSource
End Sub

Open in new window

and on the Load (or some other event...)
Dim lstRowSource as String
lstRowSource =mid(strLstBoxOriginalRowSource ,1,Instr(strLstBoxOriginalRowSource ,"ORDER BY")-1) & "YOUR EXPRESSION HERE e.g. OR ArmPK =111" &mid(strLstBoxOriginalRowSource ,Instr(strLstBoxOriginalRowSource ,"ORDER BY")) 

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Save your current query as, say qArms.

Then, when opening the form, set the RowSource of the listbox:

Me!YourListbox.RowSource = "Select * From qArms Where ArmPK = " & Me!ArmPK.Value & ""

Open in new window

To remove the filter, set the RowSource:

Me!YourListbox.RowSource = "Select * From qArms"

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial