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


Joined
Nov 5, 2019
Messages
1
Reaction score
0
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
 
Ad

Advertisements

Ad

Advertisements

Joined
Feb 21, 2018
Messages
209
Reaction score
85
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top