Copying Records

[SteveH]

I have a table called Orders and a table called OrdersDetails. The key field
on the orders table is an auto number type. I have a form that shows data
from orders with a sub form showing each order's details. Since a lot of our
orders are the same except for the customer information, we would like to
have a button on the order form that copies the current order information to
a new record. I can use an append query to copy the Order information to a
new record and the auto number field will increment the order id. What I
can't figure out is how to copy the order details info with the new order id.
Any help would be greatly appreciated.

 

[SteveH]

Yes that is correct but I'm not sure how to get the order id.

 

[SteveH]

That doesn't work because the query creates key violations.

Okay - here's a way to do that:

Put the ID field somewhere on the form but set it's visible property to FALSE.
Also add an Unbound text box with visible set to false. In the AfterUpdate
event of the form, add this:

me.unboundtextbox = me.IDfield

Now in the Unbound Text Box AfterUpdate event put:

me.unboundtextbox.defaultvalue = me.unboundtextbox

Of course change the control names to your control names. Now you can refer
to Forms!myform.unboundtextbox in the query. You will also need to refresh.

Let me know if you don't understand or have problems with this.

Bonnie
http://www.dataplus-svc.com

Yes that is correct but I'm not sure how to get the order id.

So sounds like the problem is that there are possible multiple details so you
cant just copy the record but must append records. I think you would have to

[quoted text clipped - 13 lines]

can't figure out is how to copy the order details info with the new order id.
Any help would be greatly appreciated.

 

[CraigH]

Hi,
It's past my optimal time for thinking seriously about alternatives to my
own suggestion but I don't think an append query would work in your
situation. (could be wrong but don't want to thing about it

The option I have is to just use recordsets to get the information and
create the records in the 2 tables. And then go that new order you created.

Its a little involved to show here but the Visual basic Help is good at
this. Search for AddNew (dao is my preference), FindFirst, Openrecordset
(although the example is verbose).