create delivery note from an order

[smalty]

I have a form (fOrderHead) with a subform (fOrderItem). The subform may contain several items which might be delivered at different times. I want the user to be able to select one or many items in the subform that are ready for delivery, press a 'button' that will generate a 'Delivery Note' with a unique Delivery Note No. for the items selected which will be stored in the table tDeliveryNotes and displayed as a report in Print Preview. There will be no part shipments of individual items.
I got as far as selecting the items using a check box and passing the information to a query that the delivery note will be based on but couldn't figure out how to generate a unique DNote No and store the info in the DNote table.

Any help much appreciated

 

[TheAceMan1]

How are ya smalty . . .

[blue] ... couldn't figure out how to generate a unique DNote No ...[/blue]

Have a look at the [blue]Rnd[/blue] function.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kermit01de]

Whar about having an autocount field in the tDeliverynotes? Each time you add a receord it automatically increases by 1 ...

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[smalty]

I appreciate your thoughts.
I guess autonumber and Rnd would work, in fact the key field in tDeliveryNote is an autonumber. My problem is getting the information in the query (which is based on the selection made on the form)lets say appended to tDeliveryNote and then sending that record which would include the Autonumber, to a report.

Smalty

 

[kermit01de]

Is all needed information in the appended record in tDeliveryNote?
If yes, you may base the report on a Max(tDeliveryNote.KeyField)
If no, I am not getting the pieces together to a picture for me.

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[smalty]

OK....good as far as it goes BUT.....if only ONE item was selected for delivery everything is OK but if more than one item is selected each item will generate a separate DNoteNo in tDeliveryNote when the records are appended. What I want is for all the records that are selected at that time to go onto the same delivery note. Something like

DNoteNo Item Qty
DN00002 Brochure 5000
DN00002 BCard 2000

Smalty

 

[kermit01de]

What I would try is - as soon as the Button is clicked:

Find max DNoteNo from tDeliveryNote, add 1, add records to tDeliveryNote, Open Report based on all records with maxDNoteNo

Sorry, I do not have the time to create the whole sourcecode at the moment.

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[gcDataTechnology]

Smalty, I surmize you link the 2 tables together to utilize the subform ability, are you linking on an order number? Is that number unique? Would it make sense to utilize that number as the DNoteNo? if your OrderNumber = 00005 then create a concatenated version in an AfterUpdate event when the record is being entered in the subform fOrderItem. Maybe when the Item is entered the AfterUpdate event runs code similar to the following:

Private Sub Item_AfterUpdate()
on Error GoTo Err_Item_AfterUpdate

'short hand
Me.DNoteNo = "DN" & Forms!fOrderHead.[OrderNumber]

'long version
Forms![fOrderHead]![fOrderItem].Form![DNoteNo] = _
"DN" & Forms![fOrderHead]![OrderNumber]

Exit_Item_AfterUpdate:
Exit Sub

Err_Item_AfterUpdate:
MsgBox Err.Description
Resume Exit_Item_AfterUpdate

End Sub



Learnin', Growin' and Failing Forward

 

[smalty]

Kermit, I confess I don't fully understand your solution....not your fault. As with so many ppl here I am learning every day and I will investigate your solution further. I thank you for your thoughts.
gcDataT, I do understand what you are getting at and I suspect it would work if I were delivering all items on the order at the same time, but this may not be the case and therefore I cannot have two separate deliveries with the same number.
I have come up with a perhaps 'dirty' solution. After having selected the item(s)for delivery and giving each item a shipping date of today, a create table query runs based on this information. There is a DeliveryNote No field in the query which is a calculated field with a formula of [OrderNo]& Int((99999*Rnd())+10000) (the values could be anything) but at least this gives me a unique number. This query creates a temporary table which is used as the source for the Delivery Note Report for printing. When the note has printed another query runs appending the temp table records to tDNote and then the temp table is deleted. All this is run from a macro started from a command button in the fOrders and there is a Stop Macro action to allow for the document to be printed after which the append and delete parts are completed. I told you it was 'dirty' but it does do exactly what I want.......but I appreciate all yours time....Thank You