Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent duplicate entries in a field (2002)

    I hope I can explain this clearly.

    I have a data table with two fields - student names (LNFN) and Activity. The LNFN field is setup to allow the name to appear more than once since the student can sign up for more than one activity. Activity field also allows duplicate entries.

    I have a form that opens up for a selected activity and you can then select students from a dropdown list to add to that particular activity. You add the name in the LNFN field and the selected activity is automatically added to the activity field. Is there a way to write some validation rule for the entire form (LNFN and Activity Fields) or for the LNFN field on that form that will prevent you from entering the same LNFN twice for the same activity.

    I can't eliminate duplicate LNFN entries in the Data table that the form is based on since that will not permit students to sign up for more than one activity. I just want to block adding the same name twice on the selected activity form when you are entering data.

    Thanks.

    Paul

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Prevent duplicate entries in a field (2002)

    Why dont you introduce an index in the table on both name and activity?

    Another way is to check on the form when you select a student from the drop down box and check if they alreay have that activity and if so reject that combo box entry.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent duplicate entries in a field (2002)

    Pat

    Could you explain a litle further how to check on the form if the student is already on the list for that activity. That was my idea, but I was not sure how to do it.

    Thanks.

    Paul

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Prevent duplicate entries in a field (2002)

    i would rather go the other way of making both fields an index that is unique.

    However, in the before update event of the student id do the check there if the studentID and activity are already in the table and set cancel=true if an entey is found.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent duplicate entries in a field (2002)

    Ok - I will try your approach.

    How do I do it?

    Thanks.

    Paul

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Prevent duplicate entries in a field (2002)

    In general, you can either catch a mistake or prevent it in the first place.
    In this case, I would try to prevent the user choosing someone who has already been chosen, rather than show them an error message if they do.

    To do this the combo box of people does not just draw its names from the table. Instead you have a query, but the query has a subquery. The subquery selects all the people currently assigned to the activity. the main query selects all the people not in the subquery. To make this work you need to requery the combo in the oncurrent event.

    I attach a demo.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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