Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.
Help with 2 listboxs on 1 userform, and referencing SQL
This is my first post so my apologies if i am repeating anything but i have tried my hardest to find an answer to my question with no luck.
I am writing an excel application that references a SQL database via a script within an excel macro.
On my user form I have 3 optionbuttons and 2 listboxes.
Depending on which optionbutton is chosen depends on what range on the spreadsheet Listbox1 is populated with, the code i have for this is all fine and working.
The user can then select items from ListBox1, press the addcommand button and then these will go into listbox2. (a drill down if you will)
This is where i get stuck!!
I could potentially have just one or multiple lines in listbox2.
I have code as follows:
If OptionButton1.Value = True Then
SQLStr2 = "A.[SORT/LOOKUP] ='" & ListBox2.Value & "' "
strSQL1 = ""
strSQL1 = strSQL1 & "SELECT "
strSQL1 = strSQL1 & "A.[SORT/LOOKUP CODE], "
strSQL1 = strSQL1 & "A.[EXECUTIVE], "
strSQL1 = strSQL1 & "A.[REF NO.], "
strSQL1 = strSQL1 & "B.[OPEN CLIENT], "
strSQL1 = strSQL1 & "B.[PEP/ISA CLIENT], "
strSQL1 = strSQL1 & "A.[INVEST TYPE], "
strSQL1 = strSQL1 & "B.[ISA/PEP INVEST] "
strSQL1 = strSQL1 & "FROM dbo.CLI_CORE A, CLI_CUSTOMER B "
strSQL1 = strSQL1 & "WHERE "
strSQL1 = strSQL1 & "A.[REF NO.] = B.[REF NO.] "
strSQL1 = strSQL1 & "AND "
strSQL1 = strSQL1 & "A.[REF NO.] > 100 "
strSQL1 = strSQL1 & "AND "
strSQL1 = strSQL1 & SQLStr2
Set SheetSelect = Sheets("Results").Range("A" & ActiveCell.row & "")
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;FILEDSN=F:Invest_Bespoke4i_CORE_REPLICA;"), Array("=Yes")), _
.CommandText = strSQL1
.Name = "Query from 4i_core_REPLICA"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
My problem is that i can't get it to pick up the value of ListBox2!! even if it is just a single value it won't work and I have no idea where to start in referencing multiple values with SQL.....
I hope someone can help me as it's driving me nuts :)
0 · ·