temporarily getting #error in a calculated control

[Paul James]

I have a calculated control (named txtTotal) on a main form (named
frmInvoice) that displays the total of the Amount field in a subform (named
frmDetail). As long as the main form is displayed in form view, this
txtTotal control in the main form displays the correct total at all times -
when the form first opens, and as soon as one of the Amounts is changed in
the subform.

However, when I display the main form in datasheet view, where you can click
the plus sign at the left of the rows to display the subform as a
subdatasheet, the total field doesn't initially display the total of the
amount field in the subdatasheet. Instead, when I first open the (main)
form in datasheet view, the values in the Total column display #Error. When
I click the plus sign to open the subdatasheet for a given record, then the
Total field for that record (and that record only) changes to display the
correct total amount. The Total fields in the other records still display
#Error.

Is there anything I can do to get this calculated control to correctly
display the total of the Amount field in the subdatasheet without requiring
the user to first open the subdatasheet for each record?

Thanks in advance,

Paul

 

[Wayne Morgan]

I've run into a similar problem before. When in spreadsheet view, the data
in the subdatasheet isn't seen until it has been opened once. One option
would be something similar to this in the form's Load event.

DoCmd.Echo False
Me.SubdatasheetExpanded = True
DoEvents
Me.SubdatasheetExpanded = False
DoCmd.Echo True

I haven't fully tested this, but it appears to work.

 

[Paul James]

Works great, Wayne. Thanks much.

Paul