How do I populate a listbox based on the results shown in another (on the same Form)?

[stonward]

Hi Guys,

I feel this should be straightforward , but I'm struggling.
I have a listbox that displays the results of a filter/query - including the PK.

I need to show just those same filtered results in another listbox, where I can display the data in a different format- that is it is displayed with spaces etc between the concatenated values.

Thankyou!

R.

[topher23]

I don't know if there's a "straightforwar d" way to do this, but I would write a code procedure that would:

1. Iterate through the contents of the first text box, while
2. Adding each line, formatted the way you want it, to an unbound second text box, using the .AddItem method.

Although, depending on what you plan to do with the second list box, this may not be the easiest to work with.

[nico5038]

When your filter is build by using a form and the popup filter, the results will be stored in the Me.Filter property of the form.
This can be used to construct the listbox query by adding " WHERE " & Me.filter to the original SELECT.
Would look like:

Me.listbox.rowsource = "select * from tblX WHERE " & Me.Filter
Me.listbox.requiry

Nic;o)

[stonward]

Thankyou!

Thanks for your help - haven't needed any for some time, but this Forum has been a godsend in the past.

Going to try your idea (Nic;o) right now...get back to you...

R.

[stonward]

Hello again.
I'm sorry, but the info I gave you to work on was woefully inadequate and even incorrect - I thought I could work around any differences. But I do have an idea that I'm sure will improve the design, if you can just help me with a bit of SQL (etc).
Okay, I have a listbox that updates its columns once a textbox's contents change (using recordsetclone. findfirst in the listboxes After Update event). The problem is that the results are shown with no spacing (so the user can type in search parameters without worrying about spaces - they'll be diff each time).
I was using a second listbox, but I think a subform might be better, to show the same results, but with spaces put back in. The subform then should show the records of the listbox contents, based upon Coulmn 0, the PK - and update on each After Update of the listbox. I have written out dozens of SQL lines, but I can't seem to get it right - here was my last version placed in the After Update of my listbox ('quickSearch);

<DoCmd.OpenFo rm "frm_subformMai n", , , "[tblproducts.pro ductid]= " & " '" & Me.lstquicksear ch.Column(0) & "'">

Or, can I populate the subform based on a query using the listboxes PK column as criteria?

PS: How do you guys use tags to show code snippits the way you do?! Or even change the font?

R.

[nico5038]

I've solved the same problem by adding a searchfield in the table without the spaces and other sprecial characters using a function like:

Function fncCompact(strOms As String) As String

' Filter for 0-9 A-Z en a-z to pass to the result
' All other characters are "dropped"

Dim intI As Integer

fncCompact = ""

For intI = 1 To Len(strOms)
   If Asc(Mid(strOms, intI, 1)) >= 48 And Asc(Mid(strOms, intI, 1)) <= 57 Or _
      Asc(Mid(strOms, intI, 1)) >= 65 And Asc(Mid(strOms, intI, 1)) <= 90 Or _
      Asc(Mid(strOms, intI, 1)) >= 97 And Asc(Mid(strOms, intI, 1)) <= 122 Then
      fncCompact = fncCompact & Mid(strOms, intI, 1)
   End If
Next

End Function

So the search is performed on this field, but the user will seen the "full" field with spaces.

Getting the idea ?

Nic;o)
(Nb the code is marked by a start tag being a [ and a ] with "code" in between. The end tag holds "/code" between the brackets)

[stonward]

Ha Ha...I recently DELETED a similar field (called 'handle') in my main products table on which I performed my searches, but since it was a value made up of other fields I figured it was bad form and tried to find a better way.
Your method is much more involved than mine however, and I'm gonna dig into it now.
Always a pleasure to see your thoughts Nic;o!

Thanks again,

R

[nico5038]

Nice to know my thoughts are valued :-)

Success with your application !

Nic;o)