Results 1 to 13 of 13
  1. #1
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15

    Sharepoint and its AutoNumber Primary Key Requirement

    So I recently created a database for my military squadron to assist in Personnel, Training, Resource, and Deployment requirements. When I built the Database I used an Individual's SSN as the Primary Key and Relationship connector for the various records. When I went to upload the back end of my database to Sharepoint, however, it turned all my SSNs into autonumbers. To fix this problem, I edited the database to include a new field called ID with an autonumber and made it the Primary Key but used the SSN field as the primary relationship. Now, however, none of my macros work that were used to create new records. My reports still show data connected correctly, but I can no longer generate new records. Looking for any suggestions to fix this problem. I cannot put the back end on a network drive, it needs to go on either Sharepoint or a Teams site if able.



    Carrinmere

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your macros likely need to be rebuilt because a parameter they use no longer exists. We are talking macros here and not vb code, right? Most of us don't use macros but we can sometimes advise.

    I confess I've never dealt with SP beyond doing automation from Access but this doesn't sound right
    but used the SSN field as the primary relationship
    In Access alone, the PK field should be the autonumber field. It's usually accepted that PK values should never be meaningful data. Maybe that's not what you meant by primary relationship as I'm not sure there is such a thing. Relationships between PK and FK fields are not usually described as primary, secondary or anything else like that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    So to be honest I am learning Access from Trial and Error/ Access2019 for Dummies/ Youtube/ and forums like this. So if the primary key was always meant to be non useful data then I did not operate that way. I do know it has to be unique so that's why I figured SSNs could be used. But there are some VBA codes I used when asking for passwords to open up new forms and these VBA codes no longer work either.
    The code I used was

    Private Sub Command0_Click()
    Dim strInput As String
    Dim strMsg As String
    strMsg = "Please Enter the Password for Mobility Folder"
    strInput = InputBox(Prompt:=strMsg, Title:="Mobility Folder Password")
    If strInput = "Deploymentsandstuff" Then 'password is correct
    DoCmd.OpenForm "Mobility_Folder", acNormal, "", "", , acNormal
    DoCmd.GoToRecord , , acNewRec
    DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSavePrompt
    Else 'password is incorrect
    Exit Sub
    End If
    End Sub

    And the code DoCmd.GoToRecord , , acNewRec is the one that now crashes after I changed the primary key. The code will no longer generate new records.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here's a couple of things to make your involvement here more productive:
    - always give the error message at least, and number is a useful bonus since some are similar in message but not in cause
    - post your code within code tags (# on posting toolbar) - pictures of code are often a poor substitute.

    With suggestion number 1 I could have made a guess but without the error info it's not advisable; could send you down a rabbit hole.
    When you don't provide a parameter for a method don't pass a zls (zero length string) as its value. 1st goes here, , , , last goes here. If not, drop all the commas.
    I have to wonder why that line didn't raise an error with 2 parameters being the same and one being in the wrong place. Perhaps the last should be acWindowNormal, which is the default. If all you want are the defaults (both supplied parameters are the defaults), no need to pass any parameters.

    Not sure what you're attempting to Save. Only design changes need to be saved in Access - not records, not forms and reports, etc. that have not been altered in any way.
    If your research hasn't covered normalization, it is a must. Since you seem to be on a quest for knowledge I'll just add all the links I usually post for novices. In the meantime, you could consider posting a db that you first copy, then compact/repair then zip and post here. Look at the top forum bar if you need help with posting attachments. Or we could guess further if we had the error message you're getting.

    EDIT- as an afterthought, I suppose the error number is 2015 or similar related - because no form name was provided, thus the active object is assumed. An input box would be the active object here, methinks.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    So the error I am getting is Run-time error '2105': You can't go to the specified record. When I hit debug it highlights DoCmd.GoToRecord , , acNewRec

    This only started occurring once I added ID Field as Autonumber and changed it to the Primary Key in all applicable tables while keeping SSN as the field for relationships. Below is how I currently have my relationships established. If I am doing it wrong I am all ears as I am learning this by the seat of my pants.
    Click image for larger version. 

Name:	Relationships.png 
Views:	30 
Size:	26.7 KB 
ID:	46060

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you're in 'repair' mode, I'd give the ID fields meaningful names. When you see them elsewhere (esp. code) they often mean very little. Better: MobilityID or Mobility_ID ( I refrain from using underscore). If you read the normalization links you will see that your relationships are not correct. Rather than me repeat the explanation, you should review the info as it has already been well documented there.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    Thanks for the suggestion. Will read about Normalization this evening.

  8. #8
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    So the reason I gave the ID fields the name ID is because when the back end of the database is moved to Sharepoint, the primary key field becomes ID with Autonumber as its field type. Before I made the change above, I made SSN the primary key. Now I read and watched tutorials about normalization and understand it, but it seems like I have already unintentionally normalized my database by having SSN being the only repeatable data field. Everything else is unique to that table not repeated anywhere else. I just cannot figure how I can make relationships between all the tables if every primary key has to be ID and Autonumber since Sharepoint forces it to be that way. If I am missing something important then I am all ears because google and youtube are leading me in circles.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have to point out that my only experience with Access and SP is automation code. If you're saying you have to name them "ID" because of SP then I guess you do what you have to do. For Access, you do not repeat fields. If that is also a SP requirement I'd be surprised because it is not an example of a normalized db. If you're not saying that it is a requirement, then your relationships pic does not indicate to me that you understand normalization (sorry to say).

    If Personnel is a parent and related table to RA_Issue, then the SSN field you have in RA_Issue should be a numeric (long) field named (e.g.) PersonnelFK with values that are the PK field from the parent (ID).

    EDIT- forgot to say that it's bad form to have any spaces or special characters in object names. If you follow the links on naming, there is proof of that there. Also, you appear to be breaking up data into tables unnecessarily. Isn't marital status an attribute of a person (Personnel)? Could be argued that unless they can have more than one spouse or you care to archive records if they re-marry, then spouse could be an attribute of Personnel as well. However, since you're including children data, then family members should all be in their own table with a link between Personnel.ID and tblFamilyMembers.PersonnelID_FK. You are also repeating data fields just like a spreadsheet. You should not have issue1, issue2, etc. in the same table as different fields for the same sort of thing. Then there is # of children - that is an example of a calculated field which you'd solve with the family members table.

    All this isn't meant to be negative criticism but is intended to help you avoid having to research Access issues every other day, because that's where you're headed with this. Do review, study and understand the linked info and save yourself a lot of future head banging.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    I apologize if my naming convention is off. Personnel is the Parent Table which includes name SSN rank and so forth. RA_Issue is a table dealing with gear issued to that Individual to include type of gear and dates issued. The only thing tying that table to Personnel is the SSN which I was using to build the relationship. My database works well Until I post the backend on share point and then it changes all my primary keys to auto numbers. So trying to figure out how to build the relationships with the primary keys on all tables as auto numbers.

  11. #11
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    I completely understand why you would wander about separating Personal (Marital and children) from the Personnel Table but it was intentional. The Personnel Table consists of everything personnel wise related to their military jobs such as Rank, Date of Rank, Performance Reports, when the last medal was awarded and physical fitness scores. The Personal table consists of family life like next of kin, number of children, and so on, separate from work related stuff. I wanted to separate the tables based on work vs home life else the Personnel Table was going to be huge.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No need to apologize. I suppose I should bow out and let someone with more SP experience chime in. My comments about the rest of the design stand and are only me trying to help. Yes, it may have worked but that's not a measure of good design IMHO. Hope you get a solution here real soon.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Carrinmere is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    15
    I appreciate all your help and am definitely looking to design it better. Ultimately it needs functionality and the ability to work off of sharepoint since our Shared Network drives are going away and everything will be on sharepoint.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Why use autonumber primary key fields?
    By gwboolean in forum Access
    Replies: 12
    Last Post: 10-29-2017, 11:15 AM
  2. Tables and Primary Key autonumber
    By snoop832 in forum Access
    Replies: 5
    Last Post: 11-28-2016, 09:11 AM
  3. Primary Key Autonumber
    By data808 in forum Access
    Replies: 19
    Last Post: 07-15-2014, 06:51 AM
  4. Sequential autonumber primary key
    By JackCampion in forum Access
    Replies: 0
    Last Post: 09-17-2012, 05:07 PM
  5. Replies: 2
    Last Post: 04-30-2010, 09:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums