Autonumber Substitute

[mura]

I've added an ID field to a table (records were imported) and would like to autonumber each record, but of course, can't use the autonumber data type. Other than manually typing in a number for each record, is there a quick way to input the numbers? Your assistance is appreciated.

 

[jfgambit]

Mura:

If you add an addtional field to the Table and call it let's say...AutoID, set the Type to be an Autonumber, your records will get numbered.

HTH

 

[intrep11]

is there another way

Is there a way of generating a unique id other than auto number ?

i am setting up a DB for 16 remote locations to do personnel review which i want to combine in Head office (this wasnt the orignal idea but it has developed as they do) unfortunatelly this is going to have to be file transfer as the locations are ships and they are not connected except by email. (i want to keep this all electronic you see)

Any suggestions on generating an id no for the records that i can then use in a union query in the head office to update records or enter new records from each location can you append the auto number field with the ships ID code ? or is there a better method of generating the records id in the first place ???

other advice on the whole project greatfully recieved
ta

 

[Pat Hartman]

Why do you think that you cannot use the autonumber data type? If you define your table to have an autonumber primary key, you can use an append query to load it with existing data. As long as the old key values are numeric and unique, you can just append them to the autonumber column (this is the ONLY situation in which an autonumber may be manually supplied). Then Access will start assigning new autonumbers at n+1 with n being the largest appendef value.

 

[intrep11]

thanks

So i simple set up a local ID anto number and tehn a master one in head office

i read somewhere that that didnt work i havent got to that bit yet to try it so i am pleased to find it is easier than i thought it would be.

 

[jfgambit]

There are a couple of ways to go about this and each has it's pros and cons. You might consider creating an Export that saves the information to an Excel spreadsheet that you can send to each office to update. You could then "lock" or "hide" the column containing the Primary Key so that users would not accidently remove or change it. A Module or Macro could then be written to import, append and update the data they return to you. All updates would be based on the primary key so any changes to a record from the field would show up in your database. If you where going to utilize this suggestion you might want to consider creating a unique id that can easily be reproduced in case the field employees do change the spreadsheet, perhaps the employee id.

Another way and one that I recently worked with a company on was Synchronizing. If your employees in the field have Access you could create a Synchronized database and send it out to the field for updates. They would work in the forms you create to update information and then return the completed updated Access db to you via e-mail. You could then create a module or manually open and synchronize the database to the Master in your office. One catch with this process is that if two individuals making changes to the same record you need to make sure that you synchronize in the correct order. Not sure if you will have multiple individuals working on the same record.

Access help files can assist you in learning more about both of these options (Transferspreadsheet function and Synchronization), but feel free to ask for additional help if you get stuck.

HTH

 

[Pat Hartman]

Autonumber won't work if you are adding records in multiple copies of the db. If you use replication, Access converts each autonumber to assign a value randomly rather than sequentially. This won't work in many situations because negative numbers as well as very large numbers may be assigned. If this is your situation, you will need to generate your own numbers. To do this search the archives for autonumber and DMax() to find lots of code examples.

 

[intrep11]

the plan

The plan is that the ships (of which there are 16 that will be using this) will generate the records and then export them to the office.

jfgambit The users on the ships are not that advanced and i want this to be a one way process we have enough admin to do with this already i dont want to add another layer although for other things we do that might be a viable option so i may come back and ask in the future

Pat i wasnt planning to use Replication each site dosnt need to have the others data only the head office do (this is personnel info) infact i could get into hot water if i allowed that as these are all confedential reports
thanks for the lead i will look up Dmax() and come back if i dont get it or need help.