By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,512 Online
Bytes IT Community
+ Ask a Question
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)?

stonward
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

✓ answered by 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:
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)

Share this Question
Share on Google+
7 Replies


topher23
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

nico5038
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
  1. Me.listbox.rowsource = "select * from tblX WHERE " & Me.Filter
  2. Me.listbox.requiry
  3.  
Nic;o)
Feb 18 '10 #3

stonward
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

stonward
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);

<DoCmd.OpenForm "frm_subformMain", , , "[tblproducts.productid]= " & " '" & Me.lstquicksearch.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.
Feb 19 '10 #5

nico5038
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
  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)
Feb 19 '10 #6

stonward
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

nico5038
Expert 2.5K+
P: 3,072
Nice to know my thoughts are valued :-)

Success with your application !

Nic;o)
Feb 21 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.