Which form property to check so loop can iterate? (1 Viewer)

[Margarita]

Hello, I apologize if this is in the wrong section- I wasn't sure whether this belongs in Vba or Forms.

My users use the AddInvoice form to add invoices to the Transactions table via unbound textboxes and listboxes. The user's job is to type in the invoice total amount, and allocate that whole amount to categories (administrative, clinic services, etc) by typing in a PercentperCategory. The save button inserts into Transactions the AmountperCategory calculated from the total and the %. Currently, to save 100% of the invoice pieces, the user picks a new cateogry, types in a new percentage and hits save- she repeats this until she completes 100% of the invoice.

Now, I want the code behind the save button to keep track of the sum of all the AmountperCategory entries and compare the sum to the InvoiceTotal. I want this done because we've had quite a few invoices end up in the table with a total amount that is off from the true total amount by a few cents- due to rounding errors from the db's calculation of InvoiceTotal*PercentperCategory. I want the user to be notified when the summed amount is off so she can take an appropriate action.

So- I need the code to keep track of the sum of individual AmountperCategory entries on a loop. This loop should keep going until the user gets to 100% of the invoice. But- before going to the next iteration of the loop, the user has to have the chance to change the Category and PercentperCategory. Otherwise, the loop will just keep adding identical entries. And that's my problem- I don't know how to make this loop stop and wait for the user to update the necessary fields. Which properties of the AddInvoice form do I need to check to see if the user has updated the fields? And is this even posssible?

This is a rough version of my code:

Amountcounter= amountcounter + me.InvoiceTotal*me.PercentperCategory
 
Percentcounter= percentcounter+ me.PercentperCategory
 
Do while Percentcounter<=1 
 
If percentcounter= 1 and amountcounter< me.invoicetotal then
Msgbox “You have allocated 100% of the invoice but the sum is off. You have to manually change the entries to add up to the full amount”
goto outofloop
Elseif percentcounter= 1 and amountcounter> me.invoicetotal then
Msgbox “The sum of your entries is greater than 100% of the invoice. Please subtract the extra amount manually from individual entries.”
goto outofloop
End if 
 
‘’’’
‘My insert statement is here.
‘’’’’
 
Msgbox “Please continue to allocate the rest of the invoice to get to 100%.
 
 
Check_if_can_go_to_next_iteration:
 
‘HERE, I need to check whether the fields were updated by the user since the last iteration. I’m thinking something like this:
 
If some property= true then
Go to nextloopiteration
Else
Goto 
Check_if_can_go_to_next_iteration
End if
 
 Nextloopiteration:
Loop
 
outofloop:

I apologize for this long winded question. Hopefully, I worded it so it makes sense. Can anyone tell me if this is a valid way of doing this sort of thing? Or is there another way to keep track of AmountCounter without resorting to a loop? I am open to having the user having to hit the Save button each time they do a change of Category and PercentofCategory. But I was thinking that every time they re-start the Save button code, AmountCounter gets re-set to 0 agian so there is no way for me to keep track of it other than with a loop.
Thank you. I appreciate any insight on this.

 

[Margarita]

Hello, I think I found a way to insert the necessary lag into the loop to give the user a chance to update the Category and AmountperCategory and then re-enter the loop. What I did is create a messagebox that's not attached to any window using the user32.dll messagebox. This is how the last part of the loop (after the insert statement) looks now:

nextloop:
'pop up messagebox 
'When the user updates category and amount per category she will hit ok and the loop will keep going

UserEnteredNew = MessageBox(&O0, "Please enter the next category and Amount per Category for this invoice and click ok below.", _
"Enter the next category", vbOKOnly + vbSystemModal)
If UserEnteredNew = vbOK Then
GoTo cangotonextiteration
End If
cangotonextiteration:
Loop
outofloop:

This messagebox is not modal so the user is able to go back to the AddInvoice form and update the fields that she needs. Then she clicks ok on the messagebox and the loop starts rolling again.

Now, the lag works and the loop now waits for the user. However, my problem is that the loop goes to the next iteration and inserts an identical entry- that is, it doesn't pick up the fact that Category and AmountperCategory have been updated by the user. It keeps reading the same values as in the previous iteration.

Once I figure out how to make the loop see the new values, I will post back here for anyone using this thread as a reference.

Thanks.

 

[Margarita]

I resolved this by making sure that the focus is lost from the Categroy and PercentperCategory fields. Before, the entry that was inserted into Transactions was identical to the last one because I would enter a new category and Percentpercategory and click ok on the external messagebox- and since I didn't click anywhere else on the main form, the focus stayed on percentpercategory, so the AmountperCategory (the field that auto-populates when PercentperCategory gets changed and loses focus) never had the chance to re-calculate. The losing of focus of PercentperCategory happens right after user clicks ok on the messagebox:

nextloop:
If UserEnteredNew = vbOK Then
'this makes sure that the percentage field lose their focus and the amount field is auto calculated
AmountperCategory_box.SetFocus
GoTo cangotonextiteration
End If
cangotonextiteration:
Loop
outofloop: