Results 1 to 7 of 7

Thread: How do I set a primary key as something the user enters?

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    55

    How do I set a primary key as something the user enters?

    Hi,
    I have a database with 2 tables; OrderDetails and CustomerDetails (link below).
    The OrderDetails table has OrderID as its primary key which is generated in Access. It also contains CustomerAccountNo.
    In the CustomerDetails table, there is a field called CustomerAccountNo as well. This is what I want the Primary Key to be for this table but I'm having problems with it. This is of a 'Text' data type of length 10 characters and will be entered by the user (the reason it's text is because I need any leading zeros). Just wondering if anyone can help me out.

    Link:
    https://drive.google.com/file/d/0Byk...ew?usp=sharing

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,290

    Re: How do I set a primary key as something the user enters?

    what is the problem? i just started access, created a new empty database, a new table, set the ID field that was generated and marked as primary key to text(10) and saved the table, just worked fine.
    another thing: your leading zeros are a display requirement not a storage requirement, so you could still use a number field. however i would not use a number manually entered as primary key to start with.

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    55

    Re: How do I set a primary key as something the user enters?

    Quote Originally Posted by digitalShaman View Post
    what is the problem? i just started access, created a new empty database, a new table, set the ID field that was generated and marked as primary key to text(10) and saved the table, just worked fine.
    another thing: your leading zeros are a display requirement not a storage requirement, so you could still use a number field. however i would not use a number manually entered as primary key to start with.
    I'm assuming you got it work by setting the AccountNo to a Number data type.
    The reason I was going to use a manual primary key was because using a 'normal' one seems wasteful. In my database, each AccountNo will be unique which is why I thought using that as a primary key would make sense. But the AccountNo is something that already exists on paper so the database is simply acting as a form of electronic storage in a way (I'm doing a lot more in it using VB). So I ideally need to be able to make a manual primary key.
    Also if I make the AccountNo a Number data type and enter format as '0000000000', will that keep leading zeros? They'll be needed for display purposes

  4. #4
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,290

    Re: How do I set a primary key as something the user enters?

    I'm assuming you got it work by setting the AccountNo to a Number data type.
    no, it is text with length 10 chars (the column is not called AccountNo but that should not matter)

    The reason I was going to use a manual primary key was because using a 'normal' one seems wasteful. In my database, each AccountNo will be unique which is why I thought using that as a primary key would make sense. But the AccountNo is something that already exists on paper so the database is simply acting as a form of electronic storage in a way (I'm doing a lot more in it using VB). So I ideally need to be able to make a manual primary key.
    you might find this interesting: http://www.vbforums.com/showthread.p...ht=natural+key there are some arguments that apply to your situation but in the end you need to find out what fits your needs best

    Also if I make the AccountNo a Number data type and enter format as '0000000000', will that keep leading zeros? They'll be needed for display purposes
    i dont work with access databases (or its GUI) that much, but yes the format should define how the value is displayed, i.e. with leading zeros or not.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,612

    Re: How do I set a primary key as something the user enters?

    There's no requirement from a primary key to numeric. It can be any data type you want. The only requirement is that it be unique and non-null.

    Also if I make the AccountNo a Number data type and enter format as '0000000000', will that keep leading zeros? They'll be needed for display purposes
    I have no idea but if you do that you're fixing the wrong problem. If you want your account number to support leading zeros then it's not a number, it's a string, and it should be implemented as a string type (probably a varchar). Note that this does not preclude it from being used as a primary key.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    PowerPoster kfcSmitty's Avatar
    Join Date
    May 2005
    Posts
    2,222

    Re: How do I set a primary key as something the user enters?

    If the primary key needs to be numeric, you could always save it as numeric on the backend, without the leading zeros. Then, when displaying it to the user, or receiving it from the user, massage it into the format you want.

    I'm a true believer in save it in the datatype you want and use, not what the user wants. You can format it however the user wants when they work with it, but you're the one dealing with it in your code.

  7. #7
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: How do I set a primary key as something the user enters?

    magic,
    is there ANY reason why you should use anything else then what the current paper users are used to ???
    just make sure that field has an validation rule on it that says the lenght has to be 10 and it have to be 10 numbers (0-9)
    suggestion: Like "##########" as a validation rule, it is requered and can not be a zero lenght string

Posting Permissions

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



Click Here to Expand Forum to Full Width