Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table that contains the following fields:

    FN
    LN
    GradYear
    UniqueID - Key field

    I have things setup so that you can have two or more people with the same FN and LN in the database since the Unique ID is the identifier.

    How can I set things up so that you can not add the same FN and LN with the same GradYear to the database? If you would try to do that a message would popup and tell you that that name and with that gradyear is already in the database.

    Thanks for the help.

    Paul

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Paul,

    You could use a composite Primary Key.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just tried your composite key approach and it does not seem to work.

    I can still add an existing name with the same gradyear to the database and end up with the same student in the database twice.

    Any suggestions?

    Paul

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Paul,

    How did you set it up? It works for me in Access 2007.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Paul,

    How did you set it up? It works for me in Access 2007.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    RG's suggestion works to ensure your restriction is enforced.
    Not very relevant, though, but from a design point of view, it doesn't seem the best solution to me (no offense meant by this comment). What would be done in any other DBMS would be to create a second unique index, to ensure your restriction. This would also make it easier in some situations where you might need, in another table, a foreign key to this table, as you could use a foreign key to the single field primary key (UniqueID).

    The downside of doing this with Access is that the interface doesn't help adding composite indexes, other than the primary key. Anyway, if you feel a bit daring, you can create a new query, change to SQL View and paste something like this there:

    CREATE UNIQUE INDEX UN_FN_LN_Year_IDX ON YourTableName( FN,LN,GradYear);

    After pasting and changing for your actual table name, just click the Run icon.

    This will create a composite unique index, in addition to your primary key, and the index will ensure your restriction.

    Just a suggestion .


    Regards

    Rui
    Rui
    -------
    R4

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

    I got it to work. Problem was the UniqueID field that I had included in the key.

    Now I have another related issue. When I try to add a duplicate name from a form I created I don't get an appropriate "This is a duplicate" message. Can you help me with that?

    See attached practice file.

    Thanks.

    Paul
    Attached Files Attached Files

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, here you have it working. I did use the composite index, while maintaing UniqueID as the primary key.

    To solve your problem, I just added

    DoCmd.RunCommand acCmdSaveRecord

    before your statement to move to the next record.



    Regards

    Rui
    Attached Files Attached Files
    Rui
    -------
    R4

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

    Your sample database works great and does what I wanted.

    But - I copied the three objects from your database into my database. Using the form to add a name I still get the "Can't go to specified record" message instead of the duplicate message.

    I can't see what is different. Is there a basic database setting I need to change? Any suggestions?

    Paul

  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    By "three objects" you are meaning which ones?

    What code to you have for the onClick event for the Add to Database button?
    Rui
    -------
    R4

  11. #11
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I copied these three objects:

    tblStudentData

    CreateIndexUN_FN_LN_Year

    frmAddNewPersonToDatabase

    Code for button:

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec

    Me.FN.SetFocus

  12. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    That's surprising. Is the duplicate record added?
    Rui
    -------
    R4

  13. #13
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found the problem. Really strange.

    If I directly opened the add form I discovered it worked.

    If I opened the switchboard and clicked on the link to open the add form it would not work.

    I had to remove the link from the switchboard and then redo it. Now things work correctly.

    Thanks for all your help.

    Paul

  14. #14
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great .

    Regards

    Rui
    Rui
    -------
    R4

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    As Rui mentioned you can create extra indexes, using multiple fields.
    I thought it would be helpful to add instructions on how to do it.

    In table design, view the indexes. On the next blank line enter a name for the new index then choose the first field. Set the Unique property to yes. On the next line leave the name blank, and add the second field.

    [attachment=91136:UniqueIndex.gif]

    I have just updated this image. Previously the image was showing Yes for Primary Key. That was not meant to be there (and I don't know howit got there).
    Attached Images Attached Images
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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