Need help with Alphanumeric Auto number combination

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randy - 17 Mar 2007 17:21 GMT

Access 2000. I need a to assign an Audit number that is alphanumeric and
partially auto number and partially assigned by the user. Example: Audit
Number: 01086AB07 The first two digits of 01 is the first audit, which
will advance one number automatically for each audit (Textbox). The second,
third and fourth digits are the julian date which will be set to
default(Textbox). The fifth and sixth digits are assigned indicating a
company name(Combobox) for selection by user. The last two digits are the
year which will be set to default(Textbox). I need this alpanumeric sequence
to end up as a single audit number of 01086AB07 in a single field of my
table. If this is this possible how do I do it, or do I need to look at a
different approach.Thanks for the help, Randy.

Reply to this Message

strive4peace - 18 Mar 2007 00:18 GMT

Hi Randy,

you can use the BeforeInsert event to do something like this

'~~~~~~~~~~~~`
dim mNum as long _
, mJulDate as integer _
, mCompany as string _
, mStrYear as string

if isnull(me.JulianDate_controlname) then
me.JulianDate_controlname.setfocus
msgbox "You must specify a Julian Date before creating new records" _
,,"ERROR"
CANCEL = true
end if
if isnull(me.Company_controlname) then
me.Company_controlname.setfocus
msgbox "You must specify a Company before creating new records" _
,,"ERROR"
CANCEL = true
end if
mJulDate = me.JulianDate_controlname
mCompany = me.company_controlname.column(1)
mStrYear = format(date(), "yy")

mNum = nz(dMax("cInt(left([AuditNumber_fieldname],2))" _
, "Tablename" _
, "cInt(mid([AuditNumber_fieldname],3,3)) = " & mJulDate _
& " AND mid([AuditNumber_fieldname],6,2) = '" & mCompany & "'" _
& " AND right([AuditNumber_fieldname],2) = '" & mYear & "'") _
,0) + 1

me.AuditNumber_controlname = format(mNum,"00") _
& format(mJulDate ,"000") _
& mCompany _
& mStrYear

'~~~~~~~~~~``
assuming
-JulianDate_controlname and Company_controlname are unbound fields
-Company_controlname is a combobox where the second column = Company
abbreviation (column index = 1 since indexing starts at 0)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> Access 2000. I need a to assign an Audit number that is alphanumeric and
> partially auto number and partially assigned by the user. Example: Audit
[quoted text clipped - 7 lines]
> table. If this is this possible how do I do it, or do I need to look at a
> different approach.Thanks for the help, Randy.

Reply to this Message

Randy - 18 Mar 2007 00:45 GMT

Thanks for the reply, I will give it a try. Regards, Randy

> Hi Randy,
>
[quoted text clipped - 61 lines]
>> field of my table. If this is this possible how do I do it, or do I need
>> to look at a different approach.Thanks for the help, Randy.

Reply to this Message

strive4peace - 18 Mar 2007 05:53 GMT

you're welcome, Randy :) hope it works out -- or at least gives you the
logic so that you can make the changes you need

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> Thanks for the reply, I will give it a try. Regards, Randy
>
[quoted text clipped - 63 lines]
>>> field of my table. If this is this possible how do I do it, or do I need
>>> to look at a different approach.Thanks for the help, Randy.

Reply to this Message