Results 1 to 9 of 9
  1. #1
    lonlyspartakos is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    9

    Talking Primary Key need to be reconfigured to AutoNumber without changing the Field Data Type?

    Hi everyone my first post and I am pretty sure its not my last


    Here is the thing guys

    I started a access DB with more than 700 hundred records.
    I have CusID Field which is a primary Key in VIPsInfo table and foreign Key in other tables.
    My problem now is i made the CusID data type (Short Text) and i used to enter it manually.
    I am looking for changing my manual entry to an Auto numbering or auto filling .
    i know it was a stupide thing for me to do it at the first place and foolish thing to hope it can be fixed after 700 hundred records.
    one more thing my CusID sequence is "CUS0001,CUS0002...CUS0700..."
    i think this is everything

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    1. remove the PK designation from CusID field, don't delete the field, just don't designate as PK

    2. create a new field as autonumber type and designate it as primary key

    3. check first and last records to see if the new autonumber coincides with the old CusID sequence - if they do then expect everything record in between does as well

    or

    1. create new table with autonumber field as well as all the other fields, including the existing CusID

    2. copy/paste records from original table to new table

    3. if all good, delete original table and rename new table


    Now in either case, run update SQL actions on dependent tables to remove the CUS prefix and 0s
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lonlyspartakos is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    9
    Quote Originally Posted by lonlyspartakos View Post
    Hi everyone my first post and I am pretty sure its not my last
    Here is the thing guys

    I started a access DB with more than 700 hundred records.
    I have CusID Field which is a primary Key in VIPsInfo table and foreign Key in other tables.
    My problem now is i made the CusID data type (Short Text) and i used to enter it manually.
    I am looking for changing my manual entry to an Auto numbering or auto filling .
    i know it was a stupide thing for me to do it at the first place and foolish thing to hope it can be fixed after 700 hundred records.
    one more thing my CusID sequence is "CUS0001,CUS0002...CUS0700..."
    i think this is everything
    thanks you for your reply ..I totally got your step but about CusID at other tables ; would it work still as a foreign key as well ?

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    or.....

    just do J7's step 2. create a new field as autonumber type and designate it as primary key

    then in the form when a new record is created - have a little code that writes the autonumber field's value into your existing CustID field (which can be hidden or locked if you want)....being a text field it will happily accept an integer.... and continue to use your existing keys/foreign keys

    what this is doing is simply using autonumber to then auto write a value into your existing key field for you....

  5. #5
    lonlyspartakos is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    9
    Thanks NTC , I am going to go for J7's step 2 and create and autonumber column ,

    NTC and June7 I shall be grateful for you guys
    (to be greedy)
    but do you know away to help me write that code .cause frankly I haven't used codes before :").
    so for more details ( Autonumber table "VIPsInfo";one of the foreign keys tables is "AllCards"

    thanks in advance and for everything

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Would need code to save the constructed ID into field. The real trick is figuring out what event to put the code into. Perhaps the form BeforeUpdate.

    In the event property select [Event Procedure] then click the ellipsis (...) to open VBA editor at the procedure. Type code.

    Me!CusID = "CUS" & Format(Me!ID, "0000")

    Then NEVER add new records by working directly with table or query. Unless saving this value can be accomplished in table Data Macro. I've never used them so can't say for sure.

    However, might be better if you just fix the data. Modify the existing FK fields to remove the prefix then change the field to number type. Now can be linked to the new autonumber ID field in queries and form/subform as well report/subreport.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is probably too late, but be sure you have a BACK UP copy (or 2 or 3) of your dB .... just in case there are problems and you want to start over with a clean dB.......

  8. #8
    deepucec9 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    34
    use macro "set value" to field: cusID
    "CUS"&[ID].
    where [ID] is an autonumber field
    This works for me.

  9. #9
    lonlyspartakos is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    9
    thanks guys its worked
    I shall be grateful for you

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

Similar Threads

  1. Replies: 3
    Last Post: 01-13-2015, 04:45 PM
  2. Changing Data Type of Imported Field Name
    By rhubarb in forum Import/Export Data
    Replies: 12
    Last Post: 06-16-2014, 08:20 AM
  3. Replies: 2
    Last Post: 12-04-2013, 08:09 PM
  4. Replies: 2
    Last Post: 03-19-2013, 09:36 PM
  5. Replies: 4
    Last Post: 05-07-2012, 12:08 PM

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