Hi kawther,
you can use the form BeforeInsert event if the data is already known --
or the control AfterUpdate event if the data is only known after a
control is filled out
'~~~~~~~~~~~~~~~~`
dim mMask as string _
, mNextNumber as long
mMask = format(someDate, "yyyy-mm")
mNextNumber = nz( _
dMax( _
"mid([fieldname], 9, len([fieldname])-8)" +
,"[tablename]" _
, "left([fieldname], 7) = '" & mMask & "'" _
),0)
me.controlname = mMask & format(mNextNumber, "0000")
'~~~~~~~~~~~~~~~~
where
-- someDate is a fieldname or a controlname or a variable -->
ie:
me.date_controlname
mDateVariable
-- [fieldname] is the name of the fieldname to lookup
(store as text, length = 12)
-- [tablename] is the name of the table with the data
~~~~
even though you said the mask was YY-MM-XXX, I used your data example
with mask --> yyyy-mm-9999
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*