none
Add Multiple Notes for 1 Client RRS feed

  • Question

  • I have set the clients soc sec number as the primary key and have relationships with it on other tables. I need to be able to use the client's soc sec number to add new information weekly about that client (in a form), but I get this error message:

    "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Chage the data in the field or fields that contain duplicate data, removed the index, or redefine the index to permit duplicate entries and try again.

    I understand that error message, but now it's clear I do not understand Access very well at all. I am so new to Access. Are there some tuturials that specifically address my needs (listed above)?

    Monday, November 11, 2019 2:43 PM

All replies

  • You should not store Soc Sec numbers in an Access db it is simply not secure enough.  You should be looking at an alternative RDMS if you need to store such sensitive information.  Furthermore, it should be encrypted.

    Back to you question.  You shouldn't use the Soc Sec as the client's PK.  Create a standard autonumber field for the PK.

    You should have 2 tables

    Clients
    *************
    ClientID -> PK autonumber
    FirstName
    LastName
    ...

    Clients_Notes
    *************
    NoteID -> PK autonumber
    ClientID -> Number (with a relation to the Clients table ClientID)
    NoteDate
    Note
    ...

    Then you create a Clients form, and create a Notes form, and insert the Notes form within the Clients form linking the subform child/master ClientId.  Save it it should all work.

     

    As for your current error, if I were to hazard a guess, I suspect you set your Soc Sec number in the Notes table to Unique, and thus it will only allow you to make one entry per client.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, November 11, 2019 4:39 PM
  • If you follow Daniel's advice and use an autonumber ClientID column as the key rather than the client's social security number, and use a subform within a clients parent form for entering related notes, then you will need a means of navigating to a selected client record.  You'll find an example in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section on 'entering data via a form/subforms' includes a simple contacts form, within which is an employers subform.  The parent form includes an unbound 'Go to Contact' combo box in which a contact is selected.  Code in the control's AfterUpdate event procedure then moves the form to the selected contact record.  As personal names can legitimately be duplicated, however, the combo box's drop down list includes each contacts address data, in addition to their names, to differentiate between two or more people of the same name.

    If, rather than using a subform, you wish to use a stand-alone Notes form bound to a ClientNotes table, in which a client is selected when entering a new note, you can use a combo box set up in exactly the same way, but in this case with the foreign key ClientID column as its ControlSource property.  The form following the contacts form in my demo is an orders form in which a combo box is used to select a customer in this way.  The foreign key ClientID column should not be an autonumber, of course, and should be indexed non-uniquely (duplicates allowed).

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, November 11, 2019 6:32 PM Typo corrected.
    Monday, November 11, 2019 6:31 PM
  • Elly,

    I created a very basic demo (for your scenario) for you.  Feel free to download/review it by using the following

    https://cardaconsultants.com/files/Contacts_Demo.zip

    Look at the table design, forms, ... and you'll see it is pretty straightforward (once you've done it once or twice).

    Let us know if anything isn't clear.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, November 11, 2019 6:51 PM