new_wes
asked on
Unselect Listbox value after requery err
Hi all - stumped good again. Single form bound to table with combox cmb_county and listbox lst_services at top to filter records.
cmb_county is unbound top-level filter that after update requeries form filtered on county selection, and also requeries lst_services to filter for that county.
lst_services is unbound listbox based on query filtered by cmb_county value. After lst_services update, form is requeried to filter based on both county and service selected. Records are in form detail, and everything works fine (choose county, all records for that county and services reload to just services for that county; choose service, only records for that service in that county show).
The only problem is that after a user has selected both a county and a service, and then chooses a different county, the services selection stays on their original selection, even though the county resets and the form shows all records for that county. So i'm trying to add code to 'unselect' the service, and it keeps crashing access (says it is generating err log but can't find).
tried changing lst_services on_update event to on_click, but still crashes when: pick county (form and lst_services update fine), pick service (form updates fine), pick new county (form updated fine but services still show old choice), pick new service (access hesitates and then crashes, doesn't even max cpu). cmb_county code to update is below.
rststring = "Select * from tSvcProviderDetail where [tCounty_ID] = " & Me.cmb_County
Debug.Print "cnty=" & rststring
Me.Form.RecordSource = rststring
Me.Form.Requery
For Each varI In lst_Services.ItemsSelected
lst_Services.Selected(varI
Next varI
Me.lst_Services.Requery
the lines that crash access are the For...Next loop, regardless of where i place them - take them out everything's fine (except lst_services shows up with old selection).
any ideas??
- wes
cmb_county is unbound top-level filter that after update requeries form filtered on county selection, and also requeries lst_services to filter for that county.
lst_services is unbound listbox based on query filtered by cmb_county value. After lst_services update, form is requeried to filter based on both county and service selected. Records are in form detail, and everything works fine (choose county, all records for that county and services reload to just services for that county; choose service, only records for that service in that county show).
The only problem is that after a user has selected both a county and a service, and then chooses a different county, the services selection stays on their original selection, even though the county resets and the form shows all records for that county. So i'm trying to add code to 'unselect' the service, and it keeps crashing access (says it is generating err log but can't find).
tried changing lst_services on_update event to on_click, but still crashes when: pick county (form and lst_services update fine), pick service (form updates fine), pick new county (form updated fine but services still show old choice), pick new service (access hesitates and then crashes, doesn't even max cpu). cmb_county code to update is below.
rststring = "Select * from tSvcProviderDetail where [tCounty_ID] = " & Me.cmb_County
Debug.Print "cnty=" & rststring
Me.Form.RecordSource = rststring
Me.Form.Requery
For Each varI In lst_Services.ItemsSelected
lst_Services.Selected(varI
Next varI
Me.lst_Services.Requery
the lines that crash access are the For...Next loop, regardless of where i place them - take them out everything's fine (except lst_services shows up with old selection).
any ideas??
- wes
ASKER
bummer - good try i thought but still crashes when i click on services after changing county.
ASKER
actually i should add that it does seem to reset the lst_services box (requery and unselect any exisitng) but right at the end of that it hangs, then crashes.
possibly a fault with the form. the unselect does nothing that could crash it
try one of these two things
1. do a debug/compile - any failiures? make a habit of putting option explicit in your modules (form and general)
then do a debug/compile
2. try a compact/repair
any difference?
try one of these two things
1. do a debug/compile - any failiures? make a habit of putting option explicit in your modules (form and general)
then do a debug/compile
2. try a compact/repair
any difference?
is it definitely that bit of code?
have u tried creating a new button on the form and running just the listbox unselect code?
does it crash then?
have u tried creating a new button on the form and running just the listbox unselect code?
does it crash then?
ASKER
excellent point - in a separate button onclick it works fine. trying other stuff now . . .
whats the rowsource for the listbox?
Me.lst_Services.Requery
Me.lst_Services.Requery
ASKER
SELECT [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCou
FROM tService
INNER JOIN tSvcProviderDetail ON [tService].[ID]=[tSvcProvi
GROUP BY [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCou
HAVING (((tSvcProviderDetail.tCou
still can't get it to work. unless you have some quick ideas, will go back to button action to update form - too much time already!
FROM tService
INNER JOIN tSvcProviderDetail ON [tService].[ID]=[tSvcProvi
GROUP BY [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCou
HAVING (((tSvcProviderDetail.tCou
still can't get it to work. unless you have some quick ideas, will go back to button action to update form - too much time already!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
augh! so close! i like setting the rowsource - lots cleaner, and works fine with no crashes EXCEPT that whatever record was selected still shows up as selected if it exists. eg. if i pick a county where that service does not exist, lst_services shows none selected, but if i pick a third county that has that service, that service shows up selected (but does not filter main form query unless you actually click on it again - i've got that filter set to on_click). and yes, county is a number -
ASKER
ha! soon as i read what i wrote i caught it. not sure why, but setting lst_Services.value to null before setting rowsource seems to clear it out properly. points are all yours - thanks!!!
perhaps u need your unselect code in there then
I dont think u need to do Requery and resetting rowsource does it
Does the rowsource get set everytime u reset main form?
good news the crash is gone now
I dont think u need to do Requery and resetting rowsource does it
Does the rowsource get set everytime u reset main form?
good news the crash is gone now
ASKER
hmm - didn't try to put the unselect for...next back in, but setting it to null before loading new rowsource seems to have desired effect.
and yes, rowsource for lst_services should be updated each time cmb_county is updated, so that's why i put the update code for lst_services in the cmb after update. main form requeries on update of cmb_county OR lst_services. doesn't seem like it should have been this confusing, but i'm quite sure it's working now. still not quite sure what was crashing but assuming it was something about the order of update events that threw it . . .
thanks much though - glad to have it done!
and yes, rowsource for lst_services should be updated each time cmb_county is updated, so that's why i put the update code for lst_services in the cmb after update. main form requeries on update of cmb_county OR lst_services. doesn't seem like it should have been this confusing, but i'm quite sure it's working now. still not quite sure what was crashing but assuming it was something about the order of update events that threw it . . .
thanks much though - glad to have it done!
for i = 0 to lst_Services.listcount-1
lst_services.selected(i) = false
next i
var1 is not a number I dont think
selected needs a number I think