LDP
I understand your confusion.
Basically, I am suggesting to use a autonumber or serial number as the primary key. And the Document number will be used as a description.
For example, and I am guessing here...
tblMasterDoc
DocID - autonumber, primary key
SDID - text or numberic, eg. 267.00, 267.01
+ document details - dates, owner and stuff
tblProject (ad libing here)
ProjectID - autonumber, primary key
DocID - long interger, foreign key referencing tblMasterDoc
+ project details...
The project table references the document table using the DocID number which is generated by the system (hence the name autonumber). The user will only see the SDID, your current document numbering system. And the SDID will only exist in one locatiom, the tblMasterDoc, or whatever you have called it.
Lessons learned from days gone by...
In a past life, I worked as a programmer / support rep. The software house wrote account software for government. I had an on-going discussion with one the senior programers. He was using the G/L (general ledger) account number as the primary key. This seemed to work -- except peridiocally, the G/L chart or numbering system had to be changed - incorporate new rules, legislation, etc. Since the G/L account number was the primary key used in transactions, invoices, and numerous balance and history tables, this task was extremely awkward, and took a considerable amount of time -- days and days. And business stopped during the conversion.
I finally won my argument, 2 years later. A sequence number was used as the reference, and the only location for the G/L account number on on G/L master table. Afterwards, renumbering the G/L chart took minutes and not days. Subsequent to this, all other senior programers were instructed to use sequence numbers as the primary key in their tables.
Your situation reminds me of this past situation.
Next, more about autonumbers. When creating a field for a table in Access, one of the types offered is "autonumber". Basically, this is a long interger that the system or Access controls. For simple databases, it is a very convenient system.
The typical way autonumbers works is that uses a sequential numbering system - 1 - 2 - 3, etc. If a person starts entering a new record, the system generates the next sequential number, - 4 - in this case. The one thing that bothers some people is that if the user now aborts the entry, the - 4 - is lost, not used, and the next sequential number is - 5 -. This is just the way it works, and the data integrity is not at risk.
For a replciated database, things get interesting. for autonumbering, Access uses a random number instead of a sequential number. I have never had problems with this, but I know others who have warned developers on this issue. (See FAQ written by MichaelRed).
Per changing your documentation number. Which is your original issue...
If you decide to use SN ID number as the primary key, you will still have to break the relationships between your master and associated tables. Then change the SN number in each table. Then recreate the relationships.
If you want to try using the autonumber, you will only have to fix the SD records in the Master table. But you will have to modify your forms, reports and queries.
After backing up the database
You can run the update with an update query. Review the generated listing before committing. I suspect this would be the simplest way. But it becomes an all or none thing -- once the update is run, re-running the query to fix some missed records may result in messing up other records.
However, would be tempted to do this in code simply because it would allow me to perform the record update which would give me more control.
However, this probably a personal thing. I feel more comfortable using code. I know others would prefer using straight SQL. SQL is definitely faster. VBA would allow you to check records before updating.
Richard
