Good evening everyone,
I built a database to track reprocessing of parts that were fixed
where I work. We are required by instruction to serialize these parts
as they come back in the following format. Last 2 digits of the year
and then the # it came back as. An example of the Serial # is
08-0122. I want to make the database automatically add to this
number
and generate a new one each time a new record is created. i have
tried
several different ways to accomplish this but nothing has worked thus
far. Any help on this issue would be greatly appreciated.
Do the numbers need to reset to 1 at the beginning of each year?
If not:
Add an autonumber field to the table (call it partcount). Add a field that
defaults to the current year (call it partyear) - set the default property
in design view to Year(Date())). Now a simple expression gives you your
serial number:
right([partyear],2) & "-" & right("0000" & [partcount], 4)
If the numbers need to reset to 1 at the beginning of each year, then I
would suggest creating a table (call it LastSerial) with two fields:
partyear and partcount (the latter should not be an autonumber field).
Select both fields in Design view and click the toolbar button to
designate both to be the primary key of the table. Save it.
Then in the Modules tab, create a new module and add this VBA function
(this has not been tested and may contain typos) to build your serial #:
public function GetSerial() As String
dim db as database,rs as dao.recordset
dim newserial as string,sql as string,errmsg as string
dim t as single, errnum as int
set db =currentdb
sql = "select partyear,partcount from lastserial " & _
"where partyear=year(date())"
db.begintrans
t=timer
do until errnum=0
if timer - t > 30 then
errmsg = "Time out while trying to retrieve next serial #"
errnum=-1
exit do
else
on error resume next
set rs=db.openrecordset(sql,,dbdenywrite,dbpessimistic)
errmsg=err.number & ": " & err.description
errnum = split(errmsg,":")(0)
end if
loop
if errnum <> 0 then
msgbox errmsg
GetSerial = "error"
db.rollback
else
if rs.eof then
rs.addnew
rs(0) = year(date())
rs(1) = 0
rs.update
end if
rs.edit
rs(1) = rs(1) + 1
rs.update
newserial = right(rs(0,2) & right("0000" & rs(1),4)
rs.close
GetSerial = newserial
db.commit
end if
set db=nothing
end function
You can call this function in a form to assign the next available serial #
to a part.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
