Need help with Alphanumeric Auto number combination

[Randy]

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.

 

[strive4peace]

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
*

 

[Randy]

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

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
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.

 

[strive4peace]

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

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
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.