Help needed with a unique number ? (4 Viewers)

[Gnmrad]

I am very new to access (clueless), i have workticket database which has all the workticket connected to many workticket detail, I need to make this workticket that when i want it to print to a delivery note i type in the quantity delivered and then when printing a unique number is produced , the number increments by 1 but does not run in the same order as the workticket, i am not sure if i should have another table for deliverytable. the worktickets can be closed randomly but the delivery not has to run +1 after each other in increments. I am using a form to search for the workticket then i type in the delivered quantity and then print which then closes the workticket and give me the delivery note number which only happens when i say close workticket y/n. No delivery note number until closed. Any ideas will help do i need a special delivery table separate from workticket , which seems i am duplicating data. If this sounds confusing then its confusing me.

 

[GPGeorge]

Over the years, a number of discussions here have included methods of implementing unique, sequential numbering.

Search results for query: unique sequential number

 

[arnelgp]

to make it simple, just add a Delivery table.
will hold the DeliveryID (Autonumber), WOTicket, DeliveryQty, DeliveryDate

use combobox to select which WoTicket on the form.

 

[Ken Sheridan]

Any ideas will help do i need a special delivery table separate from workticket , which seems i am duplicating data.

I don't see any duplication of data by the use of a separate Deliveries table as suggested by arnelgp, provided that the Deliveries table does not repeat any data from the WorkTickets table apart from the WOTicket foreign key.

However, you could simply add DeliveryNoteNumber(Indexed uniquely), DeliveryQty and DeliveryDate columns to the WorkTickets table and generate the next number in sequence when you close the work ticket with:

Me.DeliveryNoteNumber = Nz(DMax("DeliveryNoteNumber","WorkTickets"),0)+1

In a multi user environment there is the risk of conflicts if two or more users are closing work tickets simultaneously. The resulting error can be handled in the form's Error event procedure. A popular solution for many years has been Roger Carlson's at:

http://www.rogersaccesslibrary.com/forum/topic395.html

Even if you do use a separate Deliveries table, the use of Roger's solution or similar, rather than an autonumber key would remove the possibility of leaving gaps in the sequence, e.g. if you abort the insertion of a row into Deliveries.

 

[Gnmrad]

to make it simple, just add a Delivery table.
will hold the DeliveryID (Autonumber), WOTicket, DeliveryQty, DeliveryDate

use combobox to select which WoTicket on the form.

I have tried this ,but I want some way of having the workticket number in a combo box and I choose workticket number it shows the workticket and all related detail but then i want a delivery number generated when i print delivery note which will run in order, taking into account that some workticket might be number 10 is not closed and I am doing workticket 50 , I want the number of the delivery note to be 10 and then if I close workticket number 10 that would be delivery number 11 or i close workticket 35 and delivery is now 12. probably easy but i cannot work it out.

 

[Richard Rost]

The key idea is that the delivery note is a separate business event from the work ticket, so a separate Deliveries table makes the most sense. That is not duplicating the work ticket, it's just recording that a delivery happened for that ticket, along with the delivery quantity, delivery date, and delivery note number. The work ticket number can stay whatever it already is, while the delivery note number follows its own sequence based on the order deliveries are actually closed or printed.

Also, if the delivery note number must be strictly sequential with no gaps, I would not rely on an AutoNumber for that. AutoNumbers are fine for primary keys, but not for legal or business document numbering where sequence matters. In that case, generate the next delivery note number only at the moment the delivery is finalized, and be careful in a multi-user setup so two people don't get the same number at the same time. There are many videos on YouTube to show you how to generate sequential numbers. Just search for "ms access sequential numbering" and I'm sure you'll find some.

AutoNumbers are great for tracking relationships and forcing uniqueness, but don't rely on them for anything "real world." Hope this helps.

LLAP
RR