Max(Date) from subform?

[whenthegeeseinvade]

Hi everyone.

I have a main form and subform, both displayed as datasheets.

For each record in the main form, the subform contains a number of records, each with a date field. I want to show the Max(Date) field from each subform in a text box on the main form.

I have tried putting a Max(Date) textbox in the subform and then referencing this in the main form but I keep getting #Error in each of the main form records that refers to the Max(Date) field until I expand the subform.

How can I ensure that the main form reads the calculated field from each subform without this problem?

Thanks very much for any help.

Laurence

 

[Galaxiom]

Don't use Date as a name. It is reserved and can give you grief.

Place a textbox of the subform footer with =Max(Datefield) as the Control SOurce.

Place a textbox on the main form with the control source as the textbox in the footer of the subform:
=Forms!formname!subformcontrolname.Form!texboxname

 

[whenthegeeseinvade]

Thanks

This is how I'm getting the data but the records on the main form all still show #Error until I expand the subform for each record and they then update to the correct value.

Could it be that the main form loads forst, then the subform and so the main form doesn't know what the subform's calculated data is until it is refreshed? What would be the syntax for this?

Cheers

 

[SOS]

Actually subforms load before the main form does. So that isn't it.

 

[whenthegeeseinvade]

Fair enough.

It does seem to be to do with refreshing / display in some way or another as when I expand the subform the Max(ReminderDate) calue it retrieves is also displayed in the following record's fields on the main form to which it is not related. Weird huh?

 

[SOS]

Any chance of uploading the database (with any sensitive data removed and sample data in its place)?

 

[whenthegeeseinvade]

It's a huge beastie that i've struing together over five years. I'll try and strip out the relevant tables and forms. It'll probably take me half an hour. Many thanks.

 

[SOS]

Make sure to run COMPACT AND REPAIR and then ZIP it before uploading. (if you can get it cut down to size).

 

[whenthegeeseinvade]

Here's the database - hope it works.

See the form frmInternalWorksReminders

Many thanks,

Laurence