If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to have two autonumber fields in one table
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/For...esign/200911/1 |
#2
|
|||
|
|||
Need to have two autonumber fields in one table
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... 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/For...esign/200911/1 |
#3
|
|||
|
|||
Need to have two autonumber fields in one table
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 |
#4
|
|||
|
|||
Need to have two autonumber fields in one table
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] Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
Thread Tools | |
Display Modes | |
|
|