Run select query from VBA code where criteria comes from a multi select ListBox

[MartinS]

Hey Guys,

I'm pretty new to VBA and have hit a snag where I don't know how to go forward. Your assistance/coaching will be greatly appreciated. I have a modal form with member data populated on the form load procedure on it. Then on a listBox that has "Multi Select" enabled, the user will make a selection of all the "codes" he wants to report on.

In a button "on-click" procedure I have the following code that runs through the listBox selections:

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstBulkExercise.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Exercise"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstBulkExercise
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

What I need assistance with:
I need to run a select query in the code WHERE the member number as the primary key AND "strWhere" values are run agains the table, and then have that displayed on a report. The problem is I don't know how to accomplish this and really want to find out how to get this done.

Your assistance is greatly appreciated.

Thanks

 

[msbad1959]

SELECT * FROM tblName WHERE PKID = (pk you select) and fldname in (strwhere)

 

[AmjiBhai]

Dim SqlStr as String
SqlStr="SELECT * FROM tblName WHERE fldname in (" & strwhere & ")"

Dim dbcon As New ADODB.Connection
Dim rst As New ADODB.Recordset
constr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ReportServer$SQLEXPRESSMASTER;Data Source=SERVER\SQLEXPRESSMASTER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SERVER;Use Encryption for Data=False;Tag with column collation when possible=False"


Set dbcon = New ADODB.Connection

dbcon.Open constr



'Now lets open the recordset



Set rst = New ADODB.Recordset

rst.Open SqlStr, constr, adOpenKeyset, adLockOptimistic