Need to have two autonumber fields in one table

[plisvb via AccessMonster.com]

Hello,

I've created an inventory dbase for a grocery warehouse and initially I
created one table for different types of invoices:

1) Incoming
2) Outgoing
3) Shrinkage

My accountant has informed me that each invoice of each type must be
sequential so incoming are INC1,INC2,INC3 and outgoing are O1, O2, O3 instead
of the numbers being mixed.

If I would've know this at the beginning I would've separated the tables, one
for incoming, one for outgoing etc...

Is there a way to add another autonumber field and have it only increment
depending on the type of invoice it is?

I've tried adding creating another table just to hold the Incoming number
ID's and linking it to my main table but when I add it to the form that I'm
using, all the fields go blank.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

[Klatuu]

You should not be using Autonumber fields for any meaningful data. They
should only be used as surrogate primary keys and to link Parent/Child
tables.

And invoices are invoices. All invoices should be in the same table.
Ignore what your accountant is telling you. Most accountants live in a
spreadsheet world. Access is not a spreadsheet program.

Part of the problem with them would drive you accountant round the bend.
You cannot depend on them to be sequential. There will be gaps. That is
because once a new record is created, the number is assigned. If you don't
actually update the table with the record, the number assigned is discarded.

Your invoice table should have a field identifying the type of invoice it
is. How you assign invoice number to each type depends on your business
rules. If you want a sequential number for each type of invoice, you can
use the DMax function to find the highest number available for the invoice
type and add 1 to it. Here is a very basic example that should give you an
idea of how it works. I use the After Update event of the text box (or
combo, doesn't matter) where you enter the invoice type:

Private Sub txtInvType_AfterUpate()

If Me.NewRecord Then
Me.txtInvNumber = Nz(DMax("[InvNumber]", "tblInvoice", "[InvType] =
""" & Me.txtInvType & """"), 0) + 1
End If

End Sub

"plisvb via AccessMonster.com" <u55912@uwe> wrote in message
news:9e908e6ca0b6f@uwe...

[plisvb via AccessMonster.com]

Thanks Klatuu,

This is definitely a help for me. I'll see if I can implement your code.

Klatuu wrote:
>You should not be using Autonumber fields for any meaningful data. They
>should only be used as surrogate primary keys and to link Parent/Child
>tables.
>
>And invoices are invoices. All invoices should be in the same table.
>Ignore what your accountant is telling you. Most accountants live in a
>spreadsheet world. Access is not a spreadsheet program.
>
>Part of the problem with them would drive you accountant round the bend.
>You cannot depend on them to be sequential. There will be gaps. That is
>because once a new record is created, the number is assigned. If you don't
>actually update the table with the record, the number assigned is discarded.
>
>Your invoice table should have a field identifying the type of invoice it
>is. How you assign invoice number to each type depends on your business
>rules. If you want a sequential number for each type of invoice, you can
>use the DMax function to find the highest number available for the invoice
>type and add 1 to it. Here is a very basic example that should give you an
>idea of how it works. I use the After Update event of the text box (or
>combo, doesn't matter) where you enter the invoice type:
>
>Private Sub txtInvType_AfterUpate()
>
> If Me.NewRecord Then
> Me.txtInvNumber = Nz(DMax("[InvNumber]", "tblInvoice", "[InvType] =
>""" & Me.txtInvType & """"), 0) + 1
> End If
>
>End Sub
>

>> Hello,
>>
>[quoted text clipped - 23 lines]
>>
>> Thanks

--
Message posted via http://www.accessmonster.com

[plisvb via AccessMonster.com]

Hi Klatuu,

I'm trying to use this code (which is yours more or less) but I keep on
getting a runtime error:

Private Sub Transaction_Type_AfterUpdate()
If Me.NewRecord Then
 Me.inv = Nz(DMax("[OrderID]", "Orders", "[Transaction Type] = 2"), 0) +

1
End If
End Sub

inv = field where I want the calculation to occur
OrderID = Autonumber field for the orders(invoices) table
Transaction Type = 1 - Incoming 2 - Outgoing 3 - Shrinkage I'm trying to do
all the outgoing orders hence the #2.

Not sure about the syntax, new to VB

Thanks

Klatuu wrote:
>You should not be using Autonumber fields for any meaningful data. They
>should only be used as surrogate primary keys and to link Parent/Child
>tables.
>
>And invoices are invoices. All invoices should be in the same table.
>Ignore what your accountant is telling you. Most accountants live in a
>spreadsheet world. Access is not a spreadsheet program.
>
>Part of the problem with them would drive you accountant round the bend.
>You cannot depend on them to be sequential. There will be gaps. That is
>because once a new record is created, the number is assigned. If you don't
>actually update the table with the record, the number assigned is discarded.
>
>Your invoice table should have a field identifying the type of invoice it
>is. How you assign invoice number to each type depends on your business
>rules. If you want a sequential number for each type of invoice, you can
>use the DMax function to find the highest number available for the invoice
>type and add 1 to it. Here is a very basic example that should give you an
>idea of how it works. I use the After Update event of the text box (or
>combo, doesn't matter) where you enter the invoice type:
>
>Private Sub txtInvType_AfterUpate()
>
> If Me.NewRecord Then
> Me.txtInvNumber = Nz(DMax("[InvNumber]", "tblInvoice", "[InvType] =
>""" & Me.txtInvType & """"), 0) + 1
> End If
>
>End Sub
>

>> Hello,
>>
>[quoted text clipped - 23 lines]
>>