Results 1 to 5 of 5
  1. #1
    1337noob is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    4

    Question autonumber generation based on filed1 to generate SKU ?

    Ok so here is what I am trying to accomplish:
    I want to input a number value in [field1], then i want [field2] to generate an auto number BUT i want it to increase based on [field1] THEN combine them into [field3] creating a unique SKU for every item I have.


    EXAMPLE1: I will type in [field1]: "001234". [field2]will enter "000001" because it is the first occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00001"
    EXAMPLE2: I will type in [field1]: "002468". [field2]will enter "000001" because it is the first occurrence of "002468" in [field1]. [Field3] will then have a value of "002468-00001"
    EXAMPLE3: I will type in [field1]: "001234". [field2]will enter "000002" because it is the second occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00002"

    How do i get this to happen?

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    First off, dont use field3, you don't need it. Since it is derived from Field1 and field2, you can always generate it in a query or report as required.

    What you want to do is not really an "autonumber"; that term is best kept for referring to genuine autonumber fields. You will need to use VBA to determine the value for field2, and the best place for that code is in the AfterUpdate event of Field1, somthing like this:

    me!field2 = format(dcount("*", "TableName", "Field1 = '" & me!field1 & "'") + 1,"00000")

    This code counts how many occurances of the specified value for field1 are already in the table, adds 1 to it, and then formats the result as a string with leading zeros.

    I am assuming all your data is text type (string), so that the leading zeros are retained, and so that the DCount criteria works.

    This technique will work reliably only if you know you will NEVER delete records, because doing so could result in duplicate field1-field2 combinations.

    HTH

    John

  3. #3
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Quote Originally Posted by John_G View Post
    First off, dont use field3, you don't need it. Since it is derived from Field1 and field2, you can always generate it in a query or report as required.
    John, as we have not heard back from Elite Newb yet, what if he really does need an autonumber to use as a primary key? Your line wouldn't work because you could have duplicates. So is there a way to use your idea if the OP really does want a 3rd field that will be used as an autonumbered primary key?

    I bring this up as I would think the OP 'would' need that 3rd field to hold the primary key, if that was the goal. Since he is mentioning a generated SKU, you wouldn't want a duplicate, which is why I think he really does want a 3rd field, or at least; a field that now holds his autonumbered SKU.

    Just curious on this myself. Something has to check for whether or not the SKU exists, or way to not allow a duplicate to enter. Am I missing something?

    Tim

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    HME -

    The OP's primary key is the combination of Field1 + field2. There is nothing wrong with that (IMO), though some frown on it. In his case, an autonumber field would be redundant and unnecessary, and since he wants the value in [field2] to be based on what was entered in [field1], it is not an "autonumber" (in the MS Access sense) anyway.

    The reason I say there is no need for [Field3] is that [Field3] would be a calculated field ( =[Field1] & "-" & [field2] ). Generally speaking, calculated fields should not be stored as part of the data in a table, because it's too easy to change the data without changing the calculated field as well. There obviously will be exceptions to this (I do it sometimes).

    The code I showed will prevent duplication provided a) it is used for all entries, b) 1 is always the required increment, and c) no records will be deleted.

    An extra (and maybe better) precaution is the set the PK of the table to [field1] and [field2].

    John

  5. #5
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Quote Originally Posted by John_G View Post
    An extra (and maybe better) precaution is the set the PK of the table to [field1] and [field2].

    John
    Thank you John for the explanation. Now we can wait for the OP to see if this will work for his situation.

    Tim

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

Similar Threads

  1. Replies: 3
    Last Post: 06-09-2012, 09:49 PM
  2. Replies: 1
    Last Post: 06-09-2012, 05:13 PM
  3. Replies: 1
    Last Post: 03-01-2012, 04:35 AM
  4. Replies: 9
    Last Post: 08-30-2011, 04:08 PM
  5. Custom Autonumber based on lookup
    By luce725 in forum Access
    Replies: 7
    Last Post: 08-26-2011, 01:49 PM

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