Problem using aggregate in form I need to be editable 1

[RobertIngles]

Hi all;

Here's the challenge I am facing. I need to use two aggregate values in relation to a form.

One is to identify which records to display based on an AVG value of the Pass/Fail/Not tested choices using 1,2,3 as the numerical value. If the AVG value is 0 then the form can be displayed.

Second aggregate value is to identify which order that the records appear. It is based on the aggregate count value of another table.

If I utilize either aggregate in the query the form is based on, the fields are no longer editable (I have text fields and combo boxes required to be editable by the user).

I have been able to place the countn aggregate value into the form using DLookup (thanks again Hookum), and I am assuming I can do the same with the AVG value however I have no idea how or if I can use them to select and set the display order when the form is opened.

Is there any way to utilize these aggregate values to select and filter the form results and still allow the form to be editable?

Thanks!!

 

[dhookom]

You could create records in a temporary table. Make sure you join this to your form's record source based on primary keys.

Duane
Hook'D on Access
MS Access MVP