Line Numbers

[AceBK]

I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.

Thanks,
AceBK

 

[Minty]

You can program this behaviour in but it is fraught with problems for later use. You are better off simply having and OrderID 123456 and a separate table with your OrderLineID 3456789 (each order line obviously has the orderID in it as well).

You could store a repeating Order line number along with this to make it look pretty for a customer, but it won't serve you any real purpose internally, and if a customer deletes an line you then have the aggravation of trying to renumber it, store the new values etc etc etc.

 

[AceBK]

This line number will serve as a reference point for the item when I get to shipping the products, so if I have multiple shipments for one line item, I can reference it this way. I guess it doesn't have to be 1, 2, 3, etc. but as you said, it would look nicer. Do you know how to program this?

 

[Minty]

If it's for internal processing I wouldn't use it. If you stick to the OrderLineID you have a unique record for every line that cannot be confused with anything else.

Your PartNumber field would be the what you would reference for multiple picks of the same item - the line number would be meaningless in this context.

For a report for a customer, like a delivery note, you can easily add a "Line Number" but don't bother to store it anywhere, as its not really of any use to you.

A really simple layout is attached... You can easily add further normalisation such as multiple parts locations, multiple suppliers etc etc.

 

[AceBK]

OK thanks for the suggestion, I will give this a try.

 

[AceBK]

Well I came up with a different idea to get this number to generate, basically every time you add an item to an order, it goes in a list box, and the LineNumber is basically a count of the list + 1, however, what can happen is that I have two lines with the same number, and that is correct, but when that happens, my new line number is off. Is there a way to get the max number of a field in a list box using VBA? The list box is called "lstOrder" and the field is "LN". Any help is greatly appreciated.

 

[smig]

No easy way to have this in Access
Do some search for Running Numbers. I think its what you are looking for

 

[gemma-the-husky]

you can do this quite simply.

the way to do is to generate the line number in this way, using dmax. The best place to do this is in the "beforeupdate" event for the order line.

nextlinenumber = nz(dmax("linenumber","orderitems","orderID = " & whatever),0)+1

this usage generates a sequence for each separate ordered.


this will give you an intact sequence of lines - if you delete afterwards, you will get a gap.

 

[smig]

you can do this quite simply.

the way to do is to generate the line number in this way, using dmax. The best place to do this is in the "beforeupdate" event for the order line.

nextlinenumber = nz(dmax("linenumber","orderitems","orderID = " & whatever),0)+1

this usage generates a sequence for each separate ordered.


this will give you an intact sequence of lines - if you delete afterwards, you will get a gap.

This will not give you a dynamic line numbering in a query

 

[MarkK]

My 2c is that sequential numbering doesn't add any value.

1) Here is a numbered list
2) It shows what order things are in
3) But, it's already totally clear what order they are in
4) So the numbers don't really add anything

- This list is ordered too, but not numbered.
- If you must have number for each list item
- Then, by all means, go through the trouble of calculating them
- This is the fourth item, I can tell without numbers.

 

[smig]

My 2c is that sequential numbering doesn't add any value.

1) Here is a numbered list
2) It shows what order things are in
3) But, it's already totally clear what order they are in
4) So the numbers don't really add anything

- This list is ordered too, but not numbered.
- If you must have number for each list item
- Then, by all means, go through the trouble of calculating them
- This is the fourth item, I can tell without numbers.

LOL

In a report it's easy to put running numbers
Create a TextBox set it's RowSource to =1 and set it's RunningSum property to All

 

[Minty]

LOL

In a report it's easy to put running numbers
Create a TextBox set it's RowSource to =1 and set it's RunningSum property to All

But this isn't in a report - the OP wants to store it at order creation, we are all trying to persuade him it's not really
a) worth the effort
c) going to help
4) Do anything useful in the long run.

 

[AceBK]

The reason for this number is for a couple of reasons
1 - Sorting the line items on the job ticket
2 - referencing the line when we go to shipping, so it is important that the lines don't change. Once it is line 4, it stays line 4, regardless of deleting items to an order or adding lines.
3 - Allows production to reference to a line on a Work order instead of an item, perhaps a customer orders two of the same items but with slight differences, we can refer to the line number.
4 - The goal is to start over at 1 with every work order so that the line numbers don't go to high.
5 - When I deduct inventory, I reference the line number so that when we go to the inventory form, you can see what line number the inventory came from.

 

[MarkK]

But this isn't in a report - the OP wants to store it at order creation, we are all trying to persuade him it's not really
a) worth the effort
c) going to help
4) Do anything useful in the long run.

LOL, very good Minty.

Ace, I guess you can handle the before insert event of the form, find the biggest existing number, and add 1.

Private Sub Form_BeforeInsert(Canel as Integer)
    dim var
    var = DMax("Number", "OrderDetails", "OrderID = " & me.OrderID)
    If IsNull(var) then 
        var = 1
    Else
        var = var + 1
    End If
    Me.Number = var
End Sub

 

[gemma-the-husky]

I didn't realise you wanted sequential numbering in a query. My method gave you sequential numbering in a table.

If anyone ever really thought they needed sequential numbering in a query, I would batter them with a large wet fish until they realised the error of their ways.

fwiw, The post is in a tables forum. Why would this be about queries?

 

[MarkK]

Doesn't look like the OP really cares where it happens.

Is it possible to do this in a table? Or can this happen on the form?

"Large wet fish" though, I like that!
This is a very lively thread,

 

[smig]

Even in a table I think it should not be done.
Removing a record will leave you with a missing line, unless you renumber.

But back to what the OP asked for - a permanent numbers and dont care for missing lines, The easiest way will be using DMax() + 1, as suggested by Dave.

 

[Minty]

5 - When I deduct inventory, I reference the line number so that when we go to the inventory form, you can see what line number the inventory came from.

This is one reason why the large wet fish is being mentioned.

Which order line 3 did the inventory come from? - you have hundreds to choose from and will have to add you order header key into your query to make sure you reference the right one. If you had a unique order line you wouldn't have this or a hundred other issues you haven't stumbled across yet...

 

[gemma-the-husky]

This is one reason why the large wet fish is being mentioned.

Which order line 3 did the inventory come from? - you have hundreds to choose from and will have to add you order header key into your query to make sure you reference the right one. If you had a unique order line you wouldn't have this or a hundred other issues you haven't stumbled across yet...

well what you would have is an autonumber in the order lines table, and store this in the inventory table. this is nothing to do with the sequential order line ref.

I have no issue with storing an order line number in the table as well (although it doesn't really add much either) - but that's completely different to trying to generate a sequential reference in a query. I mean, what happens to the line numbers in the query if you re-order the query?

 

[AceBK]

WOW, this is quite the thread going on here.

To clarify, I did want this number to generate while adding records using a form. So in essence, I am recording the number to the table. This number's purpose is to be used mostly as a reference point. When a job ticket is printed off, this number can be referred to by the people on the production floor. When you go to the inventory form and find an inventory item, you will be able to see which job ticket and line number the inventory was removed for.

I will be the first to admit that I am not an expert in building access databases, but I have certainly learned a lot on these threads, So i just want to say thank you to everyone who has helped me not just in this thread but also in a bunch of the other ones I have posted.

Thank being said I think the challenge that I have now is that I am not able to clearly explain what my end goal is or why I am trying to get this number to happen. I am content with the solution that I have currently, which is count the lines in my list box and add one. This seems to work. It is not perfect, but it works.

Thanks again all,
AceBK

P.S. I love the big fish comment, that made me laugh.