Help Comparing Data Through VBA

[gnarpeggio]

Hello,

I'm working on a database currently that needs validation of an amount before a record can be saved. Since I'm very new to VBA, I'm hoping someone might be able to assist me in this procedure.

So my database is working with checks and monetary deposits. On the particular form I'm working on, I need to assign a module to a field that will check the sum of a field located within a subform and compares or validates it with the amount entered on this specific field.

Here's my problem. Since the "Total" field only returns a value (I've set the Control Source property to =Sum(Amount)), I'm not sure if I can set the module to do this.

Basically, I need my DB to do the following:

On the BeforeUpdate Event, the module will assess the data after the "Total" sum has been caluculated and then compare it with what the user enters in the "TotalAmount" field. Example, if the total amount was $1,000 and the user entered $1,001, then the module would alert the user that these fields do not match.

If there is any way to do this, please provide some insight.

Thank you!

 

[boblarson]

Quick question - If you can calculate the amount, why have the user enter in the same amount and not just do it automatically?

 

[gnarpeggio]

If I could set up a module to do this then I wouldn't be objected to it. The person requesting this change might be interested in this solution as well.

Would you know how to set something like this up? I appreciate your reply to this thread

 

[gnarpeggio]

Taking your advice into account, I've decided to use the calculated amount and set it to a field on this new form. This way, users on the other end don't have enter anything as the total amount is already listed.

Here's the new challenge:
My original table is a record of each check that our office receives. Whenever a deposit is needed, my staff creates a new deposit number on this table (named "ROC#" for its field) and deposits a group of checks per number. This deposit number is in a One-to-Many relationship with another table that has these deposit numbers as the Primary Key. This deposit number table is the record source of the form I'm currently designing.

Next, I created a query to pull the amount from the original table to have it present on the new form. The problem I'm having is to get a calculated amount of each individual deposit number group instead of the whole amount of every record. I've tried to use the Sum function on the query, but doing so will render my form non-updateable. Any suggestions (If this post makes sense)?

Thanks!!!

 

[gnarpeggio]

Any solutions available for the description in my last post? I basically would like for this field in my new form to contain the calculated total for each deposit number group, not the individual amounts of each check.

Is there a way to achieve this without creating a group total field?

 

[boblarson]

Any solutions available for the description in my last post? I basically would like for this field in my new form to contain the calculated total for each deposit number group, not the individual amounts of each check.

Is there a way to achieve this without creating a group total field?

You can use a subform with a grouping query to get the total for each deposit group.