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!
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!