How do I re-execute the query a subform is based on?

[RoyKMathur]

Because my tables were already large I split them up by environment to speed up data maintenence and retrieval. Thus I have table names which contain the environment as part of the table name (ex: tbl_TEST_MyTable, tbl_PROD_MyTable). Once the user sets the environment they want to use (by clicking on an option button) I update the form's recordsource to the appropriate table. My form has 5 subforms within it.

Even with the split loading the form was taking a long time because some of the queries the subforms are based on are joining tables so that they can be linked (parent/child) to the form. I created indexes to speed up the data access which helped a little but it was still running too slow. So, I attempted to change the subform's queries to only join the necessary data by re-creating the queries each time the user changes the form's current record.

For example, what I had originally was taking too long:

[FONT="Courier New"]
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
  FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
    ON tb.Name = pt.TName
ORDER BY tb.Name;
[/FONT]

and the parent/child link fields are CollId, ProgName, and QueryNo.

This is the change I made:

[FONT="Courier New"]
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
  FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
    ON tb.Name = pt.TName
WHERE pt.CollId  = 'BATCH_COLLECTION'
  AND pt.ProgName = 'PROGRAM1
  AND pt.QueryNo = 123
ORDER BY tb.Name;
[/FONT]

where the pt fields are changed dynamically each time the user navigates to the next record in the form. This resulted in much faster access time; however I discovered that the form was always displaying the query that existed before the form was opened. I have not been able to figure out how to get the subforms to display the latest created query.

While debugging I discovered that the form's OnCurrent event was being executed 3 times before the form is opened. I don't know what I'm doing that's causing this.

I also discovered that the subform events are executed before the form's events, so I think that's why the latest query isn't being used. I attempted to requery the subform in the form's OnCurrent and OnOpen event, but to no avail.

 

[SamDeMan]

form.requery is a common command, it can refresh your query of your form whenever you want it to happen.

i noticed you use the asteric * in your queries. based on what i have read that slows down a query. i think i read that it is better to select every field than to use the *. maybe that will help. additionally, i don't know why you would need all fields in a subform.

there may be other things that will help you speed up, so i hope others will help you as well.

good luck,


sam

 

[RoyKMathur]

I've tried using form.requery in the form's OnOpen event yet it isn't executing the updated subform's query.

I'm only using * because in that case I actually am displaying all columns on the subform.

 

[SamDeMan]

why in the form_open event, use it on the field that the user chooses afterupdate event.

sam

 

[RoyKMathur]

The user isn't choosing a field. The current record contains the data that the subform needs to use. I am not using parent/child links because that is much slower than changing the SQL to link the data. Apparently what Access does is to execute the query and then after it builds the results set it then applies the parent/child filter. This is slow and inefficient.