435,404 Members | 2,512 Online
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

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

 100+ P: 145 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. Feb 17 '10 #1

I've solved the same problem by adding a searchfield in the table without the spaces and other sprecial characters using a function like:
Expand|Select|Wrap|Line Numbers
1. Function fncCompact(strOms As String) As String
2.
3. ' Filter for 0-9 A-Z en a-z to pass to the result
4. ' All other characters are "dropped"
5.
6. Dim intI As Integer
7.
8. fncCompact = ""
9.
10. For intI = 1 To Len(strOms)
11.    If Asc(Mid(strOms, intI, 1)) >= 48 And Asc(Mid(strOms, intI, 1)) <= 57 Or _
12.       Asc(Mid(strOms, intI, 1)) >= 65 And Asc(Mid(strOms, intI, 1)) <= 90 Or _
13.       Asc(Mid(strOms, intI, 1)) >= 97 And Asc(Mid(strOms, intI, 1)) <= 122 Then
14.       fncCompact = fncCompact & Mid(strOms, intI, 1)
15.    End If
16. Next
17.
18. End Function
19.
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)

7 Replies

 Expert 100+ P: 234 I don't know if there's a "straightforward" 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. Feb 17 '10 #2

 Expert 2.5K+ P: 3,072 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: Expand|Select|Wrap|Line Numbers Me.listbox.rowsource = "select * from tblX WHERE " & Me.Filter Me.listbox.requiry   Nic;o) Feb 18 '10 #3

 100+ P: 145 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. Feb 19 '10 #4

 100+ P: 145 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); 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. Feb 19 '10 #5

 Expert 2.5K+ P: 3,072 I've solved the same problem by adding a searchfield in the table without the spaces and other sprecial characters using a function like: Expand|Select|Wrap|Line Numbers 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) Feb 19 '10 #6

 100+ P: 145 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 Feb 20 '10 #7

 Expert 2.5K+ P: 3,072 Nice to know my thoughts are valued :-) Success with your application ! Nic;o) Feb 21 '10 #8