Automatically generate reference Number from fields on form

[Bone2]

I am developing a database for inspection activities that are carried out by my department. Each inspection activity is given a unique reference number called "inspection Code". The Inspection Code is generated by combining a unique code assigned to each entity inspected (Entity Code), a unique code assigned to the purpose of the inspection (Purpose Code), a serial number that starts from 01 at the beginning of the year, the Month number and the year number. Example code is given as KCMI011122 (KC is entity code, MI is purpose code, 01 is the serial number, 11 is the month number and 22 is the year number). The code for each entity begins with 01 at the start of each year and reset to 01 at the end of the year.
I want to generate this code automatically.

I have been thinking about a way out for days but no luck. I will be happy if someone can help me out.
I have attached a sample database.

 

[Minty]

If you have an inspection date field you can easily create 99% of that reference number without storing them and simply display them.
MyInspectionCode = [EntityCode] & [PurposeCode] & Format((InspectionDate],"YYmm)

However out of interest, what purpose does this actually serve other than mimicking an existing paper-based filing system?

You can count the number of inspections per year easily simply using a query.

 

[The_Doc_Man]

I'm with Minty on this one. There is no need to actually store such a code and in fact there can be reasons why you shouldn't. Don't confuse something that is part of a computation with something that is simply a displayed number. Both forms and queries can concatenate the fields you wanted trivially so that you never have to store the combination.

There is another, rather more obscure, technical reason to not do this. The ID field you describe duplicates data already in that record. You should never store duplicated information in a static field. It is wasteful of space and highly inefficient. The uniquely generated part of the number can certainly be stored - but those other "markers" have no place as duplicates.

 

[Bone2]

If you have an inspection date field you can easily create 99% of that reference number without storing them and simply display them.
MyInspectionCode = [EntityCode] & [PurposeCode] & Format((InspectionDate],"YYmm)

However out of interest, what purpose does this actually serve other than mimicking an existing paper-based filing system?

You can count the number of inspections per year easily simply using a query.

Thank you for the response. The inspection code is generated when a request for inspection is received and that code is finally used on the inspection report that is generated after the inspection to uniquely identify the inspection report.

Could you show me how to add the serial number portion of the code?

 

[Bone2]

I'm with Minty on this one. There is no need to actually store such a code and in fact there can be reasons why you shouldn't. Don't confuse something that is part of a computation with something that is simply a displayed number. Both forms and queries can concatenate the fields you wanted trivially so that you never have to store the combination.

There is another, rather more obscure, technical reason to not do this. The ID field you describe duplicates data already in that record. You should never store duplicated information in a static field. It is wasteful of space and highly inefficient. The uniquely generated part of the number can certainly be stored - but those other "markers" have no place as duplicates.

Thank you for your response. I agree that it can be a duplication. I will just display the code then.
I will be grateful if you could show me how to generate code

 

[Minty]

Does it matter if the serial number doesn't reset every year as long as it is unique?
Do you have a unique Primary key ID for the inspection record/request (hint: you should)?
How many inspections do you carry out per year and how many on the same piece of equipment?

Sequential numbering is actually a pain in a database as you have to deal with what happens if you delete a record or create one by mistake? What would the business rules dictate?

 

[Bone2]

Yes, it's important that the serial number resets at the end of the year.
There is a primary key for each inspection record.
The number of inspections per year is not fixed. It varies.
The incremental in the serial number must always be in reference to the previous date.

 

[The_Doc_Man]

To generate a mixed-format identifying code, you have to make everything in the same data type, which in your case must be text (because you have codes that derive from text fields).

The VBA to generate something of this type MIGHT resemble

MoTxt = Format( Now, "mm" )
YrTxt = Format( Now, "yyyy")
YrCount = DCount( "*", "mytable", "[EntityCode] = '" & Me.[EntityCode] & "' AND [YrTxt] = '" & Me.[YrTxt] & "'" ) + 1
IDString = [EntityCode] & [PurposeCode] & MoTxt & YrTxt & CStr(YrCount)

Since I can't see your setup I don't know exactly where things would go so I made up some names to show the general approach. This would generate your ID string for you but I don't know where you actually need it.

 

[Pat Hartman]

Here is a sample that show how to do something similar.

 

[Bone2]

Thank you all for your contributions.
I have been able to do it with the following code

Private Sub Form_Current()
Dim YrCount As Long
Dim MyCode As String
Dim CountCriteria As String


If Not IsNull(Me.InspectionID) Then
If IsNull(Me.txtInspectionCode.OldValue) Then
    CountCriteria = "[Purpose] = '" & Me.Purpose & "' And [EntityName] = '" & Me.EntityName & "' And Year([InspectionDate]) = " & Year(Me.dtInspectionDate) & _
    "And [InspectionID] <> " & Me.InspectionID
    YrCount = DCount("[InspectionID]", "table1", CountCriteria)
    YrCount = YrCount + 1
    If Me.Purpose = "Permit" Then
        MyCode = [EntityCode] & [PurposeCode] & Format(YrCount, "000") & DatePart("m", [InspectionDate]) & Right(DatePart("yyyy", [InspectionDate]), 2)
        Me.txtInspectionCode.Value = MyCode
    Else
        MyCode = [PurposeCode] & Format(YrCount, "00") & DatePart("m", [InspectionDate]) & Right(DatePart("yyyy", [InspectionDate]), 2)
        Me.txtInspectionCode.Value = MyCode
    
End If
End If
End If
End Sub

 

[Pat Hartman]

1. Do NOT use a dCount(). That is the dangerous method. You need to use dMax(). That is the safe method. If you ever deliberately or accidentally delete records, dCount() will return a duplicate value. dMax() returns the highest assigned value and so it is safe whether or not you ever delete a record. Better safe than sorry. Think of this as defensive programming 101. Just because the count method will work under some conditions doesn't mean you should use it. Use the method that works under all conditions.
2. Indenting your code is ever so much more helpful.