Sequential Numbers ON.001, ON.002

[sv89]

Hi,

I have a form frmOrder and a subform sbfrmOrderDetails.

The PK OrderID for tblOrders is a autonumber. I want to create a sequential ID for tblOrderDetails such that OrderDetailID would be 8888.001, 888.002 etc. for OrderID 8888 and 8889.001, 8889.002 etc. for the next OrderID.

I've tried to put the following code on the BeforeInsert event field of sbfrmOrderDetails but it comes up with an error saying Run-time error '2465': Microsoft Access can't find the field 'frmOrders' referred to in your expression.

Me.SubOrderID = Nz(DMax("[SubOrderID]", "[tblOrderDetails]", "OrderID= " & Form!frmOrders!OrderID), 0) + 0.001

Help?

 

[vbaInet]

Are the digits to the right of the decimal place fixed? Is there no possibility that you would run out of numbers, such as 8888.999?

 

[sv89]

No possibility of that. Max would be about 300

Are the digits to the right of the decimal place fixed? Is there no possibility that you would run out of numbers, such as 8888.999?

 

[vbaInet]

Ok, put your code in the Default Value property of that textbox.

 

[sv89]

It doesn't seem to work. It comes up as #Name? on the form

 

[vbaInet]

Is OrderID on the subform?

 

[sv89]

No, it's on the main form

 

[vbaInet]

Is your subform not linked via OrderID?

 

[boblarson]

Pardon the intrusion but this is a very bad, bad, bad idea. You can create a special field for this number if it is important to you (can't see why at this point), but the key shoujld just be an autonumber and let the SYSTEM handle the keys. You should not be involved in that.

 

[sv89]

They are linked via OrderID.

I need it this way for invoicing. In most cases, the order would be invoiced as whole. But I need it for exceptional cases where invoices are needed for each individual Order Detail.

I think it has to be done this way. No?

 

[boblarson]

I think it has to be done this way. No?

No - again, I would not attempt to add this as a primary key. You can add it as a separate field.

 

[sv89]

Okay, so the PK for tblOrderDetails should be an autonumber? Point taken. But would you know how to store this detail in another field?

 

[boblarson]

Okay, so the PK for tblOrderDetails should be an autonumber? Point taken. But would you know how to store this detail in another field?

Okay, for your other numbering system you can put code in the subform's BEFORE UPDATE event to add your special number to its field.

 

[sv89]

Thanks.

It works fine if there is atleast 1 OrderDetail for an Order and you're trying to add a subsequent one.

However when I add a new Order (mainform) and then OrderDetail the field on the subform goes 0.001, 0.002 etc. Any ideas?

 

[DCrake]

In your invoic header file you would have a primary key and an invoice number

HeaderPK = Autonumber
FldInvNo = Text type although it will contain numbers

TblInvoiceHeader
HeaderPK:1
FldInvNo:0000001

Then in your order details table you would have a PK, a FK and a line number

TblInvoiceDetails
DetailPK = AutoNumber
FldFK = as per FldInvNo
OrderLineNumber = as per FldInnNo

DetailPK:0000001
OrdrLineNumber:0001

DetailPK:2
FldFK:0000001
OrdrLineNumber:0002

DetailPK:3
FldFK:0000001
OrdrLineNumber:0003

Then you create a relationship between the FldInvNo in the header table And the FldFK in the detail table

Then in a query you create a new field and concat the FldFK & "." & OrdrLineNumber to get

0000001.001
0000001.002
0000001.003

 

[Galaxiom]

I agree with much of what David has said and was surprised nobody else pointed it out earlier. The dotted number definitely should not be stored but concatenated from the two fields on demand as David has shown. In fact it would be a breach of normalization to store both the invoice number and the full dotted version of the line number since the invoice number is duplicated in the full dotted line number.

However I disagree with the use of a text formatted field as OrdrLineNumber. Line Numbers are integers and the field should thus be an integer datatype. This has several advantages over using the text type. It is faster to work with and smaller to store than the text. Moreover it is much faster and easier to increment.

For display it can be formatted to the padded form:
Format([OrdrLineNumber],"000")

If desired, the Format property of the field in the table can be used to also display it there with the padding.

I would also consider using a composite key for InvoiceDetail table based on HeaderPK and OrdrLineNumber rather than adding a synthetic key (DetailPK). The HeaderPK and OrdrLineNumber should have a composite index anyway to ensure there are no duplicates so it might as well be the PK of the table.

The only downside of this is that any table that is related to the LineNumber (eg from a Payment table) will require a composite FK to refer to the line. However this is not the disadvantage it might initially seem.

Indeed it can be a considerable advantage because the Payment record carries the information to directly identify the related Invoice. Hence the calcuation of the total payment amount assigned to an invoice can be calculated without reference to the OrdrLine table.

Under the system where the Payment is assigned to a particular line via the OrdrLinePK the total of payments against an invoice can only be calculated through a join involving the InvoiceDetail table.

 

[AccessBlaster]

Me.SubOrderID = Nz(DMax("[SubOrderID]", "[tblOrderDetails]", "OrderID= " & Form!frmOrders!OrderID), 0) + 0.001
Help?

In this example I don't use the primary key, I use a text field called AssetNumber. I then concatenated for the report.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String
Me.AssetNumber = Nz(DMax("AssetNumber", "tblAssets", strCriteria), 104884) + 1
End Sub

 

[sv89]

Okay, so you suggest to use autonumber for OrdrLineNumber and format it to display as 001?

However I want the OrdrLineNumber to start from 1 for each Order. How would I go about doing this?

 

[Galaxiom]

Typically the main form would have a record source based on a query on the InvoiceHeader table. The lines would be shown as a datasheet or Continuous subform and linked by the InvoicePK field.

The subform new record is assigned a LineNumber using the BeforeInsert Event of the subform. This is triggered as soon as a value is written into any field in the new record line. The Link field value is automatically written to the record at the same time.

Typically a DMax function is used to find the largest existing line number having the matching InvoiceFK and then adding one. The Nz returns a zero if no match is found making the first line number as one.

Me.[LineNumber] = Nz(DMax("[fldLineNumber]", "[tblInvoiceDetail]", "[fldInvoiceFK]=" & Me.InvoiceFK)) + 1

[LineNumber] can be either a Control on the subform or a Field in the RecordSource of the subform.

However a faster and tidier arrangement would be to place a control in the footer of the subform with the expression:
=Max([LineNumber])

([LineNumber] must be a field in the Recordset of the subform)

Then use the BeforeInsert Event to write that value plus one to the Field.
However where there are no existing records the footer control will be an error. Use an IIF and the IsError function to deal with this.

You won't want the control seen, especially if it says #Error. In Datasheet view the footer is invisible. If using a Continuous forms set the Control Visible property to False.

 

[sv89]

Thanks GalaxiomAtHome.

Now I'm ready to move to invoicing. Not really sure how to implement that. Do I just have a field in the Orders table? Or do I need a separate table for Invoices? Would I need another table for Payments?

Usually, payments are made when an order has been placed. But there are cases when payment is made after the order is fulfilled.

Does anyone know of any Invoicing Access samples? Google only throws up commercial ones.