Jun 17 2011, 03:02 PM
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.
Private Sub cmdCalculate_Click()
Dim rst As Recordset
Set rst = Form_sfrmFreightInvoices.RecordsetClone
Do Until rst.EOF
Form_sfrmFreightInvoices.txtPercentOfShipment = Form_sfrmFreightInvoices.[Quantity] / Form_sfrmFreightInvoices.[txtTotalQuantity]
I am sure it is some small thing that I am missing. Perhaps a fresh set of eyes can spot it.
Jun 17 2011, 06:41 PM
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.
Jun 17 2011, 11:35 PM
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.
Jun 18 2011, 01:38 PM
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,
Jun 18 2011, 01:42 PM
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.
Jun 18 2011, 10:00 PM
>>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:
If the code is executed in the context of the MainForm, then you can use Me like:
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.
Jun 20 2011, 10:10 AM
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.
Jun 20 2011, 10:36 AM
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.
Jun 20 2011, 11:52 AM
I see. I think I will just use the Recordset directly. This seems to accompish what I want.
Thanks for your help!
Jun 20 2011, 05:57 PM
You're welcome... Glad we could help...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here