Changing primary key

[Tina Brev]

Help anybody!

I have 2 forms: frmSource (data came from 5 tables) and frmDestination.

Question: frmDestination is setup to have an ID field (is that necessary?)

Is this possible to replace ID field to become Assembly Partnumber instead? For every
record collected from frmSource will be treated as 1 Assembly.

Instead of Autonumber make it a text data type and define a format property or input mask?

Thank you in advance for suggestions or comments

 

[Chris RR]

How is your current database laid out? By this I mean the structure of the tables and their relationships. Forms themselves don't have keys or autonumbers; tables do. If you set up a new primary key, you may be looking at a complete database redesign.

Where does the Assembly Partnumber come from? Would the user be entering it, or would you create it in the form? If you cannot absolutely, positively guarantee that it is ever and always unique (and this needs to be written in blood), then don't try to use it as a key.

It's also not a good idea to try to come up with a "meaningful" key. It sounds like an appealing idea, but building intelligence into a part number has real problems. No matter what scheme you use, sooner or later it will be too limited to work, you'll end up adding to the part number, or kludging it up somehow. I have personally written pages and pages of VBA code to format a single, poorly designed ID field (which we finally ditched last year...RIP!)

That being said, you could always change the label on your form, so that the "ID field" reads "Assbly Partno" This might be all that your users want. Or you can simple not display the autonumber field; just let Access handle that all behind the scenes.

 

[Tina Brev]

Hello Chris

I am going to try to answer your question as close as I possibly can. I have a very complicated database. Believe it or not I have no relationship set up. You are absolutely right. This form/table was generated by Access when I passed the data into frmDestination. The intent of the partnumber is to assign one partnumber to different combinations of tools which each has its own partnumber.

Think of it as a Bill of Materials where a user enters a top assembly partnumber and gives you the parts needed to build that Assembly.

What would be the best approach? Would it be less headache to just add another field in the Destination table?

Thanks for the feedback

Ps: gotta cheers for the Vikes this sunday