Unbound form requery issue

[kparker]

Hello,



This is my first post, however I have been searching for a solution to this issue for ages.

I use access to create a front end display screen for our production lines.
each production line (7) has barcode scanners that record when a product is started on the line and at key stages until completion, all of this is feed into an sql server run by corporate bods.

I have read access to the sql server and use access to query the data.

previously the form was all bound to a single main query with approx 15 sub queries calculating the data such as planed volume vs actual qty and qty at each stage of the lines / and model type qty's
However this was an issue if any one of the sub queries returned null data from the linked sql it caused the whole main table to be blank.

I therefore created an unbound form and used dlookup's in each of the textboxes on the form to calculate the data for each line, this worked great, if a line is off the textbox is just blank, not the whole form.

The form is always read only and never needs any user input, i just use the event timer to turn off painting then requery and turn on painting again.

however using this approach although the form is requeried it does not update the textbox value, unless i add a recalc, it seems that i cant use the repainting = false with the recalc and then the form remains blank until all the querys have run, (about a minute) this obviously looks bad as the screen is blank longer than with data.

I have done quite a bit of coding but would like to keep the main calculations / queries simple and others need to understand what I have done, or i would have just hardcoded the lot!!!

I can provide the access file if it helps but as all of the data is stored in the linked SQL tables would not show much.

Basicly is there a way to force a requery of an unbound form, have it update the value and use the painting off function?

I have tried to use the form with only one textbox and it works ok its just the slow access that we have to the sql and the mass of info per production line that causes it to appear blank for a minute

I do not care how long it takes as long as its still displaying data, the screen is set to refresh each 2 minutes.

I hope that you understand a bit and can at least point me in the right direction, or i'm faced with writing the screen in PHP....

Thanks in advance Kev

[June7]

Maybe instead of a single big nested query, use form/subform arrangement.

[boblarson]

previously the form was all bound to a single main query with approx 15 sub queries calculating the data such as planed volume vs actual qty and qty at each stage of the lines / and model type qty's
However this was an issue if any one of the sub queries returned null data from the linked sql it caused the whole main table to be blank.

That's why you use a query with OUTER JOINS. You would create a query to pull ALL of the productID's and then you link the other subqueries indvidually to that other query by using an OUTER JOIN which flows from the productID to the subquery (select all products and only those which match the subquery).