I have a form with a subform that is a continuous form. The subform contains bound controls that I would like to calculate with a command button once all of the lines have been added. The first calculation is a percentage of the total quantity. I have this code in the on click event of the command button. It will populate the first record, but does not calculate any subsequent records unless I select them and click the command button again.



I am sure it is some small thing that I am missing. Perhaps a fresh set of eyes can spot it.

Thanks,
Pat

Hi Pat. Maybe I don't understand what you are trying to do, but I thought you might consider another approach: Put a Sum for the amounts in the footer of the subform, then calculate the value you want on each line (say, percentage) in a text box on each subform data line. As you add subform records, after you leave the record, the calculation will adjust.

See the demo attached. But as I said, perhaps I haven't yet grasped your requirement.

HTH
Joe

The updates using the RecordsetClone are more or less independent of the Form. The first row may be the CurrentRecord at the time and it gets corrected by the "Auto RowFix" (or some similar name) of JET engine.

You need to use the Refresh Method on the SourceObject of the SubFormControl to refresh the data currently displayed on the SubForm.

Thanks Joe,
I do have the control in the footer of my subform to total the records and was able to display an unbound control with the correct percentage. I would like this to be a bound control. Is this possible?

Thanks again for your help,
Pat

Thank you for your post.

I tried adding a refresh to the Percent control, but it didn't like it. I have added after the Loop statement, but it does not refresh each line. Can you elaborate on which Source Object I need to refresh?

Thanks again for your help.
Pat

>>Set rst = Form_sfrmFreightInvoices.RecordsetClone<<

Missed the above earlier but I don't think that is correct if you want to refer to the "SubForm" in the MainForm currently open. I suspect that the above actually open a hidden instance of the of the Form [sfrmFreightInvoices] as an independent Form.

Try using the Immediate window to find the RecordCount of rst c.f. with the number of records in the "SubForm" being displayed.

To refer to the "SubForm" in the opened MainForm, you need to use:

Forms!MainForm!SubFormControl.Form

If the code is executed in the context of the MainForm, then you can use Me like:

Me.SubFormControl.Form

Note that the name of the SubFormControl can be different from the name of the Form being used as the "SubForm". You need to check the Control name in the Design View of the MainForm.

I have changed the code as you suggested, but I got the same result. In the immediate window I found that the record count for subform was correct. The values calculate correctly, but do not appear on the form unless I move to each record and execute the code.

I then changed RecordsetClone to Recordset and the values populated the bound controls for all records

I think I am good now, unless you know of a problem that might result from this change.

Thanks again,
Pat

You can either use RecordsetClone and then Requery the SubForm or you can use the Recordset directly.

However, note that the MoveNext/MovePrevious/... on the RecordsetClone do not change the Current Record on the SubForm while the same methods performing on the Recordset will move the Current Record of the Form.

Traditionally, in Access 97 and earlier (not sure about Access 2000), we could not Access the Recordset directly so we have to use RecordsetClone. Since then we can use the RecordsetClone or the Recordset directly.

I see. I think I will just use the Recordset directly. This seems to accompish what I want.

Thanks for your help!

Pat

You're welcome... Glad we could help...