Results 1 to 4 of 4
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    ID autonumber or incremental number in VBA

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  3. #3
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    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.
    Code:
    If IsNull(txtID) Or txtID = 0 Then txtID = Nz(DMax("[P_ID]", "tblParts"), 0) + 1 End IF
    Thanks for your help!
    Tom

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I could add another field ID and make it PK with autonumber if that wild be a better practice.
    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.

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-17-2017, 07:20 PM
  2. Replies: 28
    Last Post: 08-08-2017, 03:27 PM
  3. Replies: 3
    Last Post: 06-08-2017, 08:03 PM
  4. Replies: 11
    Last Post: 11-11-2014, 06:04 PM
  5. Incremental Number in a Query
    By jmauldin01 in forum Access
    Replies: 5
    Last Post: 01-01-2014, 01:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums