Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generating a Unique and Stable ID (Access 2000)

    I'm creating a form with a subform in it.
    The subform will contain a variable number of records, each with its own ID generated by code.
    The problem is, what event should generate that code? If it was in a form, it would be the Before Insert event. If I use that here, only the first record gets its' ID generated.
    If I tie the code to one of the controls changing, the ID will update if the record changes at all, and I don't want that. Once given the ID should be permanent.

    If I do tie it to a control, should I just use the OldValue property - to keep the value?

    Or is there a better solution?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Unique and Stable ID (Access 2000)

    BeforeInsert happens when you type the first character into a new record. Subforms are also forms, so isn't that what you need? I am not familiar with what you have discussed in other threads and Hans isn't available at the moment, so give me a brief overview, please. It sounds like you are creating not one but multiple records at once in the subform, perhaps through code. If you explain a bit more, I'll try to help.

    If you use a control event, just test to see if the ID is null or whatever the appropriate default value is before you assign anything to it. That will keep it from changing once it's been set.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    Well, if I gave too much information out - then your psychic powers wouldn't be necessary <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.
    Yes, I'm creating multiple records in the subform.
    Think of an invoice with multiple purchases on it - each record in the subform would be equivalent to an item on the invoice. I'm displaying the subform as continuous forms. My current solution (after posting the above question), is to have the user click a button at the beginning of each item to generate the ID. It tests to see if an ID has been assigned, if not it generates an ID.
    I really had a brain freeze before I could come up with that, and I'm still wondering if that's the best method of doing things.
    Joined to this question, is the realization that I don't really understand how to reference the individual records within the subformyes, by the ID if I have it).
    I feel that this is quite vague and annoying, so I'll stop here.
    Thanks Charlotte.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Unique and Stable ID (Access 2000)

    <hr>I feel that this is quite vague and annoying, so I'll stop here.<hr>
    Does that mean you're forgetting about it, or that you decline to explain further?? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    OK, you're creating a group of records in code, but I'm still not clear on why you aren't creating their IDs at the same time. The usual way to handle this kind of stuff is with a table for the invoice and a table for the invoice details. The invoice has it's own unique ID, and that ID is also inserted as a foreign key into each of the invoice details records. That makes the IDs for the individual items largely irrelevant as long as they're unique when combined with the Invoice ID, because the link to the invoice is through the foreign key. I take it that this isn't what you're doing, or have I misunderstood? Are the IDs for the details items something like a line item number? If so, they don't need to be unique within the table, only within the invoice ID used as a foreign key, and you should be able to generate them pretty simply when you create the records.

    I don't mean to put you through an inquisition, but Hans has been fielding your questions, so I haven't felt any need to do more than glance at those threads. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    It isn't an inquisition, don't worry.
    The usual way to handle this kind of stuff is with a table for the invoice and a table for the invoice details.The invoice has it's own unique ID, and that ID is also inserted as a foreign key into each of the invoice details records.
    Yes, this is how I've created the tables.
    But in addition each line item will have its' own unique ID, unique, not just to the invoice - but also to the table. This ID is used for tracking purposes. It probably won't surprise you to hear that I'm replicating a paper-based system into a DB.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Unique and Stable ID (Access 2000)

    Oww!! <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15> Those kind of projects always cause headaches.

    So is there some reason you didn't just use an autonumber for the unique ID? That would certainly make your life simpler. Or was there a precedent for creating some sort of special ID that must be sequential, a certain number of characters, and follow a prescribed pattern? (I hate that kind of requirement, incidentally! I generally invest a good deal of energy into persuading them to do us all a favor and settle for unique and meaningless.)

    This isn't hard in a single user situation, but if you have multiple users, it gets more complicated. Is the database split into front and back end, and where are the respective parts located? Are you storing the last assigned unique item number in a table for that purpose, or are you just looking at the table of detail items and generating the next new number when you create a new record?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    Ultimately, I plan on splitting the front and back end. There is a separate table to hold the last sequential number. (On Han's advice - that was golden) Yes the ID could only be a limited number of digits in length. The ID is based on the year, month, product abbreviation and a sequential number.
    Fortunately, there will only be a small number of users, and in fact a small number of transactions. This database is driven by the need for long term tracking of these items.
    <font color=blue>I generally invest a good deal of energy into persuading them to do us all a favor and settle for unique and meaningless</font color=blue>
    Please share your arguments, I feel I will have need of them in the future.... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Unique and Stable ID (Access 2000)

    <hr>Please share your arguments, I feel I will have need of them in the future....<hr>
    It all boils down to economics. I can spend a LOT of time (which translates into <img src=/S/money.gif border=0 alt=money width=17 height=15>) building something that slavishly replicates a paper system, which had to be that way because of the limitations of paper systems and human employees, or I can save time (and money) by building them a system that will make the computer do the hard work and spit out a report formatted for human use. That saves the time of other employees because they don't have to visually audit the information, the computer points the problems out to them.

    The formatted identifiers are set up that way so a person can spot breaks in the sequence or can extract information about the record from the identifier. That is a violation of basic relational design because you are storing data or metadata in a key, and that information generally must in some way duplicate information within the record itself. A meaningless value (to humans) is a perfectly acceptable value to a computer because the only purpose of that key is to identify the record--it contains NO OTHER INFORMATION! Let the computer do the work of telling you if there is a number missing. It's silly to pay a human to do something a stupid machine can do faster and cheaper. Plus, the computer can look at the data from a wide variety of angles and all sorts of cross validations can be built in to spot missing transactions or missing items. I built a system like that once, where it correlated any particular year's information with patterns in a specified number of prior years according to the defined business rules and spit out a report that flagged the items that needed investigation and judgment by a person.

    If continuity is needed, a mapping can be developed to "explain" the old numbers versus the new numbers, but I would highly recommend that they drop the insistence on meaningful IDs, since they aren't really meaningful to the machine, and it's the machine that has to keep track of them and use them appropriately.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    Thank you, thank you, thank you! I'm bookmarking your post. Next time I'll say "Charlotte says ..."

    The identifier will actually be translated into a label which will be stuck on the item; and the items are very small - this is the major reason for wanting a short unique ID number. Fortunately, there will never be a large volume of items. Anyhow, in a scenario like this where each line item has to be identified uniquely. Would there be a better way of doing things? Using a randomly generated number and creating the ID through code seems redundant.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Unique and Stable ID (Access 2000)

    I would use the invoice ID and a sequential (or random) line item number unique to the Invoice ID (Like XXXXXX-1, XXXXXY-1, etc.). If there will never be more than a small number of items, you could use an integer or even a byte for the line item number. That gives you the immediate tie-in to the invoice and you keep the identifier unique within the group. The combination of InvoiceID and line item number is a unique key for that line item. There is nothing wrong or illegal about a multiple field key, but links to other tables are faster and easier on a unique key like an autonumber, so I often use both, one for creating fast joins in queries and one for other purposes like grouping the items by invoice ID in some sequence. There is no law against having more than one unique key in a table, whether multiple field or single field keys. In fact, that is the whole concept of "candidate" keys, or keys that *could* uniquely identify a record. The one you select as the primary key is generally used in relationships and joins, but it isn't necessarily the only one.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    Any chance you could point me to the thread with Hans,

    Cheers

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    Hi Darsha,

    It was not a single thread, ThreeCats started several threads in August in which I was involved. If you click the Poster column heading in the list of posts in the Access forum, the threads will be sorted by Poster (in descending alphabetic order). Move forward a few pages and you'll find all threads started by ThreeCats grouped together.

    Was there something specific you wanted to know?

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    Hi Hans

    Clicking Poster in Forum takes me to Profile For Support4John, and view all this users post button is disabled,.

    Possibly because of the forum search is disabled.

    Or am I doing something wrong?

    John

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    forget it Hans, I got it

    John

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Generating a Unique and Stable ID (Access 2000)

    You are clicking on the Poster's name, not the word Poster - it's in the top bar as a header in the forum view.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •