Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Unanswered: Automatically incrementing/generating a Text Field

    Hello everybody,

    I have just discovered this forum, and this is my first part here.

    I am working on a database that should automatically generate invoice numbers, which will need a text field, as both numbers and characters are involved. I'm not totally comfortable using Autonumber for this, because of the risk of gaps, or numbers being skipped.

    I am attempting to automatically generate invoice numbers following a format of G-0000, with G-0001 being the first number. Is there a possible way that I can automatically generate invoice numbers that follow this format, without having to use autonumber?

    Thank you for anyone who can help, and I am looking forward to being a part of the forums!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Yes it canbe done
    you need to write a function that returns the next number. Theres lots of examples here and elsewhere on the web.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The man on two wheels is right, there are lots of examples both here and elsewhere, for doing this, but exactly how you do it depends on your exact needs! Is the alpha prefix always going to be G- or is that subject to change? Is the numeric part never going to exceed 9999? As usual, the secret to remaining sane is to plan for your maximum possible needs, from the beginning.

    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

  4. #4
    Join Date
    Apr 2014
    Posts
    4
    The alpha prefix is always going to be "G-", and tomorrow I'm going to learn for sure if there is a limit to how long/how many digits are expected for the actual number.

    I have an idea in my head of how to possible do this, but I wonder if perhaps I am making this more convoluted then it has to be. I thought about the possibility of storing the prefix, the number of zero's, and the number of the end as separate strings and adding them together, with an if/else to evaluated how many zero's there should be based on how large, or how many digits, the number is.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then
    
      If DCount("*", "YourTableOrQueryName") < 1 Then
        Me.IDNumber = "G-0001"
      Else
        Me.IDNumber = "G-" & Format(DMax("Right([IDNumber],4)", "YourTableOrQueryName") + 1, "0000")
      End If
    
    End If
    
    End Sub

    You'll have to carefully replace IDNumber and YourTableOrQueryName with the actual names of your Field and the Table or Query your Form is based on.

    If you need more than 4 digits, adjust the

    "G-0001"

    to reflect that, and the

    "0000"

    in the DMax formula with a Zero for each digit that you require; i.e. if you need a possible max of 999999 then use "000000"

    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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im prettyu certain DMAX will return the highest value on string columns as well as numeric columns. so a straight forward dmax may well be as useful

    although to be honest if it was me I['d jsut stroe a number, and prefix it with the G- when presented., and chop it off when captured.

    If the G never chanegs then its just redundant.
    I like the DCount approach, excpet that there is a risk of problems if someone does delete a row by accident or malicously. I feel (cant prove, but feel) DMAX would be more robust.

    if this is being used in a multi user environemnt you proabbly ought to take into account the risk that more than one person could attempt to write a new row at the same time. there's various ways of doign that, through record locking or simialr
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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