gunslingor
Registered User.
- Local time
- Today, 10:14
- Joined
- Jan 13, 2009
- Messages
- 50
This is such a cool solution I found that solved problems I've been having for years with listboxes, I had to share:
scenario: You have a form with a bunch of stuff on it, one item is a primary key of the source table. You also have a listbox that is based on values in a different table and the two tables are linked on the primary key value... so, now you want the listbox to filter its displayed values based on primary key shown on the form (i.e. kind of linking the listbox key to the form key, as would be done with a subform typically).
Solution: originally I was handling it all with VB. So I'd set the rowsource of the listbox every time the key changed... but this was very slow and jumpy. So I set out to try to define the rowsource in the properties window instead of dynamically changing it in VB (access always works better when you use these toy controls, don't treat it like a typical SQL DB)... finally solve it thanks to this post http://stackoverflow.com/questions/2...obox-selection. A few important points for those with the same issue:
1. The query builder doesn't recognize Me.anything... you have to use collections like Forms![formname]!key_txt_box in the conditions of the listbox row source. If you use me.anything, the first time the query is run, it changes to a value like the actual key value.
2. everytime the key_txt_box changes, you have to requery the listbox only!
i.e. you can dynamically set the values to be displayed in a listbox by using the query builder and collections, but the listbox must be requeried on every change.
With this method employeed, the flashing and slowness is gone, everything is pretty as hell and works perfectly... this method makes the listbox behavior more like a subform than a typical crappy access listbox (expert users should understand what I'm talking about here).
I can't describe just how much better this method is than doing it with VB, this is ideal as hell!
scenario: You have a form with a bunch of stuff on it, one item is a primary key of the source table. You also have a listbox that is based on values in a different table and the two tables are linked on the primary key value... so, now you want the listbox to filter its displayed values based on primary key shown on the form (i.e. kind of linking the listbox key to the form key, as would be done with a subform typically).
Solution: originally I was handling it all with VB. So I'd set the rowsource of the listbox every time the key changed... but this was very slow and jumpy. So I set out to try to define the rowsource in the properties window instead of dynamically changing it in VB (access always works better when you use these toy controls, don't treat it like a typical SQL DB)... finally solve it thanks to this post http://stackoverflow.com/questions/2...obox-selection. A few important points for those with the same issue:
1. The query builder doesn't recognize Me.anything... you have to use collections like Forms![formname]!key_txt_box in the conditions of the listbox row source. If you use me.anything, the first time the query is run, it changes to a value like the actual key value.
2. everytime the key_txt_box changes, you have to requery the listbox only!
i.e. you can dynamically set the values to be displayed in a listbox by using the query builder and collections, but the listbox must be requeried on every change.
With this method employeed, the flashing and slowness is gone, everything is pretty as hell and works perfectly... this method makes the listbox behavior more like a subform than a typical crappy access listbox (expert users should understand what I'm talking about here).
I can't describe just how much better this method is than doing it with VB, this is ideal as hell!
