Run query with parameters and display in ListBox MS Access 2013

Question

I've created a query in Access 2013 that takes two parameters

PARAMETERS blah TYPE, blah TYPE;
SELECT * FROM blah WHERE blah blah;

I want to run that query and display the result in a listbox.

Normally I would do like

Me.MyListBox.RowSource = "myQuery"

But when I do so, a box is popping up telling me to enter the first parameter. How can I specify the parameters programatically?

My second approach was something like

With CurrentDb.QueryDefs("myQuery")
    .Parameters("param1") = 1
    .Parameters("param2") = 2
    Me.MyListBox.RowSource = .OpenRecordset()
End With

That gave me type mismatch?

How can I do this?

EDIT: To make things clear, I know that I can concatenate strings to build the query I want, something like:

Me.MyListBox.RowSource = "SELECT * FROM table WHERE abc Like '" & somevalue & "'"

But this is precisely what I want to avoid because it makes the code difficult to maintain and read.

I think you may need to either rework the query and/or add some vba code. One option is to modify the query so the parameters are replaced by Functions (then you need to specify where the values are); or just add VBA code to create a 'run-time' view of the SQL (i.e. parameter values embedded) and set the rowsource. Someone else may have a better solution. — Wayne G. Dunn
– Wayne G. Dunn, Commented Oct 17, 2014 at 15:01
Thanks for your answer. In MySQL you can create a stored procedure that takes a couple of parameters, and you can then run the query using the CALL statement. I though it might be possible to achieve something similar in Access, but I haven't stumbled upon anything yet... — user1938742
– user1938742, Commented Oct 17, 2014 at 16:14
Yes, the issue is your rowsource is bound to a saved query that prompts for parameters. Is each user supposed to type in the values for the parameters, or are the values available somewhere as a control on a form or in a table? If so, you can change the query to grab the 'current' values. — Wayne G. Dunn
– Wayne G. Dunn, Commented Oct 17, 2014 at 16:41
The values are the selections from a ListBox and a ComboBox in a form. How can I instruct the query to grab the values from these components? — user1938742
– user1938742, Commented Oct 17, 2014 at 18:12

user1938742 · Accepted Answer · 2014-10-17 22:23:28Z

4

OK, I think I've found what I was looking for.

Private Sub SomeButton_Click()
    With CurrentDb.QueryDefs("myQuery")
        .Parameters("firstParameter") = 2
        Set Me.MyListBox.Recordset = .OpenRecordset
        .Close
    End With
End Sub

If you want to run an INSERT/UPDATE/DELETE instead, I think you can do the same thing, but instead of using OpenRecordset, call Execute.

edited Oct 17, 2014 at 22:23

answered Oct 17, 2014 at 22:16

user1938742

3251 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wayne G. Dunn · Accepted Answer · 2014-10-17 20:48:39Z

1

Depending on if the filter criteria in your listbox is MultiSelect or a single entry, the following will work. You will need to requery your ListBox after making your selections.

Public Function sListBox() As String
    If Not IsNull(Forms![frmListbox]![lstDate]) Then
        sListBox = Forms![frmListbox]![lstDate]
    End If
End Function

Your Query can look like (reference ComboBox directly and Function for ListBox:

SELECT CUSTOMER.ProductNumber, CUSTOMER.LastOrder
FROM CUSTOMER
WHERE (((CUSTOMER.ProductNumber)=Forms!frmListbox!cboCustomer) 
And ((CUSTOMER.LastOrder)=sListBox()));

answered Oct 17, 2014 at 20:48

Wayne G. Dunn

4,3322 gold badges15 silver badges25 bronze badges

Collectives™ on Stack Overflow

Run query with parameters and display in ListBox MS Access 2013

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related