Increment value on defined pattren

[detactiveh2o]

Hello guys,
I am new to the form, and also new to MS Access. I am using Office 2013.
I've created a database at my work place, in which I wanted to add my requisitions. What I wanted is to have an auto increment field for my reference number field, that field is like 001/Jan/15. In which first 3 digits refer to the requisition number, then month and then year.
How can I create an input mask with auto increment to solve out this problem.

Thanks,

 

[BlueIshDan]

You could always separate the two fields, one date, one req_no.

Then when you wish to display the information on reports you can pass it to a combining function that will format the two values to display that. Unless you're parsing the data from another source, I would take this route.

There are actually many things I would need to know about your set up.

Could you explain the source of these values and/or the process they will go through to be generated?

 

[Solo712]

Hello guys,
I am new to the form, and also new to MS Access. I am using Office 2013.
I've created a database at my work place, in which I wanted to add my requisitions. What I wanted is to have an auto increment field for my reference number field, that field is like 001/Jan/15. In which first 3 digits refer to the requisition number, then month and then year.
How can I create an input mask with auto increment to solve out this problem.

Thanks,

There are people here who do not like composite keys the way you describe them. I don't mind. I never had any problems with them. One thing you want to do though if you are creating one like you suggest is to construct in a way it orders itself for easy searches. So, tour code should be sth like 15-01-001 (yy-mm-seq) to order the numeric string from the smallest to the largest. You create the "next" reference number by VBA code.

First you need the function that creates the next ref. number:

Private Function GetNextRef() as String
    Dim HiRef As String, DateMask As String, rnum As Integer
 
    HiRef = Nz(DMax("RefNum", "RefTable"))   ' <=supply your own names
 
    If HiRef = "" Then
        GetNextRef = Format(Date, "yy-mm") & "-001"
        Exit Function
    Else
        DateMask = Left(HiRef, 5)
    End If
 
    If DateMask <> Format(Date, "yy-mm") Then
           GetNextRef = Format(Date, "yy-mm") & "-001"
    Else
           rnum = Val(Right(HiRef, 3)) + 1001
           GetNextRef = DateMask & "-" & Right(CStr(rnum), 3)
     End If
End Function

Second you will need to execute this function from the form's Before_Update event (should be the last statement there):

If Me.NewRec Then 
   Me!RefNum = GetNextRef  '<= supply your own field name for 'RefNum'
End If

Best,
Jiri

 

[spikepl]

The reason why you should keep things separate at source and concatenate when needed, like BlueIshDan suggested, is that once an application is running, new requirements invariably arise.

Your composite key throws information away unnecessarily. Retain dates as dates - that will make it easy to do any date-based operations in the future.

 

[Solo712]

The reason why you should keep things separate at source and concatenate when needed, like BlueIshDan suggested, is that once an application is running, new requirements invariably arise.

Your composite key throws information away unnecessarily. Retain dates as dates - that will make it easy to do any date-based operations in the future.

As I said, opinions on this vary. I for one fail to see where in deploying a composite key you would be "throwing away information unnecessarily". That is a big mouthful but really it does not explain anything. In fact, in some operations (especially those related tracking and filing) this kind of coding notation is very helpful as it establishes quickly a visual clue - e.g. in helping to keep documents relating to a business period together.

At any rate, unlike other experts here, I do not presume to tell people how to organize their business. I would assume - unless there is a good reason not to - that people know why they want what they want when they come here asking for help. I would just as soon help them.

Best,
Jiri

 

[detactiveh2o]

Actually, I am entering that value now manually. And it's the entry in the main table in which data comes from different table.
I've made employee name table separate, station, nature of request, budget head, etc. then all are linked with the main table.

 

[Solo712]

Actually, I am entering that value now manually. And it's the entry in the main table in which data comes from different table.
I've made employee name table separate, station, nature of request, budget head, etc. then all are linked with the main table.

Do you want to generate this reference number ? If so, you need to supply the name of the table in which it sits and the name of the field.

Best,
Jiri