Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    12

    Unanswered: auto increment integer field on new record

    Hi All,
    I'm new to Access and foolishly volunteered to create a Access DB for my camera repair store's invoicing system I'm stuck on what I would think would be a fairly simple process and I was hoping to get some help from the peeps on this forum.

    Basically, I've got an invoice table and I want to auto-update the invoice number (starting from 55208) every time a new record is created. I've come up with this code so far:

    Dim intTestInt As Long

    intTestInt = Me.Label10.Caption
    intTestInt = intTestInt + 1
    Me.Label10.Caption = intTestInt

    which works fine but I don't know how to tie it in properly to my database. Thanks for helping a noob!

    Nick

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just use the autonumber data type - it will do this for you. There are lots of long winded and complicated reasons why rolling your own is not a good idea but I won't bore you with them.

  3. #3
    Join Date
    Apr 2010
    Posts
    12
    Hi Pootle Flump,
    Thanks! I'll give that a try

    Nick

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would tend to agree but want to point out the caveat that autonumbers can skip numbers under certain conditions (for instance when a new record is started but aborted). Often times invoice systems can't use autonumber because of this (audit issues). If that's not a problem, I'd certainly stick with the autonumber. If not, you'll need to go another way.
    Paul

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by intergnat1 View Post
    Hi All,
    I'm new to Access and foolishly volunteered to create a Access DB for my camera repair store's invoicing system I'm stuck on what I would think would be a fairly simple process and I was hoping to get some help from the peeps on this forum.

    Basically, I've got an invoice table and I want to auto-update the invoice number (starting from 55208) every time a new record is created. I've come up with this code so far:

    Dim intTestInt As Long

    intTestInt = Me.Label10.Caption
    intTestInt = intTestInt + 1
    Me.Label10.Caption = intTestInt

    which works fine but I don't know how to tie it in properly to my database. Thanks for helping a noob!

    Nick
    If the above code works and you just want to write the value to field in the table, couldn't you simply add this code to the end of your code:

    me!MyFieldName = intTestInt

    Or using vba...
    Public Function writeInvoice(InvNum as variant, RecID as variant) (where RecID is the unique identifier for the specific record.)
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where MyRecIDField = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs!InvoiceField = InvNum
    rs.update
    rs.close
    set rs = nothing
    end function

    and then you'd call the function as such:
    call writeInvoice(intTestInt, me!RecordID)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2010
    Posts
    12
    pbaldy - I've heard the point you bring up before and that was why I steered clear of autonumber in the first place. Absolute invoice number linearity is not essential but I would much prefer it to having sequence holes.

    pkstormy - I would really prefer to go the route you specified with code but I think I need a bit more hand holding if you don't mind. Where do i declare Public functions in the code? Under (General)? Also, where would I call the function if I wanted the InvoiceID field to autofill when I create a new record?
    Last edited by intergnat1; 04-09-10 at 16:46.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you compute it from the Invoice table:
    Code:
    Dim NewInvoiceNumber as Long
    NewInvoiceNumber = DMax("InvoiceNumber", "Invoice") + 1
    Have a nice day!

  8. #8
    Join Date
    Apr 2010
    Posts
    12
    Sinndho - The DMax function worked GREAT!!!! Exactly what I needed! Thank you all very much for all of your help!!

    Nick

Posting Permissions

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