Solved Controlling total amount billed and total record count based on checkbox selection (1 Viewer)

[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

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

You are doing two things when only one is required.

There should be ONE money field. Checking the attested value should NOT be adding/subtracting anything. The query that displays the data should create the "attested" amount field based on the value of the checkbox.

Select ..., IIf(CheckBox = True, AmtField, 0) As AttestedAmt From yourtable;

This IIf() gives you 0 or some amount based on the attested flag which seems to be what you want.

The fact that you are using arithmetic implies that you can add the attested amount multiple times. If you insist on manipulating multiple fields when one should do, at least copy the amount from a to b rather than adding/subtracting.

When you store "duplicate" data you open yourself up to data anomalies. If the AttestedAmt field doesn't actually exist, it can never be in conflict with the checkbox. You might want to do some reading on database normalization.

 

[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

 

[Pat Hartman]

So, you check a box on a row in a subform and that causes an amount to be added to an unbound control. Is the check saved? What happens when you close the form and reopen it?

Maybe your solution isn't the way to accomplish what you want. Rather than asking us to fix your "solution", perhaps, you could tell us more about the purpose of doing this and what part of it becomes permanent and what results you expect. I'm probably going to stick with my initial suggestion since it requires no code. You can create a control in the footer of the subform that sums the "attested amount". No loop is required. The solution is permanent. as long as the field is checked, the value will be copied to the field you want to sum and the form handles everything.

PS
- in VBA, you reference the CURRENT record of a form or subform. If you want to loop through the recordset, you would use the recordset clone, NOT loop through controls.
- Access does NOT support userforms. If you search for info on userforms, what you get will not necessarily work with Access. It is for Word and Excel.
- The most efficient way to reference a control on the form in which your code is running is to use -- Me.ControlName -- the .Value property is the default property and so is most commonly omitted. Using "Me." gives you intellisense which eliminates a lot of typine.

 

[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

 

[Pat Hartman]

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

 

[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!