Use Multi-Select List boxes as query parameters

[123]

Thank you for your help:
Can any one tell any Idea about using Multi-Select List boxes as query
parameters....
Thank you

 

[John Vinson]

Thank you for your help:
Can any one tell any Idea about using Multi-Select List boxes as query
parameters....
Thank you

There are a few ways, all involving at least some VBA code. For one
simple example see
http://www.mvps.org/access/forms/frm0007.htm

 

[123]

John W. Vinson[MVP]



Thank you for your help and answer... very much

I read the question that you tell me about but I will tell you what I do and
YOU can tell me is this true or false

thank you



1- Put this code in function name it Multiselect

2- Use this function in query Grid in the field that I want to retrieve data
from..

3- The code in my function is :

--------------------()------------------

Function multiselect()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Application.Forms!frmreport
Set ctl = frm!Listselect

strSQL = "Select * from client where [Group]=
strSQL=left$(strSQL,len(strSQL)-12))"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR
[GroupNumber]=strSQL=left$(strSQL,len(strSQL)-12))"
Next varItem
' Trim the end of strSQL
' strSQL=left$(strSQL,len(strSQL)-12))
End Function



-----------------()--------------------

I hope you can tell me what is the wrong

Thank you

 

[John Vinson]

John W. Vinson[MVP]



Thank you for your help and answer... very much

I read the question that you tell me about but I will tell you what I do and
YOU can tell me is this true or false

thank you

I'm sorry... but it's got LOTS of problems.

1- Put this code in function name it Multiselect

Where? in a public Module, on a Form, ...?

2- Use this function in query Grid in the field that I want to retrieve data
from..

No. This will NOT work. The function does not return a parameter; it
returns THE ENTIRE SQL QUERY STRING; that is, it creates a completely
new Query for you. You should then use this query string as the
Recordsource for a Form or Report, or open a Recordset based on the
string.

3- The code in my function is :

--------------------()------------------

Function multiselect()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Application.Forms!frmreport
Set ctl = frm!Listselect

This will work *if* frmreport is open.

strSQL = "Select * from client where [Group]=

This will fail because you have an opening quote and no closing quote;

strSQL=left$(strSQL,len(strSQL)-12))"

this will fail because you have a closing quote and no opening quote.
Even if you were to remove the quote all it would do is take the
string you built above

Select * from client where [Group]=

and remove the rightmost twelve bytes, leaving

Select * from client wh

which isn't going to be good for much of anything!

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR
[GroupNumber]=strSQL=left$(strSQL,len(strSQL)-12))"

and this is such a complete hodgepodge that I can't even tell what you
THOUGHT you were trying to do!

Next varItem
' Trim the end of strSQL
' strSQL=left$(strSQL,len(strSQL)-12))
End Function



-----------------()--------------------

I hope you can tell me what is the wrong

Let's start over. It seems that you want to search the field
GroupNumber for the numeric values in the listbox named Listselect on
the form named frmreport, and then (I presume) launch a Report based
on those values... right?

I'll wait for your reply so I don't spend ten minutes typing an answer
to the wrong question!

 

[123]

John W. Vinson[MVP]
Thank You very much for your help....

You are right Your Suggestion is True..

 

[John Vinson]

John W. Vinson[MVP]
Thank You very much for your help....

You are right Your Suggestion is True..
-------------------------------------------
It seems that you want to search the field
GroupNumber for the numeric values in the listbox named Listselect on
the form named frmreport, and then (I presume) launch a Report based
on those values... right?

I'd suggest then using VBA code to launch the report from a command
button on the form. Let's say you have a command button cmdReport on
frmreport. Its click event could be something like:

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim DocName As String
Dim strSQL As String
Dim varItem As Variant

' See if the user selected anything at all
If Me!Listselect.ItemsSelected.Count = 0 Then
MsgBox "Please select which items you want to print"
GoTo Exit_CmdReport_Click
End If

' Specify which report you want to open
DocName = "YourReportNameHere"

' Loop through the selected items in the Listbox,
' building up a legal SQL WHERE clause

strSQL = "[GroupNumber] IN ("
' The ItemsSelected property of the listbox is a collection of
' all the items the user has selected. Loop through all of them...
For Each varItem In Me!Listselect.ItemsSelected
' If the user selects rows with values 3, 65, 92 this will build a
' text string like
' "[GroupNumber] IN (3, 65, 92, "
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem
' Now trim off the closing comma and blank and put on a closing
' parenthesis
strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
' Open the Report using strSQL as the WhereCondition
' use acViewNormal instead of acViewPreview to print the report
' immediately
DoCmd.OpenReport DocName, acViewPreview, , strSQL

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click
:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

 

[123]

John Vinson

YOU Are Man You are true programmer you are Supper Man all best word for you
= (John Vinson)

Thank you