How to generate unique id based on another field

[blyzz]

I am trying to enter data into a table using a form.
There are different fields in the form I have to generate an automatic unique id # for the records in VBA based on the Reportdate entered in an access form.


The user would enter reportdate and the formula should generate unique id as month of reportdate+Yearreportdate+001 and increment for all the events for that month.
So for 2 more events for same month, id would be (reportdate&Yearreportdate&002)
For the events occurring newmonth, I need to reset the id as reportdate&Yearreportdate&001
Then I enter the data using add record command button in the form after entering all the data and the automatically generated id field to save the data into the table.
I am able to generate the id based on Today’s date but not sure if it is possible to do that based on the the reportdate entered in the same form.

Any suggestion would be much appreciated.

Thanks,
Blyzzard

[June7]

Certainly can use the report date.

Generating custom unique identifier is a common topic. Search forum. Here is one: https://www.accessforums.net/forms/a...ing-23329.html

[CJ_London]

you might want to consider reordering your unique id to

YearofReportDate+MonthofReportDate+001

reason is if you sort on it they will be in 'date' order

e.g. your way
02-2014-001
02-2015-001
03-2014-001

my way
2014-02-001
2014-03-001
2015-02-001

[blyzz]

I will reorder them for Year+Month+001 but I am still not sure how to refer to the report date in the VBA code.
Right now I am using Now() to extract Month and Year

strCurrentYear = Right(Year(Now()), 2)
strCurrentMonth = Month(Now())

[June7]

Reference textbox on form.

strCurrentYear = Right(Me.textboxname,2)

[blyzz]

I am new to access.
I wrote the code in a new module and not in the form.
I am using the sql statement in the form to insert data from form to the table.

[June7]

You mean an INSERT or UPDATE sql action?

Did you review the referenced link? That code is a custom function that is called by multiple forms to generate unique identifier. Function can be structured to have required argument(s). Arguments can be the year (and month) value. Pass value by argument when calling function.