Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code to increment ID# (2000)

    I would like to add code to enrollment form to automatically add on to ID number (primary key) each time a new member enrolls. Any hints?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: code to increment ID# (2000)

    >>I would like to add code to enrollment form to automatically add on to ID number (primary key) each time a new member enrolls. Any hints? <<

    You could make the ID field an autonumber. That would automatically generate a new number.

    Alternatively, you could just make it a long integer. Create a control (let's call it txtID) on your form that is bound to the ID number. For this control, set Enabled=No and Locked=Yes (this will keep anyone from touching it). As the last lines in your form's BeforeUpdate event, add this:<pre> If Me.NewRecord = True Then
    Me.txtID = Nz(DMax("ID","tblMembers") ,0)+ 1
    end if</pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code to increment ID# (2000)

    I assume you're not using autonumber which is by far the easiest way of doing this. I'm also assuming this is a single user database.
    You need to create a variable, for example varNextID. Set the varNextID to find the biggest id and add 1. It would be something like "varNextID" = (Dmax("[EnrolledID]","Students")) =1. Somewhere you will have to create an event procedure to set your enrollment id to varNextID. Where you do it is up to you. You could set it to an afterupdate procedure when you type in the surname, or on the form's on current event procedure, for example.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: code to increment ID# (2000)

    I would go with Ginger's suggestion. Although the autonumber is the easiest it can cause problems using it for a real world number, particularly if you compact the database. It is better to generate your own number and then you have control over it.

    One option is to have a table called, say, tblMyVars with three fields: strVarName, strStringValue and lngLongValue You then keep all your data which is updatable from session to session in this table. One entry in the table would be NextStudentNumber, "", 1

    Each time you create a new student you pull in the value from tblMyVars, use that value, increment it by 1 and write it back to the table.

    This avoids compaction problems and, on a large database should run quicker than dmax.
    David Grugeon
    Brisbane Australia

Posting Permissions

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