Validation issues

**tinghting** · 2011-07-08 10:31

Hi, I'm creating a database of contracts and have run into some issues when it comes to validating the forms.

Each contract in the database is taken on by multiple affiliates which each take a certain percentage. So in the form for creating a new contract entry, I have a subform that allows you to pick from a group of affiliate and then type in what % of the contract they have taken on. I want to be able to validate it so that the % amount of all the affiliates combined does not exceed 100%. So if the first affiliate took on 90%, when putting in the next affiliate's information it couldn't exceed 10%.

Also, contracts generally last a year, so when the incept date is put in, I want the expiration to automatically go to 1 year after that date. I currently have in the AfterUpdate of the incept date
"Me.Expiration_Date = DateAdd("yyyyy", 1, Me.Incept_Date)"

However its not working---I have gotten this to work previously, but it seems fickle and only works some of the time so I am not quite sure what is the issue.

Hopefully someone out there has some insight!
Thanks in advance!

**johnhutchison** · 2011-07-08 18:13

It is not fickle..it is just fussy.

It did not like:

Me.Expiration_Date = DateAdd("yyyyy", 1, Me.Incept_Date) because it has 5 'y's instead of 4.

**tinghting** · 2011-07-11 07:39

Hi!

The five y's was a stupid mistake, but I just changed it to 4 y's and it's still not working..

**johnhutchison** · 2011-07-11 07:46

Your example worked for me once I changed the 5 'y's to 4.

DateAdd.gif

When you say "Not working" what do you mean? Do you get an error message? or what?

**tinghting** · 2011-07-11 07:48

I do not get any error message, but after I put a date in for incept date and then click down to expiration date, the expiration date doesn't update to the date that is one year from the incept date.

**johnhutchison** · 2011-07-11 07:54

Is your database in a Trusted Location? What version of Access are you using? Later versions (2007 and 2010) disable all code unless the db is in a Trusted Location.
Do you see a message that "certain content has been disabled?"

**tinghting** · 2011-07-11 07:56

I am using 2007, but I just enabled the content and it works now! Thank you so much! I never realized that access would disable things like that. That was a big help!

Do you have have any idea on how to solve the other issue that I am having?

**johnhutchison** · 2011-07-11 08:32

Look at the example. It uses a Before Update event to check the total of the percentages, and cancels the update if the total is more than 100.

To make this work I needed to add a Primary Key (AffiliateTreaty) to the junction table, and change some field names.
Special characters (e.g. %) cause problems with coding, and using spaces in field names tend to make things more complicated.

A Before Update event happens before the change you are making has been saved. So if you are entering a new record it has not been saved. But if you are editing an old record the change has not been saved, so the previous value is still saved. So for calculating the total of saved values, I needed to exclude the record currently being edited.

**WendellB** · 2011-07-11 08:34

I presume you are referring to the issue of calculating the remaining percentage of the contract. There are a couple of ways of doing that. One would be to put a text box in the footer (or the header) of the subform showing the total percentage taken so far by the records in the subform, and have a validation rule or procedure that checked the amount of a new record to veryify it didn't exceed the amount remaining. The other approach that comes to mind would be to have an "before insert" VBA event procedure use DAO or ADO to open a recordset of the existing records, generate the sum of the existing contracts, and then cancel the insert if the new record would make the sum exceed 100%.

You may also want to put in some logic to prevent people from editing the percentage once the record has been saved, or at least do a check when a record is updated to make sure the total of all records doesn't exceed 100%.

Looks like John has implemented most of my suggestions in his example. I made the assumption it was his sleep time, but apparently not this night!

**tinghting** · 2011-07-11 13:09

Thank you John and Wendell! You were both very helpful in solving my remaining issue! Hopefully things go smoothly from here on out!

Thread: Validation issues

Thread Tools