Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: add 1 to value of field in previous record

    Hello,

    I am looking for a little help with a form for a database I'm working on. On this form, I have the following fields:

    Batch Number
    Ship Via
    Date / Time

    On a Subform, I have the following:

    Invoice Number

    The forms are linked in such a way that one can type different values into the Invoice Number field and each record takes the entries from the main form too.

    What I would like to accomplish is the Batch Number field auto-incrementing when I press a button somewhere on the page. Basically, when that button is pressed, or when the form is first opened (either way or both), I'd like it to look at the most recent record and automatically take that numerical value and add 1 to it.

    Is there a way to do this?

    Thanks for any insight you can offer. Be well!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You really need to do this just prior to saving your new Main Form Record, rather than by using a Command Button or when first opening a Form. This cuts down on the chance of two users getting the same Batch Number.

    Here's a typical hack to generate an Auto-incrementing number for your Batch Number field.

    If your Batch Number that is defined in the table as a Text Datatype:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then
       If RecordsetClone.RecordCount = 0 Then
        Me.[Batch Number] = "1"
      Else
        Me.[Batch Number] = DMax("val([Batch Number])", "YourTableName") + 1
      End If
    End If
    
    End Sub


    Here's the same code for a Batch Number defined as Numerical:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then
      If RecordsetClone.RecordCount = 0 Then
        Me.[Batch Number] = 1
      Else
        Me.[Batch Number] = DMax("[Batch Number]", "YourTableName") + 1
      End If
    End If
    
    End Sub

    Assuming that your field is actually name Batch Number, including the space, you'll only need to replace YourTableName, in the code, with the actual name of the Table your Batch Number Field resides in.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2013
    Posts
    2
    Thank you very much for the assistance, friend.

    You're right, the possibility of two users getting the same batch number is an issue I hadn't thought of yet. Thank you!

Posting Permissions

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