Problem with multiple selection list box

[dreamsoul620 via AccessMonster.com]

Hi all,

I have two queries with parameters that I would like to run at one time.
These generate reports that are automatically exported into excel with the
click of a button. This works fine. Instead of the user having to type the
parameters for the queries twice, I am using a form to allow them to choose
their specifications. Two parameters are populated using combo boxes. The
third has to be a list box to provide for multiple selections. I found the
code below but cannot get it to work. Perhaps I'm not using it correctly.
I placed my form's name (percent_selection) after Form! and my list box name
(depart) after frm!
I keep getting an error stating that Percent_selection is not a valid field.
Is something other than the form name supposed to go after Form!? Also,
where does this code go so that I can use in as my parameter in both queries?
Any help is greatly appreciated.


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees"
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem

If Len(strWhere) > 0 The
strSQL= strSQL & " where [EmpID] In (" & _
left$(strSQL,len(strSQL)-2)) & ")"
End If

 

[Guest]

Your concatenation line looks like it's using your strSQL string instead of
the strWhere string.

Try replacing
left$(strSQL,len(strSQL)-2)) & ")"

with
left$(strWhere,len(strSQL)-2)) & ")"

You may also need to end the SQL statement with a semi colon if you're using
that for a querydef.

Hope that helps.

Dave

 

[Guest]

Ooops.
That should have been
left$(strWhere,len(strWhere)-2)) & ")"

Cheers
Dave

Your concatenation line looks like it's using your strSQL string instead of
the strWhere string.

Try replacing
left$(strSQL,len(strSQL)-2)) & ")"

with
left$(strWhere,len(strSQL)-2)) & ")"

You may also need to end the SQL statement with a semi colon if you're using
that for a querydef.

Hope that helps.

Dave

Hi all,

I have two queries with parameters that I would like to run at one time.
These generate reports that are automatically exported into excel with the
click of a button. This works fine. Instead of the user having to type the
parameters for the queries twice, I am using a form to allow them to choose
their specifications. Two parameters are populated using combo boxes. The
third has to be a list box to provide for multiple selections. I found the
code below but cannot get it to work. Perhaps I'm not using it correctly.
I placed my form's name (percent_selection) after Form! and my list box name
(depart) after frm!
I keep getting an error stating that Percent_selection is not a valid field.
Is something other than the form name supposed to go after Form!? Also,
where does this code go so that I can use in as my parameter in both queries?
Any help is greatly appreciated.


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees"
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem

If Len(strWhere) > 0 The
strSQL= strSQL & " where [EmpID] In (" & _
left$(strSQL,len(strSQL)-2)) & ")"
End If

 

[dreamsoul620 via AccessMonster.com]

Thanks for the quick reply. That helped fix one error, but I'm still getting
that the field cannot be found error. I have put the code in the OnClick
event of the button that runs the reports/queries. I replaced the Set
frm=Form!frmMyForm with Set frm=Form!Percent_Selection. Percent_Selection is
the name of my form. I replaced the next line with Set ctl=frm!Depart.
Depart is the name of my list box. I also tried using Depart with the first
line and a referenced text box named Stuff in the second line. Any idea
where I'm going wrong?

Ooops.
That should have been
left$(strWhere,len(strWhere)-2)) & ")"

Cheers
Dave

Your concatenation line looks like it's using your strSQL string instead of
the strWhere string.

[quoted text clipped - 44 lines]

 

[dreamsoul620 via AccessMonster.com]

I found some different code to try. It gives me the correct parameter, but
doesn't apply to my query. My selections are numbers defined as text, so I
use quotes around each selection. My hidden text box Stuff is updated with
all the selections. "510", "505","540", etc. I then put the following as the
parameter in my query [FORMS]![PERCENT_SELECTION]![STUFF]

I'm using the following code on the LostFocus event of my list box.

Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.DEPART

For Each varItem In ctl.ItemsSelected
strItems = strItems & Chr(34) & ctl.ItemData(varItem) _
& Chr(34) & ", "
Next varItem

'Trim the end of strSQL
strItems = Left$(strItems, Len(strItems) - 2)
Me.Stuff = strItems

Ooops.
That should have been
left$(strWhere,len(strWhere)-2)) & ")"

Cheers
Dave

Your concatenation line looks like it's using your strSQL string instead of
the strWhere string.

[quoted text clipped - 44 lines]