Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2011
    Posts
    23

    Unanswered: Can a check mark field populate based on data in another field?

    Can I have a check mark field autopopulate based on data in another field?

    Example:
    A member is eligible for a lottery if they have attened a conference for 5 consecutive years. As is stands today, we ahve to manually check the Eligible Check mark when we change the Yrs Consecutive field to 5 or more. Can it be set up to where when the number entered into the Yrs Consecutive field is 5 or more, the check mark automatically goes in?

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Yes, but it is absolutely redundant to have one field be determined by another field in the same record. It is a waste of disk space.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes place some code behind the yrs consecutive control that sets the flag as rerquired.
    that will probably be in the after or before update event, or possibly the on change /on lost focus event

    however as ruralguy says its irrelevant as you have the information already available. there is no point storing the same or derived data. although storage space can be an issue, I don't think it is here but there is the more significant issue of data integrity. ferisntace someone could set / reset the flag indepenadantly of the yrsconsecutive column and you end up with the data out of step


    UNLESS you can justify why you need a separate flag column... that could be ferinstance they only get one shot at the lottery (ie only one 5 consecutive years guarantees an entry (so you ownt enter the same person next year if they don't attend this year.

    however I'd argue the real problem here is that the table design is flaky. what you probably ought to do is to store individual attendances by year.. that will remove the need to maintain your years consecutive column
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2011
    Posts
    23
    I will say without a doubt the table design is flaky I have been brought into help with design changes and I see lots of issues with this, but my time here is limited and definetely not long enough to overhaul it.

    So would I be better to have the consecutive years field update based on the attendance information? is this possible? The Consecutive date field is in a table called Trade Shows, but there is a another table that tracks the Convention year and amount paid for booths. Is there are way to update teh consecutive yrs field by data that is in the Convention year field? I may be grasping here, jsut thinking out loud

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    The point is, you don't need a 'Consecutive years' field, as it is derived knowledge, and derived knowledge should never (in a well designed system) be implemented itself...


    For example...

    If you know that Mr. X first attended a convention in 2002, then again in 2003 and once more in 2004, you know from this information alone that they attended three consecutive conventions.

    You don't need to, and indeed shouldn't, perform this calculation and store it into a seperate field on a table... Doing so greatly increases the chances of invalidating the core system and the data stored within it, as there is more to go wrong. Instead of having to validate one set of data, you're adding a second set of data that needs validating as well, which will only end in tears.
    Looking for the perfect beer...

  6. #6
    Join Date
    Aug 2011
    Posts
    23
    Quote Originally Posted by kez1304 View Post
    The point is, you don't need a 'Consecutive years' field, as it is derived knowledge, and derived knowledge should never (in a well designed system) be implemented itself...


    For example...

    If you know that Mr. X first attended a convention in 2002, then again in 2003 and once more in 2004, you know from this information alone that they attended three consecutive conventions.

    You don't need to, and indeed shouldn't, perform this calculation and store it into a seperate field on a table... Doing so greatly increases the chances of invalidating the core system and the data stored within it, as there is more to go wrong. Instead of having to validate one set of data, you're adding a second set of data that needs validating as well, which will only end in tears.
    Ok, this make sense...I get that so what would be the solution when looking at a record in form view to know quickly if the person is eligible for the lottery. If they don't have the checkmark field they will have to go into the subform and count the entries, which would take away from what they are trying to accomplish by quickly seeing a check mark inthe box to tell them yes or no that person is not eligible.

  7. #7
    Join Date
    Mar 2007
    Posts
    277
    The system can automatically count the entries in a SubForm with no intervention from the user. The SubForm has a Recordset.RecordCount property that has this information.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Yup, as RuralGuy says, there are a number of ways to calculate whether they are entitled to enter the lottery or not. If you need help on how to do this, then we're here to help.

    A checkbox could be used (or many other controls for that matter) to inform the user that an attendee should be entered into the lottery. But it should not be used as a means to control whether they are to be entered.
    Looking for the perfect beer...

  9. #9
    Join Date
    Aug 2011
    Posts
    23
    Thanks, I think I understand this now and will look for a way to not duplicate with the checkmark field.

Posting Permissions

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