Translate Selection in strSQL into ListBox Selection 1

[Vie]

Hello all,

I am trying to write a piece of code to take a strSQL statement (created in code on the fly and saved in a global variable -- i.e., not a saved query) that will translate its selection into a selection in a multi-select-enabled listbox. For instance if the strSQL selects TenantID's (1, 9, 34, 52, 190, 315, etc.), when I open the form that contains the listbox, I want the listbox to show each of the records with those TenantID's already selected.

I understand that you can do something like:

For x = 0 To Me.lstbox.ListCount - 1
        If Me.lstbox.Column(1, x) > intTenantID And Me
           .lstbox.Column(1, x)  < intTenantID Then
           Me.lstbox.Selected(x) = True
        End If
    Next x

Of course this doesn't work because the TenantID's selected in the strSQL aren't actually variables intTenant. Do I need an array for this? How can this be done?

As always, thanks!
Vie

 

[PHV]

You can store the TenantID's in an array, say arrTID, and then try something like this:

For x = 0 To Me.lstbox.ListCount - 1
  For i = LBound(arrTID) To UBound(arrTID)
    If Me.lstbox.Column(1, x) = arrTID(i) Then
      Me.lstbox.Selected(x) = True
      Exit For
    End If
  Next i
Next x

When building your array you may have to consider the ReDim Preserve instruction.
Another way is to build a string like ":1::9::34::52::190:: 315" and to play with the InStr function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Vie]

Thanks PH,

Yes, that does help very much. I still have some questions though.

I'm looking in my trusty handbook on this - so I type something like:

Dim arrTID() As Long
ReDim Preserve arrTID(9, 9, 9)
Redim arrTID(9, 9, 9)

This is a "multi-dimensional" array. Do I need multi-dimensions? It's true that it won't be known in advance how many TenantID's are selected in strSQL - could be 1 could be 1000 - so I can't really specify how many elements the array needs to accomodate in advance.

Also, how do I put the TenantID's from strSQL into the array? A function?

I've never used arrays before so I'll need a little leg up on this.

Thanks so much for your help!

Vie

 

[PHV]

Can you post some examples of your strSQL ?
I guess it's something like this :
"SELECT someFields FROM myTable WHERE TenantID IN (1, 9, 34, 52, 190, 315, etc.)"
If so, try something like this:

strIN = "TenantID IN ("
i = InStr(1, strSQL, strIN, vbTextCompare) + Len(strIN)
j = InStr(i, strsql, ")", vbTextCompare)
arrTID = Split(Mid(strsql, i, j - i), ",")

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Vie]

PH, thanks for being so quick!

Unfortunately, the where clause isn't selecting records based on TenantID but TenantID is the first field in the selection. The where clause varies -- it's built in code using a select case block based on the selection in option group on a search form. The where clause often has multiple criteria.

It is something like:

Select qry1.TenantID, qry1.ApplicationDate, qry2.Eligible, qry2.IssuedDate....etc.
FROM qry2 JOIN qry1...etc.
WHERE qry1.ApplicationDate is not null And qry2.Eligible = "Yes"

The reason why I want to take the selection from the search form and translate it to a listbox is so that the user can add or remove tenants (multi-select) who may not be included in the strSQL recordset because they don't meet the criteria. Oddballs, really (there's always a few of those).

So there really is no "In (...)" to utilize. Also, the listbox itself doesn't contain every field selected by strSQL but it does ALWAYS include TenantID at the very least.

Maybe there's no way to do this?

After a marathon weekend of wall-to-wall workaholism, I don't know if my brain can process up from down. But thanks again. If you have any ideas, I welcome them!

Vie

 

[Vie]

PH, or anyone else out there, wonder what you think of the following?:


I'm thinking that if I save the strSQL in a temporary recordset object...

Dim rs As ADOB.recordset
Set rs = strSQL

arrTID = recordset.GetRows(Rows, Start, TenantID...)

Does the GetRows method work this way?

I'm fishing around in every book in my arsenal for a clue on this and I'm not sure I'm making sense of any of it.

Vie

 

[PHV]

Thanks for the star.
BTW you don't need multi-dimensional array:
ReDim arrTID(NumberOfRowsReturnedBySql)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884