Need to have two autonumber fields in one table

  • Thread starter plisvb via AccessMonster.com
  • Start date
P

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
 
K

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
 
P

plisvb via AccessMonster.com

Thanks Klatuu,

This is definitely a help for me. I'll see if I can implement your code.
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
[quoted text clipped - 23 lines]
 
P

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
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
[quoted text clipped - 23 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top