[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Unselect Listbox value after requery err

Posted on 2006-05-31
13
Medium Priority
?
498 Views
Last Modified: 2008-02-26
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) = False
    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
0
Comment
Question by:new_wes
  • 7
  • 6
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801092
try this

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
0
 

Author Comment

by:new_wes
ID: 16801154
bummer - good try i thought but still crashes when i click on services after changing county.
0
 

Author Comment

by:new_wes
ID: 16801182
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 16801212
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?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801244
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?

0
 

Author Comment

by:new_wes
ID: 16802205
excellent point - in a separate button onclick it works fine.  trying other stuff now . . .
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802502
whats the rowsource for the listbox?

Me.lst_Services.Requery
0
 

Author Comment

by:new_wes
ID: 16802670
SELECT [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCounty_ID]
FROM tService
INNER JOIN tSvcProviderDetail ON [tService].[ID]=[tSvcProviderDetail].[tService_ID]
GROUP BY [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCounty_ID]
HAVING (((tSvcProviderDetail.tCounty_ID)=Forms!Main!cmb_County)) ORDER BY [tService].[Service];

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!
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16802716
hmm, try this

when u requery

Dim sSql as String

sSql = "SELECT [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCounty_ID]  " & _
           "FROM tService " & _
           "INNER JOIN tSvcProviderDetail ON [tService].[ID]=[tSvcProviderDetail].[tService_ID]  "  & _
           "GROUP BY [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCounty_ID]  " & _
           "HAVING (((tSvcProviderDetail.tCounty_ID)= " & Forms!Main!cmb_County & ")) ORDER BY [tService].[Service]; "


Is your form called Main?
if so u dont need to do Forms!...

Now what is the bounded column for cmb_Country, is it a number value?

'DIAGNOSTIC
msgbox "County is " & cmb_County

sSql = "SELECT [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCounty_ID]  " & _
           "FROM tService " & _
           "INNER JOIN tSvcProviderDetail ON [tService].[ID]=[tSvcProviderDetail].[tService_ID]  "  & _
           "GROUP BY [tService].[ID], [tService].[Service], [tSvcProviderDetail].[tCounty_ID]  " & _
           "HAVING (((tSvcProviderDetail.tCounty_ID)= " & Me.cmb_County & ")) ORDER BY [tService].[Service]; "

lst_services.rowsource = sSql


Setting this, I think u dont need to do the unselect


0
 

Author Comment

by:new_wes
ID: 16802874
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 -
0
 

Author Comment

by:new_wes
ID: 16802912
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!!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802922
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
0
 

Author Comment

by:new_wes
ID: 16803052
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!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question