Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011

    Unanswered: Trying to not use AutoNumber and running into difficulty

    Hello, if someone can give me a hand I would appreciate it,

    I am trying to setup my database without using an autonumber field for a seperate table (SerialNumber is a field that will not have reoccuring numbers)

    In my main form where you enter the data, I am gettin an error when you enter into a combobox a serial number that is already in use, if you use the dropdown and select a number it will error. I know why the error is there, assuming because it is trying to enter in a number that is already being used. I have the one to many relationship correctly flowing in the relationship, and this is the first time I have tried using this method. Do I need to use an autonumber or can I do what I am doing, and am just working it in improperly.

    Any help is appreciated.

    Attached Thumbnails Attached Thumbnails confused.png  

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Using an autonumber is never an obligation. However it never hurts to have one. The use of a serial number (supposed to be unique) and the use of an autonumber (Identity column being a better and commonly used denomination, there can be several types of them: GUIDs, Timestamps, Random Numbers, etc.) are not mutually exclusive.

    An identity column differs from a primary key. Its advantage is that it is handled by the system and cannot be modified, which cannot be the case with a serial number that must be input by the user and then is prone to several possible errors (it can be mistyped, one can try to enter the same data twice, it can be changed, etc.).

    I'm not sure to precisely understand the usage you do of the combobox you mention (select a record or enter a value) but it should not prevent you from using an autonumber (that should never be accessible by the users of the database) to build the relationships among the different tables, while using the serial number for identifying the items (it can be indexed with a unique constraint or even be the primary key).

    Anyway, to use Identity columns or not is a question that often inflames passions of almost fanatic fervour (as a search will easily demonstrate in the many threads found on the subject), and I won't go there. If you can live and have you database kept in a consistent state without using them, that's fine, do so. If (as I do) you consider that using Identity columns helps you in maintaining this consistent state, opt for using them systematically and in a consistent manner.

    If you need more help for the problem you encounter with the form and its combobox, please post details about these objects (names, bound columns, RecordSource for the form, RowSource for the combo, code being used...).
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    don't use an autonumber when there is a good 'candidate' primary key. that could be becuase the thing that makes the row unique could be. but there is no hard and fast rule thats says when you 'must' use or not use an autonumber column. such reasons could be:-
    not known at the time of row creation
    the candidate key is complex (say several columns a mix of alpha nad numeric data
    is subejct to (frequent change)
    on the face of
    possiblility of multiple vaild vlaues in what would otherwise nbe a unique column (eg the same serial number used by differnet manufacturers

    the error you are reporting sounds fair enough, after all if you havedeclared your serialnumber as unique, then by defintion you should nbot be able to select a serial number form exisiting known serial numbers (unless you are allocating serial numbers form another table. Id want to check that your combo box isn't limited to list
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2011
    Okay so if I am wanting to use that serial number again, how do I call it in, or reference it without it erroring, I know it must be somthing simple but I can't seem to figure it out.

Posting Permissions

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