 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2009Copying Records
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. 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 >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 [quoted text clipped - 5 lines] >can't figure out is how to copy the order details info with the new order id. >Any help would be greatly appreciated. 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. 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. > [quoted text clipped - 3 lines] >> >can't figure out is how to copy the order details info with the new order id. >> >Any help would be greatly appreciated. That doesn't work because the query creates key violations. > Okay - here's a way to do that: > [quoted text clipped - 23 lines] > >> >can't figure out is how to copy the order details info with the new order id. > >> >Any help would be greatly appreciated. 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. > [quoted text clipped - 3 lines] >> >> >can't figure out is how to copy the order details info with the new order id. >> >> >Any help would be greatly appreciated. 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). > 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 [quoted text clipped - 5 lines] > can't figure out is how to copy the order details info with the new order id. > Any help would be greatly appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.