Would like to know what you would use. ID (PK) (autonumber) or ID (PK) (number) using VBA to increment. I was using autonumber, but changed to VBA. Just looking for pro's and con's.
Thanks
Tom
Would like to know what you would use. ID (PK) (autonumber) or ID (PK) (number) using VBA to increment. I was using autonumber, but changed to VBA. Just looking for pro's and con's.
Thanks
Tom
Autonumber that's also a PK (or FK value elsewhere)? Not at all - no ID's. Rule #1 about PK and FK values is that they should never be meaningful data, which is what you're suggesting.
This is a more involved question than what it likely appears to you at present. Several factors are involved, such as unique or not, consecutive or not, and what to do about concurrent users. A common approach with consecutive values is Max+1 or DMax+1 but as I say, concurrent user situation requires planning as to when to generate a value.
See
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I went over your links and understand the basics. I'm using the code below to generate an P_ID number. Index with no duplicates, P-ID is also the PK. I could add another field ID and make it PK with autonumber if that wild be a better practice. I'm using P_ID as sequencer and is shown on the form in txtID.
Thanks for your help!Code:
If IsNull(txtID) Or txtID = 0 Then txtID = Nz(DMax("[P_ID]", "tblParts"), 0) + 1 End IF
Tom
I believe that is what is meant by not making pk fields meaningful data. Suppose 2 years from now something about that meaningful data changes. You the must ensure that you update every record and field everywhere it was propagated. If the changed value had an autonumber PK of 55 and 55 was the fk value everywhere else, what do you need to do if the meaningful data changes? Probably nothing beyond updating it in the parent table.I could add another field ID and make it PK with autonumber if that wild be a better practice.
Your expression should be OK as long as a user cannot edit the control contents. The result may or may not be, depending on how you're handling concurrent users assuming there are any.