> Grid-rows Not Displaying Selected-state Correctly After Many VBA Requeries (1 Viewer)

[johnywhy]

ListBox-rows Not Displaying Selected-state Correctly After Many VBA Requeries

Access 2010 Desktop

this is very weird (and urgent).

when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.

oListBox.Selected(lngRow) = True

However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).

Print oListBox.Selected(lngRow)
->True

video:
Access ListBox Control Under VBA Control Behaving Wacko - YouTube

It seems that the more times i requery the listbox, the worse the problem gets.

oListBox.Requery

This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse.

Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.

i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms).

not sure if relevant, but this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.

help!

posted here:

http://p2p.wrox.com/access-vba/9232...state-correctly-after-many-vba-requeries.html
http://www.access-programmers.co.uk/forums/showthread.php?p=1335219
http://www.techsupportforum.com/for...orrectly-after-many-vba-requeries-804257.html
http://www.utteraccess.com/forum/Grid-rows-Displaying-Sel-t2015549.html
http://www.vbaexpress.com/forum/sho...-Selected-State-Correctly&p=305855#post305855

 

[johnywhy]

here's a stripped-down access db, containing only the objects and code necessary to duplicate the issue.

https://drive.google.com/folderview?id=0B8FhwalyGcnvNWVMTWdvd1FaMEk

let me know if you cannot open this.

i think it might be a problem with my display driver.

 

[JHB]

Maybe you can use this instead, database attached.

 

[johnywhy]

hi

i think what you're doing is:

-- moved some of the code into the form, instead of in a module.

-- you're no longer selecting all items in the Units list on each click of the Warehouse click

-- you took the query source out of the Units list, and instead you're updating the Units list with hardcoded SQL in VBA.

-- your Select loop is apparently skipping every other row, i think. If so, Why? this seems weird.

-- you're using Split to convert the listbox selected items into a comma-string (great!)


Is this correct? What am i missing? Which of these changes is the KEY to your solution to my original problem? I'm thinking the key might be to NOT do the select-all-units loop on every click of Warehouse-- that repetition might be the cause of the memory or repaint bug.


Some problems:

-- i need each click of Warehouse list to select all items in Units list after requery, part of the spec.

-- i'm seeing quick updates of Units on each Warehouse click, UNLESS i click 'Select All' in Units form, and then go back to clicking around in the Warehouse list-- then updates of Units list are very slow.

-- i prefer common form procs, like "Select-All", to be in a shared module instead of in a form (other forms in my application also need the select-all proc.

-- i prefer no hardcoded SQL in VBA.


Improvements:

-- Is it possible for you to delete MY procs and just use yours, so i can understand better what's happening?

-- Are those new query objects essential to your solution, or just some temporary queries you made for testing? Can they be removed?

-- Can you prevent the problems i mention above, like eliminate the delay, and select-all-units on each Warehouse click?

-- Can you keep the sql query in the listbox properties, instead of in VBA?

-- Can you delete all unnecessary code and objects, so i can understand better what's essential to your solution?


If you don't have a chance, no worries, i will try.
many thanks for your efforts!

 

[johnywhy]

i think i solved this.

Problem: when vba selects all rows in a listbox every time the listbox is requeried, selection behavior gets wonky: selected rows do not appear selected.

the fix is to UNselect all rows in the target Units table before requerying it.

Produced wonky selection behavior:
1. Where condition changes
2. Requery
3. Select All Rows

Fixed wonky selection behavior:
1. Where condition changes
2. UNselect All Rows
3. Requery
4. Select All Rows

see debug-fix
https://drive.google.com/file/d/0B8FhwalyGcnvT3FucTZ0Qm15ekE

i think this might be considered a Microsoft Access Bug, because the behavior (selected rows appearing unselected) is abnormal.

i'll mark this thread solved, after anybody confirms my fix works.

thx!

 

[JHB]

hi

i think what you're doing is:
...
..
.

I see you've got your solution to work, so I don't think there is any need for explanation.

 

[essaytee]

i think i solved this.

Problem: when vba selects all rows in a listbox every time the listbox is requeried, selection behavior gets wonky: selected rows do not appear selected.

the fix is to UNselect all rows in the target Units table before requerying it.

Produced wonky selection behavior:
1. Where condition changes
2. Requery
3. Select All Rows

Fixed wonky selection behavior:
1. Where condition changes
2. UNselect All Rows
3. Requery
4. Select All Rows

see debug-fix
https://drive.google.com/file/d/0B8FhwalyGcnvT3FucTZ0Qm15ekE

i think this might be considered a Microsoft Access Bug, because the behavior (selected rows appearing unselected) is abnormal.

i'll mark this thread solved, after anybody confirms my fix works.

thx!

I had the opposite effect (today). Using a multi-select listbox - extended mode - selecting a number of items, then using VBA to delete those items, at times, not all the time, one item would appear to be selected (highlighted) from the remaining items. This item not being a part of the items so deleted, seemed to be random. When I detected this I thought it would be a simple matter to run another procedure simply to clear out all selected items and then hours and hours later I was still working on the problem. As it turned out it wasn't being detected as selected and I couldn't figure out why.

Thank you, your solution fixed things.

Steve.