send multiple selection listbox to a view or storedprocedure

[bilouk]

hi and thank for all your precious help.
can you explain me how to send the multiple selection of a list box to a storedprocedure. I can do it without problem with access and a module but in ADP project, the stroredprocedure says that I have a ADO problem.
I appreciate all help.
thx

(sorry for the spelling, I am french)

 

[PWise]

bon jour
can you show us the code that you are using with access and we will try to help with ado

 

[bilouk]

hi.
first I have a table (Clients) with :
Numéro (number), Clients (char) as field.

I got a form with a listbox (Liste1) with column 1 bound to the table (Clients) and a command button that open a query on click :

Private Sub Commande0_Click()
If Not PrepareList(Me.Name, Me.Liste1.Name) Then
MsgBox "impossible de lancer la requête"
Else
DoCmd.OpenQuery "requête1"
End If
End Sub

my module contain:

Option Compare Database
Option Explicit

Dim frm As Form
Dim ctl As Control
Dim varItm As Variant

Public Function PrepareList(FormName As String, _
ControlName As String) As Boolean
On Error GoTo Err_PrepareList
Set frm = Forms(FormName)
Set ctl = frm(ControlName)
If ctl.ItemsSelected.Count = 0 Then GoTo Err_PrepareList
PrepareList = True
Exit Function
Err_PrepareList:
PrepareList = False
End Function

Public Function CompareList(ParameterValue As Variant) As Boolean
For Each varItm In ctl.ItemsSelected
If CStr(ParameterValue) = ctl.ItemData(varItm) Then
CompareList = True
Exit Function
End If
Next varItm
CompareList = False
End Function

and my query (requête1)got this code:

SELECT Clients.*
FROM Clients
WHERE CompareList([numéro]) = True;

when I select multiple item item on the list and after I click on yhe button, my selection is vivible on the query.
with access it works great but with ADP project, it says that ADO cannot read the (Comparelist) function that is written in my module. (as there are no query in ADP, I've tried with a view, function or procedure that I've call with:
Private Sub Commande0_Click()
If Not PrepareList(Me.Name, Me.Liste1.Name) Then
MsgBox "impossible de lancer la requête"
Else
DoCmd.OpenView "requête1"
End If
End Sub
for example)

I hope you've got all the informations.
thanx's for your help.