Custom Primary ID ( not Autonumbers)

[Mohammed Abdul]

Dear All,
I know that this question was asked many times but I am new to access and programming field.
I have created a tracking DB where incidents/accidents are tracked by Event ID number for which I have linked a table called recommendations RecID.
What I need is following:
1. The EventID shall be this format: Dept-EVENT#-Year.
2. The Recommendation ID shall be Dept-Event#-Year-Rec#

With the new year change the Event# shall reset to 1 and for every EventID the recommendation ID shall start with 1
example: ASD-0123-2011 ( EVENTID)
ASD-0123-2011-01 (RECID)
ASD-0123-2011-02 (RECID)
ASD-0123-2011-03 (RECID)

Anyone can help me please. Its quit urgent and if dont implement this I might loose my job.

[NeoPa]

It's certainly unusual (and probably counter-intuitive even) to have the sequence number to the left of a static value like the year, but it makes little difference fundamentally. It may just make understanding the logic a little more difficult for someone new to it.

Anyway, what you have to do is find the maximum matching value currently in the table, extract the number value from that, increment the number determined and then create a new one using that number.

I'm assuming from what you say that all this data is being stored in the same table (which would be a thoroughly bad idea but I have no other information so must assume that for now).

Assuming then, that we have variables indicating the Dept and for working with the Event# & Rec# we might have something like :

Dim strDept as String, strEventID As String, strRecID As String
Dim lngEvent As Long, lngRec As Long
 
strDept = "ASD"
strEventID = strDept & "-*-" & Year(Date)
lngEvent = Val(Split(Nz(DMax("[EventID]", _
                             "[Recommendations RecID]", _
                             strEventID), "-0-"), "-")(1)) + 1
strEventID = Replace(strEventID, "*", Format(lngEvent, "0000"))
 
strRecID = strEventID & "-*"
lngRec = Val(Split(Nz(DMax("[RecID]", _
                           "[Recommendations RecID]", _
                           strRecID), "---0"), "-")(3)) + 1
strRecID = Replace(strRecID, "*", Format(lngRec, "00"))

PS. It's generally a good idea, when you're in a hurry, to make sure you provide all the information likely to be required, otherwise the extra time taken (and all the experts who turn away from your question) are eating into your precious time.

[Mohammed Abdul]

Thanks! NeoPa, for your precious time and efforts you hav put in this code.
I just want to eloberate little more:
I have two forms The parent form control source is EventTbl and child form is RecommedTbl.
I just want to ask you where do I put this code in to use it and the second is I want in the first form only the Year-ASD-0001 and the recommedID shall be in sub form such as Year-ASD-0001-01,02,03....
Kindly help me out in implenting this.
Thanks!

[NeoPa]

I'm afraid I can't really help there.

Generally we don't like to do the work for you, which is why I have posted code which illustrates what you need to do, but which is no good to simply paste in. You need to take the code and put it where it makes sense within your project.

The other reason I can't help is that, even if I wanted to, I still don't have anywhere near enough information about your project to do it.

All that said, if you know your own project (database) it should be a simple matter to amend this code to fit it. It's a pretty close match already. Only you can do that though, as only you have the database.