AutoNumbering

[thunderbolt1164]

My Question is: Can you put Two(2) AutoNumber Fields on One(1) Form?

When I Coded my "New Transaction" Button with:

DoCmd.GoToRecord,,acNewRec

I get an Application or Object-Defined Error

 

[RuralGuy]

You can only put *one* AutoNumber FIELD in a TABLE but you can base a form on two tables that are joined. FYI, it is rarely a good idea to display an AutoNumber field on a form.

 

[thunderbolt1164]

There is Only One(1) AutoNumber per Table

They are:

tblTransactions
*TransactionID: AutoNumber
TransactionDate: Date/Time
TransactionType: Text

tblDropOffs
*DropOffID: AutoNumber
*TransactionID: Number
Employee: LookUp
Supplier: LookUp

tblDropOffsDetails
*DropOffDetailID: AutoNumber
*DropOffID: Number
Quantity: Number
Product: LookUp
Category: LookUp
UnitPrice: Currency

Do you think putting in another FK to link tblDropOffsDetails and tblTransactions would help?

 

[RuralGuy]

Can you go to the query directly and add a new record? BTW, Lookup fields can be problematic. Here's a link to read.

 

[thunderbolt1164]

Thanks for the advise and link. Can you suggest another way to do it?
I know of List Fields but figure that they would cause similar problems and my Product List is pretty big.(over 100 Items)

Have been thinking of doing a VB database but am still learning.

 

[thunderbolt1164]

Thanks any suggestions?
Know of List Fields but figure the would cause similar problems.

 

[statsman]

I have been using the following to create Invoice Numbers for many years.

In the example, I would not use "TheYear" portion.

You will probably find that "The Sequence" number is the same as your AutoNumber.

I downloaded this off another forum some time ago. The paper is yellow its so old but it works great.
I don't recall whose code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. It’s much easier to store and group them when they're stored separately.

It’s also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was
TheYear - 2006 TheSequence – 14
the next will be
TheYear - 2006 TheSequence – 15
assuming its still 2006. If this is the first entry in 2007 you will get:
TheYear – 2007 TheSequence – 1

Be sure to set TheYear and TheSequence text boxes on your forms to Enabled-NO Locked-YES. This will prevent people from over riding the pre-set numbers.