Passing MultiList box values to a query

[abenitez77]

I have a form which has a multilist box and i select more than 1 items on the list then click on a button and it saves the items to a text box. Then a query is ran. The query has the text box reference in the criteria. It ends up copying 0 records. I copied and pasted the values from the text box onto the query and ran it and it worked. What am i doing wrong?

Private Sub TestMultiSelect_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer

iCount = 0

If Me!ItemNo.ItemsSelected.Count <> 0 Then
For Each oItem In Me!ItemNo.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Chr(34) & Format(Me!ItemNo.ItemData(oItem), "000000000") & Chr(34)
'sTemp = sTemp & Format(Me!ItemNo.ItemData(oItem), "000000000")
'sTemp = sTemp & Me!ItemNo.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & " or " & Chr(34) & Format(Me!ItemNo.ItemData(oItem), "000000000") & Chr(34)
'sTemp = sTemp & " or " & Format(Me!ItemNo.ItemData(oItem), "000000000")
'sTemp = sTemp & "," & Me!ItemNo.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!MySelections.Value = sTemp
' MsgBox (Forms!SelectItemNumbers.MySelections)
DoCmd.OpenQuery "QryLoadDeclineDtlForm", acViewNormal, acEdit

End Sub

 

[pbaldy]

If this is for opening a form or report, I'd use this:

http://www.baldyweb.com/multiselect.htm

 

[abenitez77]

Thanks, this is good for a report, but i'm trying to pass the values to a query and it doesn't work when i do it to a query.

 

[pbaldy]

I don't know of a non-code way to do that, sorry.

 

[boblarson]

First of all, go download this from MVP Armen Stein's website.
Then import basSQLTools from his sample to your database.

You can modify your code like this:

Private Sub TestMultiSelect_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer
[COLOR="Red"]Dim db As DAO.Database
Dim qdf As DAO.QueryDef[/COLOR]

iCount = 0

If Me!ItemNo.ItemsSelected.Count <> 0 Then
For Each oItem In Me!ItemNo.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Chr(34) & Format(Me!ItemNo.ItemData(oItem), "000000000") & Chr(34)
'sTemp = sTemp & Format(Me!ItemNo.ItemData(oItem), "000000000")
'sTemp = sTemp & Me!ItemNo.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & " or " & Chr(34) & Format(Me!ItemNo.ItemData(oItem), "000000000") & Chr(34)
'sTemp = sTemp & " or " & Format(Me!ItemNo.ItemData(oItem), "000000000")
'sTemp = sTemp & "," & Me!ItemNo.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!MySelections.Value = sTemp
[COLOR="RoyalBlue"]sTemp = [/COLOR][COLOR="red"][B]"WHERE YOURFIELDNAMHERE In(" & sTemp & ")"[/B][/COLOR]
[COLOR="Red"][B]Set db = CurrentDb
Set qdf = db.QueryDefs("QryLoadDeclineDtlForm")
qdf.SQL = ReplaceWhereClause(qdf.SQL, sTemp)
qdf.Close[/B][/COLOR]
' MsgBox (Forms!SelectItemNumbers.MySelections)
DoCmd.OpenQuery "QryLoadDeclineDtlForm", acViewNormal, acEdit

End Sub

 

[abenitez77]

What do I set the variables (db, qdf) as, before i use them?

 

[abenitez77]

Sorry, I didn't scroll up...got it now...

 

[abenitez77]

I got an error msg for this line:
Dim db As DAO.Database

Error msg:
"user defined type not defined"

 

[boblarson]

you have a missing reference to the DAO library. Go into the VBA screen, to the top menu and select TOOLS > REFERENCES and then scroll down the list until you find
Microsoft DAO 3.x Library (where .x is either .51 or .6).

If you are using Access 2007 you would need to use a different one.

 

[abenitez77]

Now I get a compiled error:
"Sub or function not defined"

for line:
ReplaceWhereClause(qdf.SQL, sTemp)

 

[boblarson]

Now I get a compiled error:
"Sub or function not defined"

for line:
ReplaceWhereClause(qdf.SQL, sTemp)

Did you follow this step:

Then import basSQLTools from his sample to your database.

 

[abenitez77]

Now it's installed, but now i get a runtime error ('3464')
Data type mismatch in criteria expression.

 

[abenitez77]

runtime error happens on this line:
DoCmd.OpenQuery "QryLoadDeclineDtlForm", acViewNormal, acEdit

 

[boblarson]

Within this code:

sTemp = "WHERE YOURFIELDNAMHERE In(" & sTemp & ")"
Set db = CurrentDb
Set qdf = db.QueryDefs("QryLoadDeclineDtlForm")
qdf.SQL = ReplaceWhereClause(qdf.SQL, sTemp)
qdf.Close

ADD this line to see what the final sTemp looks like:

Debug.Print sTemp

Put a breakpoint at the line just after that one and then copy and paste the SQL output from your IMMEDIATE WINDOW in the VBA window to the forum here so we can see what was built by your code.

 

[abenitez77]

Here you go:

"WHERE dbo_tbl_decline_dtl.vnd_nbr In("000029978" or "000082393" or "000096370")"

 

[boblarson]

Here you go:

"WHERE dbo_tbl_decline_dtl.vnd_nbr In("000029978" or "000082393" or "000096370")"

Okay, that helps. We need to change the OR parts to a comma and use single quotes. So, you need to modify the part in your code which adds the OR and instead put a comma and ALSO changes to use SINGLE quotes around the numbers so it comes out reading:

"WHERE dbo_tbl_decline_dtl.vnd_nbr In('000029978' , '000082393' , '000096370')"

 

[abenitez77]

That worked!

Thanks so much for your help.

 

[boblarson]

Glad we could help you out.