Copy Set of records between 2 subforms

[doughnut]

Hi,
my database is for my clients purchases (from different suppliers).
The main Purchase form includes information as client name, supplier, date
etc.
The same form also includes 2 subforms - first is the actual order details
(product, price,qty) and the second is the arrival order details (Product,
Price,qty).
Each Purchase can have several products. Each subform is based of a
different table, both linked (one-to-many) to the mainform.

This is done since the arrived order maybe different from the actual order,
and I have to keep both sets of records.

So, I would like to use the "Delivered" check box (on the main form) in
order to copy the products from the original order subform to the delivered
order subform.
As i mentioned, there are several records to copy.

And incase the "delivered" checkbox is unchecked - to delete the copied
products.

hope this is not too long and thanks for reading.

 

[Rod]

Hi doughnut

Attach something resembling the following code to the OnClick event for the
checkbox:

If Forms!YourMainForm!Checkbox = True Then ' The box is click and ticked
Forms!YourMainForm!YourSecondSubform!YourField1 =
Forms!YourMainForm!YourFirstSubform!YourField1
Forms!YourMainForm!YourSecondSubform!YourField2 =
Forms!YourMainForm!YourFirstSubform!YourField2
...
Else ' The box is clicked and unticked
Forms!YourMainForm!YourSecondSubform!YourField1 = Null
Forms!YourMainForm!YourSecondSubform!YourField2 = Null
...
End If

The same can be done with a macro using conditions and the SetValue method.

 

[doughnut]

Hi, Rod and thanks for the reply,

This is similar to what i do now but it doesn't coppy all the records. it
copies only the first.
I need to copy all products from original order to delivered order.

I think i should use a kind of loop.

thanks again

 

[doughnut]

and another thing... i get a message - "can't change Key field to Null"

 

[Rod]

Okay then...

I assume the records which need to be transferred share an order number or
some similar means of associating them with the primary form.

In that case, have you tried INSERT and DELETE queries? Copy the records
from table one to table two using: INSERT INTO myTable2 (field1, fields...)
VALUES (value1, value2) WHERE UniqueIdentifier = IdentifierOnParentForm,
followed by DELETE Field1, Field2... FROM MyTable1 WHERE UniqueIdentifier =
IdentifierOnParentForm?

 

[doughnut]

Solved it in another way...
not clever as yours though...

thanks