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.

 

[bhicks11 via AccessMonster.com]

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
run an append query with the new records autonumber ID for those details (to
have your relationship) from the copied record autonumber ID.

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

 

[SteveH]

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

 

[bhicks11 via AccessMonster.com]

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]

 

[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.

 

[bhicks11 via AccessMonster.com]

Hi Steve,

You don't want to append the old ID Number, you need to add the ID number
from the current record you are trying to append. If you are adding new
records to the details table and have the ID from the main form, you should
not have key violations. Where is the key violation?

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

Okay - here's a way to do that:

[quoted text clipped - 23 lines]

 

[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).