Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Autonumber to numeric and back?

    Hi all... long-time lurker, first-time poster.

    I have an Invoice table that uses an Autonumber to generate the Invoice number. The autonumber starts at, say 10000, and increments by 1 every time a new invoice is generated.

    A user came to me with an old invoice from an old Alpha 4 application (I know) and asked if we could put it into the new database with its original Invoice number, which was less than 10000. Like a dummy, I said sure.

    Without thinking what I was doing, to add the invoice I changed the autonumber field to numeric and added the old invoice to the new table. Then I went to change the field back to autonumber. Obviously (now), that didn't work.

    I wrote a little fix to deal with my plight to get a new invoice number (using VBA to add 1 to the highest invoice number when a user clicks on the "new" button to gen an invoice), but there are a few complications I'd like to avoid, such as the fact that even though the field is a key field with no duplicates, using Autonumber, if someone deleted an invoice that number would just go away forever. As it is now, if someone deletes the last invoice that number can be reused. A minor thing, but one I'd like to avoid.

    I guess this was a long story to ask a short question: knowing that Access even states in its online help that no datatype can be changed to an autonumber, is there, in fact, a workaround for this to return a field from numeric datatype to an autonumber?

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Quote Originally Posted by scheese
    Hi all... long-time lurker, first-time poster.

    I have an Invoice table that uses an Autonumber to generate the Invoice number. The autonumber starts at, say 10000, and increments by 1 every time a new invoice is generated.

    A user came to me with an old invoice from an old Alpha 4 application (I know) and asked if we could put it into the new database with its original Invoice number, which was less than 10000. Like a dummy, I said sure.

    Without thinking what I was doing, to add the invoice I changed the autonumber field to numeric and added the old invoice to the new table. Then I went to change the field back to autonumber. Obviously (now), that didn't work.

    I wrote a little fix to deal with my plight to get a new invoice number (using VBA to add 1 to the highest invoice number when a user clicks on the "new" button to gen an invoice), but there are a few complications I'd like to avoid, such as the fact that even though the field is a key field with no duplicates, using Autonumber, if someone deleted an invoice that number would just go away forever. As it is now, if someone deletes the last invoice that number can be reused. A minor thing, but one I'd like to avoid.

    I guess this was a long story to ask a short question: knowing that Access even states in its online help that no datatype can be changed to an autonumber, is there, in fact, a workaround for this to return a field from numeric datatype to an autonumber?






    No.......unless you know JAVA. For whatever reason I was able to over-ride this rule by updating the autonumber column in an access table using straight JDBC. In fact I was able to make all the autonumbers (that were the PK of the table) the same value. Very odd I know but it is true.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Yes. And you don't need to know Java.

    Step 1 - Copy and paste the table's structure to a new table.
    Step 2 - Change the new table's field to an Autonumber field
    Step 3 - Append all of the records from the old table to the new table.

    Your new table should start the AutoNumber from the highest number +1.

  4. #4
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Quote Originally Posted by DCKunkle
    Yes. And you don't need to know Java.

    Step 1 - Copy and paste the table's structure to a new table.
    Step 2 - Change the new table's field to an Autonumber field
    Step 3 - Append all of the records from the old table to the new table.

    Your new table should start the AutoNumber from the highest number +1.



    Thats great....but it doesnt solve his/her problem. If I am reading the issue right he wants to set the autonumbers equal to specific values that coincide with the invoice numbers.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    My interpretation of scheese's problem is that he change the current Autonumber field to a Long Integer to add one invoice and then tried to change it back to an Autonumber field and Access doesn't allow it.

    If I am wrong then hopefully scheese will clarify it more so that someone can provide more help.

Posting Permissions

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