Solved Controlling total amount billed and total record count based on checkbox selection

[abette]

Hi, I have a form with a Check box control called Attest. When the user marks the checkbox I would like the Attested Amount Billed to increment based on the Amount Billed value for the record/row. In addition, I would like the Attested Recs to count the number of marked checkboxes. How can this be done? I am not a VBA coder so I appreciate any help you can give me. Thank you. Ann Marie

 

[abette]

I put together this VBA module that I am attaching to the AfterUpdate event property of the Attest checkbox control. Obviously, it's not going to work bc I am not good with VBA but it at least has the pseudo code that I am looking to program!
Private Sub AttestationCheck_AfterUpdate()
Dim AttestedAmountBilled As Integer
'Checked
If AttestationCheck.Value = -1 Then
AttestedAmountBilled.Value + AmountBilled.Value And AttestedRecs.Value + 1
DoCmd.RunMacro "mcrAttestCheckboxControlProcess"
End If
'Unchecked
If AttestationCheck.Value = 0 Then AttestedAmountBilled.Value - AmountBilled.Value And AttestedRecs.Value - 1
End Sub

 

[abette]

I'm not sure I understand what you are trying to do but I do know that it is not auditable. I suggest rethinking the process and instead of using a checkbox to update a record, think about a process that inserts a new record in a child table. That way you can see who added records, when they were added, and the individual amount. To get the total, you would use a query that sums the details.

The concept is similar to the two ways to maintain inventory. The novice thinks that there should be one record per item and you plus or minus the value. The expert creates transactions that are used to add/subtract from the quantity and the transactions are summarized to produce the quantity on hand.

I am not sure I understand your method. All I am looking to do is verify if the checkbox control on my form is marked and if it is add the Amount Billed from control value to the Attested Amount Billed form control. If the user unchecks the control is should subtract the Amount Billed from the Attested Amount Billed.

 

[abette]

Hi Pat - Thank you for your feedback. The Attested Amount is not an actual field I am storing on my table. It's an unbound control on the form that just shows the user the running total of the AttestedAmount when they check the Attest checkbox. So if the user checks Attest, it will add the Amount Billed value to the AttestedAmountBilled form Control. For every Attest box checked, the AttestedAmountBilled should reflect only those attested Amounts. The TotalAmountBilled is not a field on the table either - it's just a control that displays the Total Amount Billed of ALL the records displayed.

 

[abette]

I tried this VBA code but it's going down on the For Each line

Private Sub AttestationCheck_AfterUpdate()

Dim MyAttestedGrandTotal As Currency
Dim MyRunningTotal As Currency
Dim MyAttestedRecsTotal As Integer

Dim ctrl As Control
Dim ctrlType As String

'What control type to loop through
ctrlType = "Checkbox"

'Loop through each Checkbox control on UserForm
MyRunningTotal = 0
For Each ctrl In frmWeeklyClaimsReviewQueueSUB.Controls
'Checked
If AttestationCheck(ctrl) = ctrlType Then
If AttestationCheck.Value = -1 Then
MyAttestedGrandTotal = AttestedAmountBIlled.Value + MyRunningTotal And MyAttestedRecsTotal = MyAttestedRecs + 1
MyRunningTotal = MyRunningTotal + AttestedAmountBIlled.Value
Else
'Unchecked
MyAttestedGrandTotal = AttestedAmountBIlled.Value - MyRunningTotal And MyAttestedRecsTotal = MyAttestedRecs - 1
MyRunningTotal = MyRunningTotal - AttestedAmountBIlled.Value
End If
End If
Next
DoCmd.RunMacro "mcrAttestCheckboxControlProcess"
End Sub

 

[abette]

I made adjustments to my code so now I am getting this error

 

[abette]

Hi Pat - I tried your method using a calculated field in the query that is the record source of my form. I actually created 2 calc fields one for the AttestedAmount and another for the AttestedRecs and it worked!

So no VBA required. I can't thank you enough for your help and suggestion. You gave me a very simple solution to what I was thinking was going to be more complex.
Regards,
Ann Marie

 

[abette]

Always glad to help. I've been developing software for a long time and by the time I came to Access, I had already written my million lines of code and didn't need the practice so I was very pleased at what I can do with queries and property settings. Code is always my last option

You are a lifesaver. I am still learning Access as you can tell!