An record number

  • Thread starter Thread starter vb_Dumb
  • Start date Start date
V

vb_Dumb

I was wondering if anyone knew how to create a record number that
would automaticlly change one increment at a time but in a weird
format like this:
ETP000001
ETP000002
ETP000003...... ect Just a thought would like to have that instead of
a autonumber because of the skipping numbers and i have five different
forms that all need unique DMR numbers ETP, MHC, FG, HFA, IQC. Thanks
 
Store the "ETP" and the number parts in separate fields. Use DMax function
to get the next incremented number.

Show the combined recordnumber by concatenating the two fields together:

RecordNumberValue = ETPField & NumberField
 
Assuming the following:
1) The text box txtYourNumber is bound to the incrementing field YourNumber
in the table tblMain
2) ETP is stored in a field named DMR
3) There is an unbound text box named txtFullNumber
you could have something like this in the form's Current event:

Dim strDMR as String
Dim lngNum as Long

strDMR = Me.DMR
lngNum = Nz(DMax("[YourNumber]","[tblMain]", _
"[DMR] = """ & strDMR & """"),0) + 1

Me.txtYourNumber.DefaultValue = lngNum
Me.txtFullNumber = strDMR & Format(lngNum,"000000")

Again, this assumes a DMR value is field in the table (rather than there
being a separate table for each DMR).

In a multi-user environment you will need to make allowance for the
possibility that two users may be entering records at the same time, and
thus generating duplicate numbers. We can go into that if need be.
 
Ken and Bruce have bother offered good information. I thought I might
suggest how the DMax fucntion would look:

Me.txtRecNum = Nz(DMax("[RecNumber]", "SomeTable", "[DMR] = """ & Me.txtDMR
& """"),0) + 1
 
Back
Top