Date-sequence primary key

[newbiewonkinobie]

I am in the process of moving our RFC/ECR (Request for Change/Engineering Change Request) data from Excel to Access. The problem I am having is creating the primary key. Our RFC/ECR number is a combination of YearMonthSequence, meaning the current year, the current month, and the sequence of RFC/ECR for that month.

For example:

060701  (1st RFC/ECR) 2006 July
060702  (2nd RFC/ECR)
060703  (3rd RFC/ECR)
060801  (1st RFC/ECR) 2006 August
060802  (2nd RFC/ECR)
060803  (3rd RFC/ECR)
(Each month, sequence starts over)

I would like the RFC/ECR to be the primary key so it will be unique and automatically created, but I am unsure of how to handle this. Hopefully I explained it good enough without being confusing. Any suggestions?

Flores

 

[PHV]

In the BeforeInsert event procedure of the data entry form:
strYYMM = Format(Date, "yymm")
Me![RFC/ECR number control] = Format(1 + Nz(DMax("[RFC/ECR number field]", "[RFC/ECR table]", "Left([RFC/ECR number field],4)='" & strYYMM & "'"), strYYMM & "00"), "000000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[newbiewonkinobie]

PHV, that was fast! I'm sorry that I don't know what to do with that code though. A Google search of BeforeInsert led me to:

http://www.everythingaccess.com/tutorials.asp?ID=Carry-data-over-to-new-record

Followed the instructions on the bottom of the page, and # 3 states:
"Verify that Access understands the code by choosing Compile from the Debug menu."

I did that an error says "Compile Error, invalid outside procedure" and the date is highlighted.

I am also not sure if I am supposed to have a column for the date. I am trying, but I haven't got it yet.

Flores

 

[PHV]

You should follow steps 5 thru 8

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886