Auto line number

[GUIDO22]

I have a form on which I can split an order into deliveries.
eg. Each contract can be 'split' into a number of phases(deliveries).

On this 'continuous' type form where this info is entered - I have a couple of fields that take the despatch date and quantity to be shipped. but I need a further field in the underlying table that represents the phase itself. ie. first delivery date will be 1, second date will be 2, third date will be 3 etc.
Where the contract is one shipment only - phase will be 0 or NULL.

This incrementing scale will be the same for every additional contract i.e. 1 - n.....(hence an AutoNumber field is NOT suitable for this purpose).

I am not too sure the best/simplest way to do this..... anyone done anything similar please?

Thanks for your help.

Regards
Guido

 

[workmad3]

will you have several values for each contract?

if so, then you should make a new table called phase, which stores all the values, and has a foreign key of the contract number.

if not, just have a number field in the contract table that stores the current phase

 

[GUIDO22]

Each contract will be different, of course, but if the deliveries are 'phased' (as stated), they will follow 1,2,3.....n depending upon the number of deliveries required.

I have got a seperate table keyed to the contract number in the main orders table but the problem is this phase identifier and how to generate it automatically (at VBA level or table level????) - (it cannot be entered manually!).

Hope this is clear....?

 

[directormac]

There are several discussion threads on a similar problem, people wanting to generate incrementing numbers but, for one reason or another, not wanting to use autonumber. Read MS Access help on the DMax function and search the archives here.

--Maximum Mac

 

[workmad3]

if i understand correctly, i think what you want is a separate table for delivery dates. into this, you have the id for the contract it relates to, the phase numbers, and the dates associated with these phases. To add into this when you are splitting the contract, you will want to keep track of the number of splits, then do a loop along the lines of

For i = 1 to noOfPhases
Insert into phase table(contract id, phase no, delivery date) values(contract id, i, date(i))
next i

that is very high level pseudo code, but hopefully you get the idea.

if i have the wrong end of the stick, please say

 

[GUIDO22]

if i understand correctly, i think what you want is a separate table for delivery dates. into this, you have the id for the contract it relates to, the phase numbers, and the dates associated with these phases. To add into this when you are splitting the contract, you will want to keep track of the number of splits, then do a loop along the lines of

For i = 1 to noOfPhases
Insert into phase table(contract id, phase no, delivery date) values(contract id, i, date(i))
next i

that is very high level pseudo code, but hopefully you get the idea.

if i have the wrong end of the stick, please say

I appreciate your response but the fields on the form are bound controls- the phase identifier is a hidden field on the form also bound to the appropriate field in the underlying table. I was hoping to update the value of this field in the BeforeUpdate() event or similar when the user creates this new row record. Having a line of code to do this in the event is' nt too clean a way of doing this ..... any other ideas.....????

 

[directormac]

Not to mention WorkMad's idea (as I understand it) requires knowing ahead of time how many phases the delivery will be split into.

IF I understand what you're doing, there is an underlying fallacy in your structure. Devlieries are not split into phases. You are either delivering something or not -- it might take more than one delivery to complete the order, but any given delivery is a thing unto itself. So your multiple "phases" are actually multiple deliveries, all related to the same order record. If you want to track the order of the deliveries, you can do so by the delivery dates. To see if another delivery ("phase") is needed, check items ordered vs. items delivered thus far -- if something was ordered but hasn't been delivered, you will need at least one more delivery ("phase").

Or am I missing something? Probably the case, as I haven't had NEARLY enough coffee yet today...

--Decaf Mac

 

[workmad3]

Not to mention WorkMad's idea (as I understand it) requires knowing ahead of time how many phases the delivery will be split into.

IF I understand what you're doing, there is an underlying fallacy in your structure. Devlieries are not split into phases. You are either delivering something or not -- it might take more than one delivery to complete the order, but any given delivery is a thing unto itself. So your multiple "phases" are actually multiple deliveries, all related to the same order record. If you want to track the order of the deliveries, you can do so by the delivery dates. To see if another delivery ("phase") is needed, check items ordered vs. items delivered thus far -- if something was ordered but hasn't been delivered, you will need at least one more delivery ("phase").

Or am I missing something? Probably the case, as I haven't had NEARLY enough coffee yet today...

--Decaf Mac

this is a classic case of my explanations getting in the way... this is almost exactly what i had in mind, but with better explanation, and more detail on the delivery status this is why im never going into teaching

 

[GUIDO22]

Not to mention WorkMad's idea (as I understand it) requires knowing ahead of time how many phases the delivery will be split into.

IF I understand what you're doing, there is an underlying fallacy in your structure. Devlieries are not split into phases. You are either delivering something or not -- it might take more than one delivery to complete the order, but any given delivery is a thing unto itself. So your multiple "phases" are actually multiple deliveries, all related to the same order record. If you want to track the order of the deliveries, you can do so by the delivery dates. To see if another delivery ("phase") is needed, check items ordered vs. items delivered thus far -- if something was ordered but hasn't been delivered, you will need at least one more delivery ("phase").

Or am I missing something? Probably the case, as I haven't had NEARLY enough coffee yet today...

--Decaf Mac

The bottom line is that I DO know how many delivieres there will be... I didnt want to get too deep but I may have to - here goes.....

We receive an order from a customer - this generates an order. Normally, this is the usual/simplest scenario for us - one ORDER with one ORDERLINEITEM pertaining to the component(s) ordered. The contract number assigned will be five digits ONLY. eg. 32100

However, in some situations we may have a bulk order with despatch dates at say 25 units per month for 4 months. We would have the following :
with a 6 digit contract identifier.
321001 - first month
321002 - second month
321003 - third month
321004 - fourth month

This is important for us because when we come to book staff hours to these contracts, at present they are all getting lumpued onto the one (5 digit) contract number and it is very difficult (nigh on impossible) to gauge how many hours have been worked on each 'phase' of the contract.... Hence, the 'phasing' of contracts will enable us to book staff hours specifically to each particular phase.

At table level - I wanted to keep the original 5 digit contract identifier on its own in the ORDER table - having a seperate table for phased deliveries PHASEDORDER_DETAILS that holds the delivery date and its corresponding 'phase' (1,2,3,4....) identifier.... just how to create this phase ID thats all.......

When reports are run etc. it would be a simple case of taking the 5 digit contract number and (if applicable) appending the phase id to it...

I hope this makes sense?

 

[workmad3]

Right, i think i have the idea now.

You want a table that has the 5 digit contract number, along with a phase number to create a composite key(access doesnt seem to like these too much if you try to enforce them, but the idea still works) this composite key then references in the phase details the delivery dates for that phase etc.

Implementation probably along the lines of:
Set up the table as above.
The value for the hidden phase number should just start at 1 for the first delivery of the contract, and increment for each additional delivery.
If there are no phases, you store phase one with the delivery dates in the phase details.
When producing reports and get the details from the phase details table, you do a recordcount of phases for each contract. If the count is 1, then you dont append the phase number to the contract id. If its more than one, you append the phase number(might look better if you do 34215-1, 34215-2 etc to show its sub parts more easily, and then keeps making sense if you ever go over 5 digits for contract id)

hope this makes sense and is in enough detail.

 

[GUIDO22]

You want a table that has the 5 digit contract number, along with a phase number to create a composite key(access doesnt seem to like these too much if you try to enforce them, but the idea still works) this composite key then references in the phase details the delivery dates for that phase etc.

Yes - table structure follows :
ORDERDETAILS
[OrderId/Base Contract Id]
PHASEORDER_DETAILS
[OrderRefId/Phase/Delivery Date]

Implementation probably along the lines of:
Set up the table as above.
The value for the hidden phase number should just start at 1 for the first delivery of the contract, and increment for each additional delivery.
If there are no phases, you store phase one with the delivery dates in the phase details.

Correct!

When producing reports and get the details from the phase details table, you do a recordcount of phases for each contract. If the count is 1, then you dont append the phase number to the contract id. If its more than one, you append the phase number(might look better if you do 34215-1, 34215-2 etc to show its sub parts more easily, and then keeps making sense if you ever go over 5 digits for contract id)

Exactly - this is what I had planned to do.

Where to go now.....?????? Back to the original question - how and where (form level in VBA code / macro / table) level do I generate the phase id?

TFAYH
Guido

 

[workmad3]

like i said, when you are making the deliveries for the order, you start off with the phase at 1. every time you have a new delivery added to the contract, you add one to the phase counter, which is stored in the delivery. if the form isnt reloaded for every new delivery (just reset to blank values with the delivery stored) then you just have a variable in the form code that doesnt get reset and holds the phase counter which gets incremented on every reset(i.e. every new delivery).

if the form is completely reloaded each time, then you can do the same thing, just move the phase counter out into a module and declared as a public integer so you can access it, principle is still the same though

 

[Jack Skeleton]

That's have been really hard to understand...I have some Access Database working more or less "as you want". It's not necessary to go to the VBA level except for some small details.
Assuming that the [OrderId] field in the ORDERDETAILS table is linked to the [OrderRefId] field in the PHASEORDER_DETAILS table (1 to ∞),
and assuming your Form fills the ORDERDETAILS fields and has a Subfrom that fills the PHASEORDER_DETAILS fields... (maybe I assume too much, but...):

In the Subform, the enter field for "Phase" has to be blocked and associated to an event (you mentioned a "beforeupdate", that's a valid option). You can use a very simple code like this one:

Dim rsttmp As DAO.Recordset, i as integer

Set rsttmp = CurrentDb.OpenRecordset _
("SELECT Max([PHASEORDER_DETAILS].[PHASE]) AS CurrentLastPhase _
FROM [PHASEORDER_DETAILS] _
GROUP BY [PHASEORDER_DETAILS].[OrderRefId] _
HAVING [PHASEORDER_DETAILS].[OrderRefId])=" _
& Val(Nz(Forms("Put Here Your Form").Controls("And Here Your Control Name for the OrderID Field").Value)), dbOpenDynaset)
[COLOR=seaGreen]'This recordset takes the Maximal Phase number recorded in the underlying table
'for the Order you have in the Form. [/COLOR] 
If rsttmp.RecordCount = 0 Then [COLOR=SeaGreen]' If there are no records yet[/COLOR]
        i= 1   [COLOR=SeaGreen]' Phase = 1[/COLOR]
    Else
        i= Nz(rsttmp(0).Value) + 1 [COLOR=SeaGreen]' Phase = Last Phase  recorded +1[/COLOR]
End If
Forms("("Put Here Your Form").Controls("And Here Your Control Name for the Phase Field").Value = i
Rsttmp.close

Hoping it helps...

 

[GUIDO22]

That's have been really hard to understand...I have some Access Database working more or less "as you want". It's not necessary to go to the VBA level except for some small details.
Assuming that the [OrderId] field in the ORDERDETAILS table is linked to the [OrderRefId] field in the PHASEORDER_DETAILS table (1 to ∞),
and assuming your Form fills the ORDERDETAILS fields and has a Subfrom that fills the PHASEORDER_DETAILS fields... (maybe I assume too much, but...):

In the Subform, the enter field for "Phase" has to be blocked and associated to an event (you mentioned a "beforeupdate", that's a valid option). You can use a very simple code like this one:

Dim rsttmp As DAO.Recordset, i as integer

Set rsttmp = CurrentDb.OpenRecordset _
("SELECT Max([PHASEORDER_DETAILS].[PHASE]) AS CurrentLastPhase _
FROM [PHASEORDER_DETAILS] _
GROUP BY [PHASEORDER_DETAILS].[OrderRefId] _
HAVING [PHASEORDER_DETAILS].[OrderRefId])=" _
& Val(Nz(Forms("Put Here Your Form").Controls("And Here Your Control Name for the OrderID Field").Value)), dbOpenDynaset)
[COLOR=seaGreen]'This recordset takes the Maximal Phase number recorded in the underlying table
'for the Order you have in the Form. [/COLOR] 
If rsttmp.RecordCount = 0 Then [COLOR=SeaGreen]' If there are no records yet[/COLOR]
        i= 1   [COLOR=SeaGreen]' Phase = 1[/COLOR]
    Else
        i= Nz(rsttmp(0).Value) + 1 [COLOR=SeaGreen]' Phase = Last Phase  recorded +1[/COLOR]
End If
Forms("("Put Here Your Form").Controls("And Here Your Control Name for the Phase Field").Value = i
Rsttmp.close

Hoping it helps...

Thanks Jack - this looks to be exactly what I needed - I had figured that VBA code in an event maybe the only way forward but I was wondering if maybe there was a simpler way that I wasnt aware of...... will keep you posted

Thanks