Help with 2 listboxs on 1 userform, and referencing SQL - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

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

RoxanneRoxanne Posts: 1Member
Hello,

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 & "' "
GoTo SORTREFSQLQuery

SORTREFSQLQuery:

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")), _
destination:=SheetSelect)
.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
.Refresh BackgroundQuery:=False
End With

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 :)

Many thanks!!!!

Roxanne


Comments

  • dokken2dokken2 Posts: 532Member
    : 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 :)
    :
    : Many thanks!!!!
    :
    : Roxanne
    :
    :
    :
    there are two issues,

    if the listbox Multiselect property is set to 0-fmmultiselectSINGLE then you can determine the selected value with (ONLY one item selected)-

    MsgBox Me.ListBox1.Text & vbCrLf & _
    Me.ListBox1.Value & vbCrLf & _
    Me.ListBox1.ListIndex

    listbox1.value (or text) will return the text value, listindex will return the item-number in the list [ie- the 1st item = 0, 2nd = 1, ...]


    if the listbox Multiselect property is set to 2-fmmultiselectEXTENDED then you need to iterate over the list items with [MULTIPLE items can be selected] -

    Dim i As Integer
    For i = 0 To Me.ListBox2.ListCount - 1
    MsgBox Me.ListBox2.Selected(i), , Me.ListBox2.List(i)
    Next

    in the case of a multi-select, you could run a single sql statement for each case where- Selected(i) = True
    [that is, if list items 1, 2, 3 are selected, you need to do sql for each one those items) [performance-wise, you may want to batch the multiple sql's into a single transaction, and if one fails you can rollback the others]
Sign In or Register to comment.