Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    24

    Unanswered: auto numbering but not for every record

    hi

    i have a table with persons in it. Some of them are members, others are not, unfortunately they are in the same table
    So some of them have membership numbers, and others don't.
    When I enter a new record in a form, is it possible to have the form automatically create the new membership number if I indicate that the new person is a member? The new membership number should just follow up the last...

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could run a select query to return the MAX value of a particular column, and then immediately use that value as to update the new membersip number.

    your query should run in a function or macro with a lock to stop other users reading the table whilst you retrieve AND update the value.

    HTH

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The easy solution would be to create another table with membership number and whatever you're using for individuals as a foriegn key.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by Teddy
    The easy solution would be to create another table with membership number and whatever you're using for individuals as a foriegn key.
    Exactly. This way, you can let Access handle the "No duplicate Keys" problems and you don't need to lock the records

    .
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    concur, but I'm expecting funkmob, or his teacher, to get upset about non contiguous membership numbers

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ahhhh, well one could manually make entries into this table I suppose. Of course that brings us back to the original suggestion.

    If you want extra credit, I would go with a nonsyncronous semi-analogous variable array approach. Granted it's a little advanced, but it scored me big bonus points with my instructor! A lot of people don't think to incorporate the nonsynchronous aspect of randomization, but I've found it greatly improves overall quantum performance...

    Try something like this:

    Select Max(yourField) + (Int((.6 * Rnd) + 1.4) * (3.14 ^ 0)) As YourNextKey
    FROM yourTable
    WHERE Max(yourField) <> -3.14
    Last edited by Teddy; 11-21-05 at 12:29.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Wow Teddy, we are not worthy...

    That looks a very intersting way of generating the next memebrship number. I'm particularly impressed with the stochastic use of the rnd function, I would never of thought of that, but then pros like you must know these things of Pat(P)

Posting Permissions

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