AUTOMATICALLY create a Unique Key(NO AOUTONUMBER INVOLVED)

[ZaZa]

Hi every one, I have yet another question for the gurus outthere.

This is a resuest from some users that I don't know how to satisfy.

I have a database that tracks Current assignments ( called "Punch items&quot for engineers. Engineers belong to several disciplines like Piping, Electrical, Mechanical etc.
So the tblPUNCHITEMS has the follow fields:

tblPunchItems
----------------
PunchItemID (Autonumber)
Title (text)
EmployeeID (number
Discipline (text)
etc.


Now for reports the engineers wanted the punch items grouped by Discipline. that is fine But now they are getting confused by the apparent out of sequence numbering for the PunchItenID ( egthe Mech discipline might only have punchItemId's 1, 4, 24 and 33)

They keep asking dumb questions like "Did I delete Punch items 2 and 3 for the Mechanical discipline?".

Question:
Is there a way to automatically add a record identifier that would begin with a description of what discipline the Punch item relates to and be sequential PER DIscipline??

I want to retain the PunchItemID as the autonumber. But when entering a new punch item( say PunchItemID = 23), after I choose the discipline ( eg MECH) I want another field to be automatically filled in with "MECH-05" . this mean that it is the 5th MECHANICAL related punch item on this database (which contains 23 records in all).

The intention is to later use this filed to sort and group reports...

Please help. If I have confused anyone, please let me know and i will try to explain again..


Thanks,
ZaZa

 

[p27br]

You can create an event procedure on the before update event of a form based on the table
ex
sub form1_BeforeUpdate
dim reference as string

reference = Discipline & IDNr
check if ID already exists in table first then
IDField = reference

 

[ZaZa]

P27br,

Thanks for responding.

The "IDNr" part of the reference is were i am not clear becasue I want the numernic part of the field identifier to act just like an autonumer( ie I want it to incrementally go up by 1 if a new record for that discipline is added and to not appear again if that record is deleted)

Any ideas on how to do this will be greatly appreciated.

Thanks,
ZaZa

 

[ZaZa]

Anyone???

ZaZa

 

[MichaelRed]

You would need to use it more to understand the process than to just plug in, but see faq700-181 for a technique to generate a unique Id. One of (probably THE) modification would be to change the table to indlude a col for each dicipline, and uopdatte the ID for the dicipline, and remove the reference to the date rollover.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ZaZa]

Michael,

Thanks for directing me to the FAQ.

These past days I have been trying to modify it to suit my needs but I have a questions about the FAQ

It seems that there are two tables involved : the tblNewQINum and the BASICDATA table where you used the select query. When you got the new QiNum you updated the tblNewqINum . Shouldn't you also update the BASICDATA table with the new QiNum??

ZaZa