clearing recordsource on close/unload not working

[dfingas]

Hi,

I have a complex form whose recordsource is an extremely slow query.
In order to speed up the loading, I set the recordsource of the form
and comboboxes on load, and 'empty' them in the unload event (set to a
zero-length string) as per the recommendation on Tony Toews' website
at http://www.granite.ab.ca/access/performanceforms.htm

This speeds things up when it works, but often it doesn't actually
clear these values. The unload and close events are firing (i.e. not
a simple case of corruption), and I've tried putting code like

Me.RecordSource = ""
Me.areaCombo.RowSource = ""

in both of those events, but when I subsequently look at the form in
design view, the recordsource and rowsources are still populated.

It does work sometimes, and particularly when I break on the unload
event.

Any ideas what I'm missing?

Thanks,
Daniel

 

[Arvin Meyer [MVP]]

Add:

Me.Requery

after changing the recordsource.

 

[Armen Stein]

Hi,

I have a complex form whose recordsource is an extremely slow query.
In order to speed up the loading, I set the recordsource of the form
and comboboxes on load, and 'empty' them in the unload event (set to a
zero-length string) as per the recommendation on Tony Toews' website
at http://www.granite.ab.ca/access/performanceforms.htm

This speeds things up when it works, but often it doesn't actually
clear these values. The unload and close events are firing (i.e. not
a simple case of corruption), and I've tried putting code like

Me.RecordSource = ""
Me.areaCombo.RowSource = ""

in both of those events, but when I subsequently look at the form in
design view, the recordsource and rowsources are still populated.

It does work sometimes, and particularly when I break on the unload
event.

Any ideas what I'm missing?

Thanks,
Daniel

You need to make sure the recordsource and rowsource properties are
empty *first*, in design. Then load them when the form loads, as you
are doing. Clearing them out in the unload event will usually *not*
change the design of the form - in fact, it's an anomaly if it does.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Van T. Dinh]

It sounds to me that you may have code in your database that save the design
of the Form. Normally, the RecordSource / RowSource set in the FormView are
not saved when you close the Form.

1. One of the common mistakes in this case is that in the code to close the
Form (DoCmd.Close), the acSaveYes option. New users often think this option
is used to save the data before closing which is incorrect. Data is usually
saved automatically and the the acSaveYes option is actually used to save
the *design* of the Form.

If you do have the acSaveYes in your code, remove it should fix the problem.

2. If you use A2000 or later, you may like to set the Property "Allow
Design Changes" (in the "Other" tab of the Properties window of the Form) to
"Design View Only".

 

[dfingas]

That seems to have fixed it. Thank you!

Daniel

Add:

Me.Requery

after changing the recordsource.

 

[dfingas]

Thanks Van and Armen,

Interestingly, I hadn't specified the acSave option, so it should've
defaulted to acSavePrompt, and I did not receive a prompt. I'll keep
your suggestions in mind for my future development though, because I
think acSaveNo is something I should normally be using.

In design view, I cleared all the recordsources/rowsources, but it
normally saved new values over them. Fortunately, Arvin's suggestion
seems to do the trick.

Daniel