Query in a Form Only Once Part II

[DrewMan]

My last question wasn't specific enough so I narrowed it down and have pictures that should make it easier to understand what I am talking about.

So I have a main form that looks like this:

** SNIP **

Where you see the Lines column represents how many lines are on a specific record so for example Bloom Township has 145 Lines on it.

I am pulling from the following Query:

** SNIP **

The query is doing a Count by the IOB ID Num and the query Bid Status Query is the Record source for the Sub Form of the main form Bid Coordinator Main Form. The Sub Forms name is Bid Status Form.

So my question being once this query is run and you open this form it only Loads the Bid Status Query ONCE and that's it unless the person clicks a button that refreshes the Query. Cause as of now it takes around 45 seconds-1 minute to fully load this and it's used multiple times back and forth so it's just taking forever to Load.

[TheSmileyCoder]

First off, why do you join the customer table when you do not use of the fields from that table?


I think the long load time is caused in part, by grouping on the bid name, which I am guessing is not indexed.
Second, off, I think what I would do is to create a seperate query, to do perform the count of bid items, and then use that in your subform.

Are you sure your code is not doing anything active to requery the form? I dont believe the form requery's itself, unless asked to, allthough I do believe it will refresh the active record, which is what you may be experiencing. Having never had the need myself, I haven't really looked much into it. You could try setting the recordset TYPE to a snapshot instead of a dynaset and see if that helps anything.

Finally if none of the above works, a solution could be to have code that runs on form open, as well as on click of refresh button, to run a DELETE and a APPEND query to insert the records into a temporary table, and show that in the form.

Or if you can live with a denormalized database calculate the count and store it in the parent table (Bid information).