Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with AutoIncrement (Access 97 )

    Hi!

    I need help figuring out an expression in Access. I need to auto increment a number within a set of numbers during data entry.

    For example:
    Document_Code field - entries to pick from combo box are 1001-1005
    Number field - should auto increment at change in Document_Code

    So -
    Document_Code 1001 Number 001
    Document_Coed 1001 Number 002
    Document_Code 1003 Number 001
    Document_Code 1001 Number 003

    Plus the Number field should be limited to 999

    Thanks!
    JC

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with AutoIncrement (Access 97 )

    Put these code in a module. Save the module with any name but GetNextNumber.
    <pre>Function GetNextNumber(Document As Integer) As Integer
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT MAX([Number]) as LastNumber FROM tblDocuments WHERE Document_code = " & Document
    Set rst = db.OpenRecordset(strSQL)
    If IsNull(rst!lastnumber) Then
    GetNextNumber = 1
    Else
    rst.MoveFirst
    GetNextNumber = rst!lastnumber + 1
    End If
    If GetNextNumber = 1000 Then
    MsgBox "You have already 999 numbers for document " & Document
    GetNextNumber = 0
    End If
    Set rst = Nothing
    Set db = Nothing
    End Function</pre>

    In the line strSQL = ... replace the name of the fields and the table with yours.
    In the After Update event of the control with the document code enter:
    Me!NameOfControlWithNumber = GetNextNumber(Me!NameOfControlWithDocument_Code)
    You will have to write some code to take action if the number reach 999. I put a messagebox and return 0.
    This code assume that the two fields are integers.
    Francois

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with AutoIncrement (Access 97 )

    HI!

    Thanks Francois!

    But I'm not getting to work. Followed your instructions... typoed on the after update and got an error message. Fixed the typo and now... nothing. No error... but no autonumber either. It's most strange. What might I be missing?

    JC

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with AutoIncrement (Access 97 )

    Are you sure that you have put the code in the after update event of the textbox contain the Document_Code ?
    If so, can you tell us what code you use ? From Private Sub .... till End Sub
    Francois

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with AutoIncrement (Access 97 )

    HI!

    Just me being an idiot...

    The first time I put the after update event I had selected the Number field in error... saw that and put it in the Document_code... so guess what I did...

    Neglected to take it out of the Number field

    Francois ... you are a genius... thanks so much for the code!

    JC<img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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