Solved Query not accepting Form Value (multi Select List) (1 Viewer)

Hi Everyone,
Wondering if anyone has any ideas on a couple of issues I'm having. First, I'm using Access 2000 so some of my functionality is not where the newer versions are at.

First, I created a simple Form using a combo box that asks a user to select a machine. The MachineID (autonumber) gets passed from the form to a query. After declaring the parameters in the query I was able to get this to work:

PARAMETERS [forms]![frmMachineProductTotals]![MachineID] Short;

Now I want to expand on this and be able to select multiple machines. I've been reading for days through posts and I understand that I need to use a List box, not combo box, to do this.

So I created a new Form and I copied the original query for testing. I've been testing the form and it's giving me the correct machine ID's. But I'm stumped on getting the data from the form to the test query. No matter what I do I get different errors. I've tried converting the string to a number by testing just one machine and that doesn't work either.

So I'm guessing I have to somehow convert the string into something that the query can recognize since MachineID is not a string. Then I'm thinking I'll have to declare the proper Parameter in the query. I'm just not sure how to do these things and move forward. Here's the code I have in the new form (lots of great posts that helped me get this far, copied it, works great).

Private Sub BtnRunReport_Click()

Dim ctl as Control
Dim strList as String
Dim varSelected as Variant

Set ctl = Me.MachineListBox

If ctl.ItemsSelected.Count = 0 Then
MsgBox "You have not selected a machine or machines."
Else
For Each varSeleted In ctl.ItemsSelected
strList = strList & ctl.Column(0,varSelected) & " Or "
Next varSeleted
strList = Left$(strList, Len(strList) - 4)
MsgBox "You selected these machine ID's: " & vbCrLf & strList
End If

***AND HERE'S WHERE I'M STUMPED****

DoCmd.OpenQuery "qProducts_MachineCheck_TEST", acViewNormal

End Sub

I would appreciate any insight how to move forward. Thank you in advance!

Hi. Not sure if this will help but take a look at this article. Good luck.

That requires users to respond to a parameter prompt?
It seems to me that if you could pass your string to the query as you are doing, it would work - but you cannot. You can if you used a function to return your value list to a query field, or you can build the whole sql statement in code and run/execute it. Not sure why you want to open what I suspect is a select query anyway. It's like allowing users to interact with tables, which is ill advised.

There are also more complicated methods, such as defining a query definition and passing the entire coded sql statement to it, then running/executing that query def. Maybe you would need to decide on the approach that is most appeaing to you if the link isn't the answer.

Don't use parameters in query. Don't open query. Apply filter to form or report. Review http://allenbrowne.com/ser-50.html

Thanks for the reply's.

At this point I'm only opening the query to test the functionality and make sure it works. When I know it works then I'll take the time to create the reports that the end-user will see. Right now I'm in the testing phase.

When I do the single machine using a combo box the query runs perfect (as do the reports). So when I decided to take on multiple machines I knew I had to try a different method as I cannot select multiple items in the combo box.

I know the correct information is getting pulled from the List box it's getting the query to accept it that is causing me issues. I'll read the links suggested and see if it helps me move forward. Thanks.

Can you try something like this:

Have a look at this link to setup your query:
https://stackoverflow.com/questions/49857041/access-vba-change-query-criteria-via-vba

Cheers,
Vlad

create a Query (same as qProducts_MachineCheck_TEST) but without parameter and without criteria.
save it to any name.
copy and paste it with Prefix "zz".
on your code:

Thanks Bastanu and Arnelgp. I am looking into both solutions. I appreciate your assistance.
I don't know if it's because I'm using Access 2000, but DAO.QueryDef and QueryDef is not coming up as a valid in my VBA code during compile. I'm going to see if I can figure this part out.