Unbound form

[Database Girl]

I am trying to speed up a form by making it unbound. On the Main Form, the
record source is set when the form is loaded and cleared when the form is
unloaded. I also have three tabs, one has a unbound subform, and the other
two have bound subforms.

On the form Header of the Main Form there are three cbo boxes that I use to
filter the records by Manufacturer, Division, and Location. On one of the
tabs with a bound form, I have another cbo box with a list of Orders for the
particular Manufacturer, Division, and Location selected in the header of the
Main Form. Also on this tab there are fields that are associated with the
record source of the Main Form. Once I select the order from the drop down
list I get the following error:
2427 - You entered an expression that has no value.

So in other words, it is not loading the fields from the record source on
the Main Form.

When the form was bound is worked fine.

Any idea why loading the record source OnLoad acts different then making the
Main Form bound to a select statement?



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/communitie...a761e0&dg=microsoft.public.access.formscoding

 

[Allen Browne]

This is a bug in Access. If a form has no records, and no new record can be
added, the Detail section goes blank, and controls that are still visible in
the Form Header or Form Footer misbehave. More info and example in:
http://allenbrowne.com/bug-06.html
More info on the conditions that trigger this:
http://allenbrowne.com/casu-20.html

Back to the original issue though. Saving the form without a RecordSource,
and then assigning one in Form_Load probably won't speed it up in most
cases. There may be something else going on here too.

 

[Database Girl]

I am working with a Access 97 Database. The only reason I am going to the
trouble of unbounding some of the forms in the database is because when the
backend of the database get over 40 kb the forms on the frontend work very
slow. Twice a day the Backend of the database has to be repaired and
compacted. Do you have another ideas on how I can improve the performance of
the database?

 

[Allen Browne]

... Do you have another ideas on how I can improve the performance
of the database?

Hmm: that will involve tracking down what's causing it to be slow. It might
be that the records are selected or sorted on a non-indexed field. It might
be that you have too many subforms on the form, or too many combos or list
boxes that each contain too many records. It it might be the code you have
in the events that run when the form opens (Open, Load, Current, Activate,
and the events of the first control to take focus, in both the main form and
the subforms.)

As a general guide to improving Access performance, it is worth working
through Tony Toews' list:
http://www.granite.ab.ca/access/performancefaq.htm

And just for comparision, it is possible to build a form bound to say
100,000 records, with up to 10 subforms, and 20 combos (between the main
form and its subforms), and still have it opening in < 2 seconds.