Personialised Autonumber

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

Hello all,

I'm thinking about computerising a very laborious procedure at
work - every transaction has an ID number in the format Type-Year-/-number.
The number increments each time - so, today for example I had A3/324,
A3/325, A3/326 - I also had T3/175, T3/176, T3/177 etc.

Is there any way I can get an access table to do this incrementing
automatically - like auto number, but with the A3/ bit in front of it?

Thought maybe input masks but I can't see how... the T and A
transactions can be in two different tables or one, which ever makes more
sense

Any ideas gratefully received!

Amy
 
Amy, to personalize your AutoNumber, go to the table design and find the
field that is AutoNumbered. On it's Format Property, enter "A3" then save
your changes. That should do it.

HTH,

Nick C.
 
Hello all,

I'm thinking about computerising a very laborious procedure at
work - every transaction has an ID number in the format Type-Year-/-number.
The number increments each time - so, today for example I had A3/324,
A3/325, A3/326 - I also had T3/175, T3/176, T3/177 etc.
Click to expand...

This is called an "Intelligent Key" - and that's not a compliment,
unfortunately! Packing three disparate pieces of data into a single
field is generally considered bad design, and at the very best it
makes your code much more difficult to manage. The only real
justification for ever doing it is for compatibility with a legacy
system from the days before relational databases, when it did make
sense (for filing cabinet labels, frex).

I'd suggest storing THREE fields - Type, Yearno (i.e. 3 for 1993,
2003, 2013 and 2023 :-{( ) and Seq, and concatenate them in a Query or
in the control source of a form or report control for display
purposes. You can increment the Seq by forcing ALL data entry to be
done using a Form. Have a combo box bound to Type so the user can
select a valid Type; set the Default property of the Yearno field to

Year(Date()) MOD 10

or, if you want it to be T10/xxx in 2010, set the default to

Format(Date(), "yy")

In the Type combo's AfterUpdate event use code like

Private Sub cboType_AfterUpdate(Cancel as Integer)
Me!Seq = NZ(DMax("[Seq]", "yourtablename", "[Type] = '" & Me.cboType _
& "' AND Yearno = " & Me!txtYearno)) + 1
End Sub
 
Back
Top