purchase order auto number lines

[Ade F]

I wondered if anyone had an idea for this problem. We currently use a purchase order system designed by my good self my only draw back at the moment although not essential it would really put the icing on the cake.

Basically when a user chooses which items they wish to order they are created in a line-by-line listing stating description (combo) and quantity. What I would like to do is have the lines of chosen products numbered sequentially.

For example when a supplier phones us I would like it for them to say “about item 7 on your list; the Russian backscratchers ah yes….”. At the moment these lines are not numbered and therefore appear a little open to interpretation in some people’s eyes.

Any ideas from the pro’s would be greatly appreciated…

Regards

Ade

 

[dcx693]

You don't say in exactly what view you'll have the listing, but here's a link to a page that has a "numbered query". See if it helps: Roger's Access Library. Look for "NumberedQuery".

 

[Ade F]

You don't say in exactly what view you'll have the listing, but here's a link to a page that has a "numbered query". See if it helps: Roger's Access Library. Look for "NumberedQuery".

Humble apologies and my lack of details the purchase order has an option of a datasheet view or formatted report form. Depending on what solution I can come up with depends on my final output.

By the looks of the query you mentioned this will give me a running sequencial numbers. I wish for every time a new purchase order is created for the product lines to be numbered in realtime.

Basically I will run down how the purchase order is created

Purchase OrderID - Primary key autonumber
Other details regarding supplier name etc.

The productID below is a sub form within main above
Purchase Order ID
ProductID - Primary key autonumber
Quantity

From modding the numbered query all I can manage is to get a sequencial numbers related to all ProductID and not those just associated with that specific purchase order.

I'm trying to achieve this

1. Cream eggs 2
2. Rice cakes 3
3. Broomsticks 5

Any further ideas from other would be great.

Cheers

Ade

 

[dakcg]

Greetings, I have been trying to find a solution to this very problem for over 3 years for a Quote database, and I have not been able to find one. However, I early on found a work-around that works quite well- having the user type in the item number themselves when the order is being entered into the database. It is an extra step, but it gets the info into the table for easy use on reports.

DAKCG

 

[neileg]

I guess you could use a DMax on the order lines for that order number, and then add 1 to create the next line number. On the first line the DMax will return a null because there will be no lines yet, so remember to use a Nz() to turn this to a zero.

There may be better solutions. Domain aggregate functions like DMax aren't very efficient.

 

[Rich]

Use the Form_Current event to add the CurrentRecord number

 

[Ade F]

thanks people

Use the Form_Current event to add the CurrentRecord number

Sorry Rich could you elaborate on this one.... I'm not the sharpest knife in the draw.

Regards

Ade

 

[The_Doc_Man]

Sorry Rich could you elaborate on this one.... I'm not the sharpest knife in the draw.

Regards

Ade

Pardon me while I put on the "professor" hat.

The issue is that autonumber is useless here because you need to restart it.

Therefore, the problem you have is that the line-item number must be computed on-the-fly. This implies that you have (at least) two tables set up to hold a purchase order.

table tblPO
-- POSeq (PK) - (whatever format you choose) - this is YOUR internal PO number because the customer might have a different PO system.
-- customer info including customer's PO number
-- date info
-- whatever else belongs to the PO

table tblPOItem
-- POISeq (part of PK) - integer or long (but integer might do)
-- POSeq (part of PK)
-- Info about the item

Now, here is how you do this on-the-fly...

In the form, when you are about to create a new line item, you have to do a DCount( ) of the tblPOItem where the POSeq = the currently open POSeq. Then add 1.

The first time you do this, the Dcount is zero so the number is 1. As you add new line items, the Dcount increases.

The reason you cannot do an autonumber is because the key has a meaning other than strictly as a sequence number. In this case, it is a running count of items in the PO. Therefore, you can never use Autonumber for this.

Weakness of this method: If it is possible to go back and delete an earlier line item, you will have a missing line item number. You can handle this either of two ways.

First, just mark the deleted item as "deleted". No muss, no fuss, but it might not be pretty.

Second, you could build an update query that reassigns numbers to line items based on using a slightly different DCount. Trigger it with a "Renumber" button on the form if you wish.

Do this only when no line items are "open" i.e. still being updated. You would want to have a DCount of the line items for the current PO where the line item number is less than the current line item number, and then reset the line item number. When you come to the missing number, the query will reset the line item number for the next item.

 

[Ade F]

wow thanks for your time doc man

Pardon me while I put on the "professor" hat.

The issue is that autonumber is useless here because you need to restart it.

Therefore, the problem you have is that the line-item number must be computed on-the-fly. This implies that you have (at least) two tables set up to hold a purchase order.

table tblPO
-- POSeq (PK) - (whatever format you choose) - this is YOUR internal PO number because the customer might have a different PO system.
-- customer info including customer's PO number
-- date info
-- whatever else belongs to the PO

table tblPOItem
-- POISeq (part of PK) - integer or long (but integer might do)
-- POSeq (part of PK)
-- Info about the item

Now, here is how you do this on-the-fly...

In the form, when you are about to create a new line item, you have to do a DCount( ) of the tblPOItem where the POSeq = the currently open POSeq. Then add 1.

The first time you do this, the Dcount is zero so the number is 1. As you add new line items, the Dcount increases.

The reason you cannot do an autonumber is because the key has a meaning other than strictly as a sequence number. In this case, it is a running count of items in the PO. Therefore, you can never use Autonumber for this.

Weakness of this method: If it is possible to go back and delete an earlier line item, you will have a missing line item number. You can handle this either of two ways.

First, just mark the deleted item as "deleted". No muss, no fuss, but it might not be pretty.

Second, you could build an update query that reassigns numbers to line items based on using a slightly different DCount. Trigger it with a "Renumber" button on the form if you wish.

Do this only when no line items are "open" i.e. still being updated. You would want to have a DCount of the line items for the current PO where the line item number is less than the current line item number, and then reset the line item number. When you come to the missing number, the query will reset the line item number for the next item.

I shall give this a go...... Such a seemingly simple question generates a fairly complex answer as I suspected. A most ingenious way.... As far as the tables; I have structured them in a similar way to your. The functionality of the tables are fine it is just trying to balance this blasted huge cherry on top of a tiny cake.

Other methods would be gladly considered this is always handy for workarounds.

Cheers mate.

Ade

 

[Ade F]

sequencial numbering

I shall give this a go...... Such a seemingly simple question generates a fairly complex answer as I suspected. A most ingenious way.... As far as the tables; I have structured them in a similar way to your. The functionality of the tables are fine it is just trying to balance this blasted huge cherry on top of a tiny cake.

Other methods would be gladly considered this is always handy for workarounds.

Cheers mate.

Ade

Would it make a difference if I said I would like to do this in a report this way whatever the report gets is final i.e. the user cannot delete lines what you see is what you get.

Is there some way in code I could assign an initial value to a text box and then for every event of the orderid or productid add one to that value thus creating a sought of running numbered series?. This must be possible but it's a tricky on.

Another thought I had but dont know if this is possible is to create a table with the numbers 1 to 20 and some how display these for every event of an orderid or productid then re-set this having done so. This may be pie in the sky at the moment but having seen the amount of expertise in these forums somebody will come up with a rock solid solution.... I'm hoping anyway

Ade

 

[Rich]

Set the control source of an unbound text box to =1, set it's running sum property to over group

 

[Ade F]

Set the control source of an unbound text box to =1, set it's running sum property to over group

yeh I just sussed that about 25 seconds having written the message. With a report its completely different to on the fly input forms.

Cheers Everybody for their effort.... Onwards and upwards

Ade