Web Development

Ask something
Discussion Locked

Speed up sub-forms

0 helpful
6 replies

by bambamz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a database that has an initial form and then 4 sub-forms. When a person calls in and gives their SSN, it is input and then the name auto fills in from another table. Then the sub-forms appear with history information from four different areas on this employee. This is so there is an accurate record of all the calls placed by this person. I currently have the sub-forms become visible after the SSN is input and then set back to blank and not visible after they cycle to a new call. Theproblem is that when they load the initial call it takes 45 to 60 seconds for the form to load. After it is loaded it is quick. I have the Option Explicit at the top of each module but do not know what it means when it says you must explicitly declare all variables using the Dim, Private, Public
, ReDim, or Static statements. I have Private Sub before each action is that all it wants? I don't get an error. I need to speed it up.

6 replies

by seagull data services

Assuming you're using access.
To speed up your subform loads, leave all of the subform recordsource properties blank.
This will prevent the form from loading the data before the form is completely loaded.
After that, you have several choices including:

Set the recordsources to whatever they are currently and let Access handle the subform data, or

After you've entered the ssn, set each of the subforms recordsource properties to an sql statement specifying exactly the records you want tosee., e.g.

me..form.recordsource="select from where .ssn='" & me. & "';"
me..requery

You can set the recordsources back to blank when you go to a new cycle.

Option Explicit would have nothing to do with the recordset performance. It's used to predetermine the type of variables you're using in advance, unless you don't specify and everything reverts to a variant. This can save on memory and can speed up program execution. It also helps finding mistyped variable names. 8o)

by bambamz

Ok I tried this. Setting the record source to blank does speed it up. But then where do I put the SQL statement that re-sets them? They are connected by master child link so do I have to specify field list and is that every field on the subform?

by bob sellman

One way to use this method is to NOT specify any links between the master and subforms.

The recordsource that you use for each subform will then be a query which is based on the same table as you previously used as the source for the subform withthe addition of including a where clause to link with the master form.

For example, if your master form contains a control named txtSSN and the SSN is the column used to link the data among the columns (and forms/subforms), the query will containfor the sub form table's SSN column should be set equal to the value in txtSSN on the master form. (This is easily built using the query builder and right clicking on the box in the criteria row under the SSN column for your table, click on build, and select the txtSSN control on the master form.)

Using this method you will probably have to set the update property of the SSN control on the master form to requery each of the subform controls whenever the SSN is changed. Otherwise the data in the subforms will not automatically change.

If txtSSN is initially blank, you may not even have to set the source for the subforms to null on loading.

This discussion is closed to new replies.