Creation of Unique ID Field

[jkejke]

Trying to create a unique identifier for a service request by requestin area--we input a two character code like UI then the date--(it pulls the year for the the next 4 blocks so we have UI-2001-XXX--want xxx be the next in line for UI or AS or whatever (about 10 sections)if have ui-2001-015 in file want it to be UI-2001-016--just stumped--anyone have any idea

Thanks

 

[THoey]

How about creating a table just to keep the NextValue for each of these ten types. Every time you create a new record, get the last value, add 1 to it, append it to your string for your unique identifier, and then increment the counter in your table.

tblCounters
SectionName
CurrValue

Since you have ten types of sections, this table would have ten records, as in:

UI    015
AS    027

Just an idea... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[jkejke]

see where you are going and have been trying that--but must have year in--boss is adamant about having correct year in 1st of year--some projects we do not get until midmonth of jan when requested last of dec--can not seem to get sql to work right--can create UI-2001- in table but not able to get out to other--not had much success with dlookup--

Thanks

 

[MichaelRed]

Look at faq700-184 It is my own 'Home Grown' remedy for the situation. I will (as always) need to be modified to suit your particulars, but the overall process is EXACTLY what you want/need.


MichaelRed
redmsp@erols.com

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

 

[THoey]

"I will (as always) need to be modified "

Gee Michael, I don't think you need to be modified... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[jkejke]

Have been told that this the data will only be input by one person--how many times have you heard that one--but looks promising for answer--thanks again to each of you

 

[THoey]

I didn't look at Michaels thread, but I am sure it will work like a charm. I missed what you said about needing the year in the string. What I was trying to say was using this table of numbers, you could get the next value and append it to your string:

DIM LastID as Integer
DIM mySQL as String
DIM NewValue as String

mySQL = "SELECT CurrVal FROM tblCounters WHERE SectionName = 'UI';"

LastID = DoCmd.RunSQL mySQL
NewValue = "UI" & Year(DATE()) & CStr(LastID)

mySQL = "UPDATE tblCounters SET CurrVal = CurrVal + 1 WHERE SectionName = 'UI';"
DoCmd.RunSQL mySQL

Or something similar to this. I am just freelancing this, so it is not tested.

Good luck with it...
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MichaelRed]

Terry,

I think the relevant qoute is from way back when ...

Something like "when your'e green growing, if your'e ripe you are starting to rot", ther fore I ATTEMPT to just keep on Growing (otherwise it's just another typp).


MichaelRed
redmsp@erols.com

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