CODEWITHSUNDEEP
ms-accessvba
user1938742
user1938742

Reputation: 315

Run query with parameters and display in ListBox MS Access 2013

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.

Upvotes: 1

Views: 4401

Answers (2)

user1938742
user1938742

Reputation: 315

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.

Upvotes: 4

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

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()));

Upvotes: 1

Related Questions