Only show records in listbox when column =

[james_IT]

Hi all

I want to show/hide records in a listbox depending on which command button is pressed. I have 5 command buttons and column 3 in my list box refers to each of these command buttons (1-5).

How do i display the records in the listbox that match that ID/command button? I was think of:

Private Sub CommandButtonNo_Click()
Me.OrderBy = "FieldName"
Me.OrderByOn = True
End Sub

But i dont want to order that field I want to display matching records in that field.


Thanks

 

[LPurvis]

By what criteria would you want to limit the rows returned?
Does column 3 (what is it called?) contain a numeric value (1 - 5) which you'd use the command buttons to filter? (Numerically between 1 to 5).

Does the listbox load with all rows displayed initially - and the command buttons filter this list down? Or would you want one to be the default assumption upon form load?

In Access 2002 onwards - filtering down can look like

Dim rst As Object
 
With Me.ListBoxName
    Set rst = .Recordset
    rst.Filter = "Field3Name = " & intCmdNo
    Set .Recordset = rst.OpenRecordset
    rst.Close
End With

Or if you want to load a specific set from the outset you might be as well off to just specify the source
Me.ListBoxName.Rowsource = "SELECT Field1, Field2, Field3 FROM TableName WHERE Field3 = " & intCmdNo

Where, in each case, intCmdNo is the numeric value determined by the command button clicked.

 

[james_IT]

Column3 has values 1-5 (numeric, yes).

When cmdbtn1 is pressed I want the listbox to display all records where column3 = 1.
When cmdbtn2 is pressed I want the listbox to display all records where column3 = 2.

and so on. Also, I would like the listbox to default to Column 3 = 1 when the form is opened.

Thanks for your help

 

[LPurvis]

Yeah you're perhaps just as well to keep this simple.
Create a function like the following in your form's module.

Function fLimitList(intVal as Integer)
Me.YourListboxName.Rowsource = "SELECT Field1, Field2, Field3 FROM TableName WHERE Field3 = " & intVal
End Function

In the Click event property of each command button enter
=fLimitList(1)
where 1 is the value to enter for command button 1.
=fLimitList(2)
for command 2 - and so on.

If you prefer you can use the function call from each command button's event procedure... e.g.

Private Sub CommandButton1_Click()
    fLimitList 1
End Sub

and so on.

Or set up nothing manually - and place something like this as your form's open event procedure.

Private Sub Form_Open(Cancel As Integer)
    Dim intI as Integer
 
    For intI = 1 to 5
        Me("CommandButton" & intI).OnClick = "=fLimitList(" & intI & ")"
    Next
 
End Sub

to set up the procedure calls for you.

If that's not very clear just post back.

 

[james_IT]

im trying the call function but it doesnt seems to be working. When i click the btns it asks me to enter a parameter value for all the fields, 1-5.

Trying:
Private Sub CommandButton1_Click()
fLimitList 1
End Sub

with:
Function fLimitList(intVal as Integer)
Me.List18.Rowsource = "SELECT Field1, Field2, Field3 FROM TableName WHERE Field3 = " & intVal
End Function

in the forms code

??

 

[LPurvis]

OK - you'll need to have your own SQL statement appropriate to your table as the rowsource.
What is the Rowsource of the listbox currently?
(If it's just a query name then view and copy the SQL definition of that query).

 

[james_IT]

OK - you'll need to have your own SQL statement appropriate to your table as the rowsource.
What is the Rowsource of the listbox currently?
(If it's just a query name then view and copy the SQL definition of that query).

Hi Again

I dont follow. My listbox (List18) is based on rowsource "qryProductsList" (obviously a query).

In that queries SQL view this is what i ahve:
SELECT tblProducts.ProductID, tblProducts.ProductTitle, tblProducts.CategoryID, Format([UnitPrice],"Currency") AS Expr1
FROM tblProducts;

 

[LPurvis]

I'm going to guess wildly that the field in question is CategoryID?
Your statement would then end up akin to

Me.List18.Rowsource = "SELECT ProductID, ProductTitle, CategoryID, Format([UnitPrice],'Currency') FROM tblProducts WHERE CategoryID = " & intVal

 

[james_IT]

I'm going to guess wildly that the field in question is CategoryID?
Your statement would then end up akin to

Me.List18.Rowsource = "SELECT ProductID, ProductTitle, CategoryID, Format([UnitPrice],'Currency') FROM tblProducts WHERE CategoryID = " & intVal

Yes, CategoryID is the column In question.

If i try to enter that into my queries SQL it says its an invalid SQL statement?? Am i putting it in the right place?

 

[LPurvis]

Ah, no that was a replacement line for the function in your form's module.

 

[james_IT]

Ah, no that was a replacement line for the function in your form's module.

oops, Perfect!

But how can I make it default to CategoryID = 1 when i open the form?

 

[LPurvis]

Either by calling
fLimitList 1
in your form's load event - or by just giving the listbox a saved rowsource which selects only the CategoryID 1 values by default.

(The fact that you change the listbox's rowsource at runtime doesn't alter the saved property - which will always remain whatever you assign it to be in design view).

 

[james_IT]

Simple.

Thank you so much for all your help

 

[LPurvis]

Welcome.