Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Validation issues

    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!
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  4. #3
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi!

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

  5. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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?
    Regards
    John



  6. #5
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  7. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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?"
    Regards
    John



  8. #7
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?

  9. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Attached Files Attached Files
    Regards
    John



  10. The Following User Says Thank You to johnhutchison For This Useful Post:

    tinghting (2011-07-19)

  11. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts
    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!
    Last edited by WendellB; 2011-07-11 at 08:39. Reason: Acknowledge John's post
    Wendell

  12. #10
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thank You

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •