Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Question Unanswered: How do I add one number to the next record

    I am working with a database that has a field which is a unique serial number in numerical order. I would like to be able to have the database automatically put the serial number in (which is always one number higher than the last. Example: 321111 next is 321112, 321113 ect.) These are the controlling document numbers and are always required in the database. Anyone know how I would be able to do this so that the number in the form opened is always the next number in line?

    Thanks much.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can use a RecordSet, Go to the Last Record, read your serial Number, then
    ADD.New, add one to your Serial Number then Update the record set.

  3. #3
    Join Date
    Dec 2003
    Location
    Sheffield, UK
    Posts
    17

    Use Autonumber

    Hi,

    turn the field type into an auto number. then when you add a new record it will automatically add 1 to the last autonumber field.

  4. #4
    Join Date
    Oct 2004
    Posts
    7
    Thanks for the information. Since I am new with the recordset thing, could you give me an idea where to start.

    As for the autonumber, unfortuntaly once data is entered into the field it will no longer allow changes (wish I would have thought of that before). Thanks all for the responses.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Suupose your table is called tab1

    Write the following in the On Form_Open Event

    PHP Code:
      Dim db as DataBasers As RecordSet
      Dim lastID 
    as Long
      Set db
    CurrentDB
      Set rs 
    db.Open("SELECT * FROM tab1 ORDER BY yourSerialNum")
        
    rs.MoveLast 
        lastID
    rs("yourSerialNum")
        
    lastID=lastID+1
        rs
    .AddNew
          rs
    .Edit
             rs
    ("yourSerialNum")=lastID
          rs
    .Update

       rs
    .close
       SET rs 
    nothingset dbnothing 
    I didn't test it, I just wrote it form the mind.
    Good luck

Posting Permissions

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