Generating a Unique ID

[cheuschober]

So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...

In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).

I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:

1006789-001 -|
1006789-002 -|--> Same EmploymentID
1006789-003 -|
1006790-001 ----> New EmploymentID with a reset numeric

Any ideas?
~Chad

 

[KenHigg]

Chad, You could do fld in the income table that is populated using logic in a function that would insert the appropriate value then combine it with the pk in the main table as you need it.

kh

 

[cheuschober]

First off, thank you very much Ken.

I had considered such an option already, but didn't know if it was actually feasible. So maybe a better question would be related to the process necessary to create the correct income id number I am relatively confused about.

Thanks again,
~Chad

 

[WayneRyan]

Chad,

Use the Income Table's BeforeInsert event:

ChildID = Nz(DMax("[EmploymentID]", "IncomeTable", "[EmploymentID] = " & Me.EmploymentID), 0) + 1

Wayne

 

[KenHigg]

One other potential problem I see with storing such a value; If a record is deleted you will have a gap.

kh