Multiselect Listbox

[Hayley Baxter]

Hi All

Wondering if any of you kind people can help me out with this. I have a multiselect listbox of companies so I need to be able to choose the selection(s) to then email or print etc. I have a textbox on the form as well which puts in the selection(s) made by the user. This part seems to be working ok.

However I do have a problem with the code in that it keeps throwing up error 3075 "Syntax error(missing operator) in query expression. If I could resolve this part of the coding all should work well. Anyone any ideas?

here is the code:

Private Sub Search_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!LstBoxCompany.ItemsSelected
stWhat = stWhat & Me!LstBoxCompany.ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!txtCriteria = CStr(left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qryMultiSelect")
stSQL = "SELECT CompanyID, Company Name "
stSQL = stSQL & "Company Name FROM qrycompdet WHERE CompanyID"
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenQuery "qryMultiSelect"

Any help with this greatly appreciated.
Many thanks
Hayley

 

[Drevlin]

This is what your sequel statement says at the moment:

SELECT CompanyID, Company Name Company Name FROM qrycompdet WHERE CompanyID...

The bold part is where you're having a problem.
I'm assuming you only want one Company Name and it should be in brackets.

stSQL = "SELECT CompanyID, [Company Name] "
stSQL = stSQL & "FROM qrycompdet WHERE CompanyID"
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"


Peace

 

[Hayley Baxter]

Hi Drevlin

Thank you so much it works very well now. You're a genius!!

Cheers
Hay