Creating Primary Key 2016-001, 2017-001 etc

[grubnz]

Hi There,

I hope I have put this in the right place.

I had tried doing this from another post on here suggested

txtUniqueNumber = Number & Format(Date, "ddmmyy") & ID

What I did was
txtUniqueNumber = Format(Date, "yyyy")&"-"&ID

Didn't quite do what I wanted.

What I am trying to achieve

(Date + Number) and then Reset Next Year e.g. below to give its uniqueness

Year 2016
2016-001
2016-002
2016-003 etc

Year 2017
2017-001
2017-002
2017-003
2017-004 etc

Would you put this in the default field of the table and create a new field?

Thanking you for your time and assistance

Annie

 

[plog]

Actually, this is more aptly called a compound key (https://en.wikipedia.org/wiki/Compound_key). That's because this data shouldn't be stored in 1 field, but 2. You have 2 distinct pieces of data, therefore you store them distinctly--One for the year, one for the ID.

The real key is generating the ID per your specifications. To do that, search this forum for 'incrementing ID'. It usually involves making a custom function that looks into your table, gets the largest ID (using DMax) and then adding 1 to it to determine the new records value.

 

[arnelgp]

try using function:

Public Function fnSequence() As String
    Dim s As String
    Dim l As Integer
    s = Nz(DMax("field1", "table1"), "")
    If s = "" Then
        s = Year(Date) & "-000"
    
    End If
    If Left(s, 4) <> Year(Date) & "" Then
        s = Year(Date) & "-000"
    End If
    l = Val(Replace(s, "-", ""))
    l = l + 1
    s = Format(l, "0000-000")
    fnSequence = s

End Function

 

[grubnz]

Hi Plog and Arnelgp,

Thank you very much for your replies.

I read the information on the compound keys.

Arnelgp,

I have tried the code below sorry for my ignorance. Is this where I should be putting it in the database. As below?

I created a form and placed an unbound field on the form and then placed the code under before update. As below.

Public Function fnSequence_BeforeUpdate() As String

Dim s As String
Dim l As Integer
s = Nz(DMax("PKID", "tblTestingMultiAutonumber"), "")
If s = "" Then
s = Year(Date) & "-000"

End If
If Left(s, 4) <> Year(Date) & "" Then
s = Year(Date) & "-000"
End If
l = Val(Replace(s, "-", ""))
l = l + 1
s = Format(l, "0000-000")
fnSequence = s

End Function

Thanks for your assistance.

Have a wonderful day.

Regards,

Annie

 

[arnelgp]

put the code in a module.

you should create a new field (short text) in your table. let say your table is named Table1 and the field name is Sequence.


on your fom's Before_Insert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Sequence = fnSequence()
End Sub


you should replace the "Sequence" with the correct field in in your table. and "table1" with correct table name (see the code fnSequence).

 

[grubnz]

Hi Arnelgp,

Hope all is well.

Thank you for your assistance yesterday greatly appreciated.
I did what you mentioned and have been trying work out the Run-time error ‘6’: Overflow issue today with unfortunately limited success as my knowledge of VB isn't very strong at the moment.

I have attached a document with the error msg and I had tried commenting out various lines to see what would happen.

Thank you

All the best,

Regards,

Annie

 

[arnelgp]

on our function's variable declaration change this:

Dim l As Integer

to:

Dim l As Long

 

[grubnz]

Hi Arnelgp,

I have got it to work With your assistance and knowledge.

Thank you for your patience.

Have a lovely week.

All the best,

Regards,

Annie

 

[arnelgp]

your welcome!