Comparing 2 text boxes in a form and subform

[gauntletxg]

Here's what's going on. I have one table which contains information regarding bank deposits (customer, check number, date, etc. etc.), nothing out of the ordinary. My parent form links to this table. I have a second table which contains detailed information regarding these deposits, such as what department they are allocated to. The subform links to this table.

I will try to give an example to explain this. Say a company gets a check for $100 and deposits it in the bank, all of this information is stored in a single record in the first table. Now, let's say the marketing and HR departments each were responsible for earning the money. The second table will have 2 records, one showing that the marketing department earned $50, and the other showing that the HR department earned $50.

Ok, I hope that made sense. So, when data is entered via the form, the total amount of the deposit will be entered into the parent form, and the details of the deposit will be entered into the subform. In the form footer section there is a text box which calculates the sum of the detailed amounts. This should always match the total amount entered into the parent form.

So currently, I have a very simple "Verify Total" button, which runs a barebones macro that just closes the form, opens it up again and goes to the last record. Then the person can look at the total in the parent form and the total in the form footer section to make sure they're equal.

I'm thinking there has to be a better way of doing this, but I'm not sure of how to approach it. Ideally, I'd like to intercept (for lack of a better word) the previous/next/add record buttons on the navigation bar, and have Access compare the 2 totals first. If they match, then everything would continue on as normal. If they don't match, then a MsgBox would pop up informing the person, and they'd have to correct the entry before they can continue with whatever action they are trying to do.

If not possible, I think the same approach can be applied to the "Verify Total" button I have, so instead of having to compare the amounts manually Access will do it for the person and inform them of the result.

I'm still pretty new to Access, and I have a working knowledge of VBA in Excel. So I'm definitely not looking for someone to just give me an answer, but I need to know where to start because I'm really not sure.

Thanks so much in advance!

 

[TheAceMan1]

How are ya gauntletxg . . .

Outside of the tables you say you have, this can be done, although it'll take some code.

The basic logic you face is getting the check distributed proper for all departments entered! ... or all department values add up to the check deposited. [blue]If you get this right, your sums should always come out right![/blue] ... with no need to verify!

The method I have in mind involves summing the departments and subtracting this from the total check. [blue]This value would be set as the default for new records and would aid users in distribution of the check.[/blue] When saving records in this way, the [blue]BeforeUpdate[/blue] event would be used to perform the calculation and prompt if the current sum is over the check amount! Canceling the save op, allowing the user to make corrections.

I have problems in mind with your 2nd table, as it appears these fields should belong to the parent. This may come later, but for now, the above logic is what I purpose.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[gauntletxg]

Sorry for the super-late reply. Thanks for your idea, that approach had never dawned on me. I didn't implement exactly as you outlined though.

On the main form, below the box where the user inputs the transaction total, I added another box which subtracts the sum of transactions from the subform from the total in the main form. It's nested in Iif to display "No errors" if the difference is zero, which happens 99% of the time, and it displays the difference if it's not zero.

The only caveat is that the user has to click a button to more or less refresh the form to get the totals to populate. I've got some ideas for getting around this, but nobody has complained about it yet. Plus, my brain hurts.

Thanks again.