Seeking Table Conversion Advice

[justphilip2003]

Back around 1997 I designed my inventory database and made a major mistake in my stock Table design, I made a volatile field the Primay Key. Over the years of using it I deleted old records and attempted to restructure it. Now I have search arguments based on the PK which missmatch the Stock no (ID). I would like to remove the Stock no as a primary key and reload the table, knowing that I will need to change all referencing code. Is there a recomended procedure? TIA Phil

[June7]

If your records are not properly related now, replacing the PK/FK values in mass probably won't help.

This is method to change PK (use copy of database for testing):

1. remove table relationships between impacted tables, if they are set

2. create a new field in Stock, populate it with the new key value (maybe an autonumber field)

3. create a new field in dependent table(s)

4. join Stock table and dependent table on the original PK/FK values

5. UPDATE the new FK field with new PK

6. delete the original fields

7. rename the new fields with old fields name

8. set PK and re-establish relationships

[justphilip2003]

I believe the records are properly related between tables, I took care to maintain relationships. So since I am going to explore this situation, what would the "replacing the PK/FK values in mass" entail? TIA Phil

[June7]

Following the steps I outlined.

[justphilip2003]

I was able to reorganize/structure both the Stock and Carving tables. I now have a new problem; the old code allowed me to select a new record (bottom of screen) and this became the StockID now it points to the new indexed Primary Key StockPK. I kept StockID indexed with no duplicates. When I create a new stock entry in my Form, the StockPK performs correctly but I am having difficulty locating the last used StockID to increment it to the a new entry. I looked at using RecordSet logic to position but all my examples perform the code in an AfterUpdate() EP or require a search which would change with each entry. Is Find Last possible for a my situation; using last number +1 for new field StockID? I would like to have the new stockID automatically generated After Updating the first field. TIA Phil

[June7]

You want autonumber StockPK field as primary key but still want to generate a unique StockID? I thought you wanted to avoid that hassle and that was the reason for the whole restructuring exercise?

[justphilip2003]

Thanks for replying to my posting. Yes and no. I want the StockID to automatically generate to the next unique number or find a way to increment the last entry. How else could I assign a unique number to it? I fear my ~15 year old mistakes are catching up with me! TIA Phil

[June7]

The autonumber field is a unique identifier. Autonumbers are normally not apparent to users, however, it can be formatted to display as a custom unique ID. Depends if you care about gaps in sequence. If the StockID is an accountable series, then autonumber field is less desirable. Incrementing a custom ID is tricky. It is a common topic in the forum. Does your unique ID include any alpha or punctuation? For example, I create a unique ID like 2013A-0211. Every ID is accounted for and the sequence starts over each year. I use VBA procedure to generate the ID. This is also my primary key. Except for one table, I don't use autonumber in this db. Discussed in http://forums.aspfree.com/microsoft-...ta-403208.html

[justphilip2003]

I reviewed your code and I failed to identify how it guarantees uniqueness. I can't imagine the task I would have in converting to a string stockID.
No StockID is defined as an integer. Perhaps my usage of "auto number" is misleading, I simply want the code to generate the next number for relating to the carving table when the carving is entered in that table. I trace the wood purchase, placing in stock and tracing carvings made from it. TIA Phil

[June7]

Take my word for it, my code generates the unique ID we require.

If the StockID is just a number, then incrementing is simply: DMax("StockID", "tablename") + 1

The real trick is figuring out what event to put the code in. Maybe in the form OnCurrent:

If IsNull(Me!StockID) Then Me!StockID = DMax("StockID", "tablename") + 1

However, if there could be multiple users at the same time, need to immediately save the record so the new StockID is committed to table and users do not generate the same value. The alternative is to generate the value after all data is entered and user is ready to commit record. In either case the possibility of duplicate generation is minimized, not eliminated. In the 6 years my code has been in place, I think we had duplication happen once. And since this is my primary key one user got an error message because of primary key violation and their data did not save. They just had to initiate another record and reenter data.

[June7]

Take my word for it, my code generates the unique ID we require.

If the StockID is just a number, then incrementing is simply: DMax("StockID", "tablename") + 1

The real trick is figuring out what event to put the code in. Maybe in the form OnCurrent:

If IsNull(Me!StockID) Then Me!StockID = DMax("StockID", "tablename") + 1

However, if there could be multiple users at the same time, need to immediately save the record so the new StockID is committed to table and users do not generate the same value. The alternative is to generate the value after all data is entered and user is ready to commit record. In either case the possibility of duplicate generation is minimized, not eliminated. In the 6 years my code has been in place, I think we had duplication happen once. And since this is my primary key one user got an error message because of primary key violation and their data did not save. They just had to initiate another record and reenter data.

[justphilip2003]

Thank you Jun7, it works perfectly. I had to put it in an afterUpdate() for the first field on the form because the form "On Current EP" generated the next number immediately after writing the record to the table which would create blank entries every time the user exits the form. Also, I will never place this code in a shared environment; however I do hope to use a compiler on it to make it portable to a none Access ready PC. My next challenge is to figure out why a scroll bar on a Form Note/memo field doesn't work. Thanks again Phil