sequential number for records

brispark1

New member
Local time
Today, 08:35
Joined
Mar 10, 2017
Messages
1
Hi, can anyone help? I currently have a database which creates a unique sequential number for each new donor I receive. The current number pulls the year from date() and adds 001 etc. to give a number such as 2017-001 I need to increase the numbers to a sequence on 12 but if I change anything in the code below it stops working

The code runs on opening the form by being placed in the "on Current" function I would be quite happy if I could just pad out the space between year and number with 0's eg

201700000123

can anyone help?

Code:
Dim x As Long, ss As String
If Me.NewRecord Then
  ss = Nz(DMax("DonorNo", "Donor"), "")
  If ss = "" Then
    x = 0
  Else
    x = CLng(Left(ss, 4))
  End If
  If x <> Year(Date) Then
    Me.DonorNo = Year(Date) & "-001"
  Else
    x = InStr(ss, "-") + 1
    ss = Mid(ss, x, Len(ss))
    x = CLng(ss) + 1
    Me.DonorNo = Year(Date) & "-" & Trim(Right(Str(10 ^ 3 + x), 3))
  End If
  End If
 
Why do you do that?
Database works well with 1 fact in 1 field.
If you have to put some field values together to simplify something for someone, you can conctenate the fields.
Why does a unique identifier have to be sequential?

Just curious.

What's wrong with
Code:
DonorId    231467
DonorJoinDate  27Mar2016
 and concatenate 23146727Mar2016 when needed
 
You can use the Format function to pad a number with zeros. For example with the function

Code:
Function Pad(str)

Pad = Format(str, "00000000")

End Function


Pad(123) would produce 00000123
 
I agree with jdraw. Constructing a human readable frankencode like this is a headache to manufacture, and it adds no value to your system.
My 2c...
 

Users who are viewing this thread

Back
Top Bottom