Auto Number

[Guest]

Hi All,
Access amateur here so go easy! I have a database that we use for sales
order management. The orders are entered using two linked forms (main and
sub data sheet) and the data is stored into two tables, which are linked via
the orderID primary key in the main table and they have a one-to-many
relationship. The main table stores the order header info (customer, account
number, order date) and the subdatasheet table stores the products, prices
and quantities on the order. I would like to add an autonumber to the sub
datasheet that starts at 1 for every order and increases by 1 for each order
line entered! Example:

OrderNo LineNo
12345 1
12345 2
67890 1
67890 2

Hope this makes sense,

Thx
Robin

 

[Guest]

As far as I know there is no easy way to do that (though I am no access
wizard).

You could achieve what you want with a "Before Insert" event code in the sub
data sheet. Code would look like as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)
/*** First get what the last line number was (this is one way)
lastlineno = DMax([order line number], "orderlines","orderno = " &
parent.orderno)

/** Increment last line number, or insert 1 if there are no lines
me.orderlinenumber = iif(isnull(lastlineno),1,lastlineno+1)
End Sub

Hope this helps.

 

[DL]

Just a comment;
You would appear to be missing a table, Customers, at the very least.
This should be linked to Orders, CustomerID or some such.

See here for some hints
http://www.databaseanswers.org/data_models/index.htm

 

[BruceM]

Robin,

You can probably contrive code using DMax to create an incrementing number
such as you describe, but if all you need is numbered line items on the
printed (or displayed) version of the Order you could set the control source
of an unbound text box to =1, and set its Running Sum property to Over Group
(you set this property of the text box Property Sheet (View > Properties
after selecting the text box in report design view).

DMax code to increment the number could be in, say, the After Update event
of a text box or combo box on the subform. It would be something like:

Private Sub YourTextBox_AfterUpdate()

Dim lngItem As Long

lngItem = Me.Parent.OrderID

If Me.NewRecord Then
Me.ItemID = Nz(DMax("ItemID", "tblItem", "OrderID = " & lngItem)) + 1
End If

End Sub

ItemID is the incrementing number in the related table.

By the way, there was an observation elsewhere in this thread that you
probably need a Customer table. To that I will add that very likely you
will also need a Products table. Rather than storing CustomerName in the
main table, you should be storing CustomerID (the primary key field from the
Customer table). The Products table would be for a list of your products
from which to select when completing a LineItem record. The Northwinds
sample database that ships with Access has illustrations of these things.

 

[Guest]

Thanks for the feedback guys. Bruce - i tried the unbound text box with
running sum set to over group and it's worked fine. It would be nice to have
the line numbers on the datasheet but printed on he report is good enough!

 

[BruceM]

You can have the numbers in the related table (if that's what you mean by
"datasheet"), but it is more complex than using the Running Sum. If having
the numbers in the report does the trick, I won't go into any more detail
about the other method, but it may be good to know the option is there for
future reference.