Copy/paste a record with VBA in Microsoft Access

70 pts.
Tags:
Access VBA
Microsoft Access
VBA
Visual Basic for Applications
How can I copy number of records from a form and paste them in another form with VBA, not with the right click, in MS Access? Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think the best way to do it in VBA is to use the RecordSetClone property. This way you can manipulate columns before you actually insert incase there would be issues with primary keys. The recordset clone returns all records that you currently see on the form. So this would account for filtering too.
“TableName” referred for the RS recordset is the table bound to the other form. Or even your same form that you are copying from.

Code could look like this.

Dim RSC as recordset, RS as recordset
set RSC = me.recordsetclone
Set RS = currentdb().Openrecordset("TableName",vbOpenDynaset)
While Not RSC.EOF
RS.AddNew
RS![Field1]=RSC![Field1]
RS![Field2]=RSC![Field2]
etc.
RS.Update
RSC.MoveNext
WEnd
RS.Close
RSC.Close

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JennyMack
    Editor note: Toli posted his response as another question; I've removed the question and pasted the content here: thank you Random, it helped to give me a clue how to make it work. im building a software for a restaurant. the issue has to do with printing the receipts for customers.I want to be able to print receipts for them every time they oredr something and the total receipt at the end. How can i seperate to print after each time they order without printing everything and how to print everything they have to pay at the end? help would be much appreciated
    4,280 pointsBadges:
    report
  • Randym
    You could possibly use a CustomerID and OrderID as a unique key. So the separate orders go under the same customer ID. You can print the receipt for each individual order and at the end, print the receipt for the customer. Customer ID could be the table number or something that identifies who that customer is. Order ID could simply be a datetime that makes it unique within the CustomerID
    1,740 pointsBadges:
    report
  • Toli
    Hi Randym, thank you for your help. Actually I created a mainform containing data on the number of the table, waiter, date, etc and the subform containign data for every order on that table. Now should i create another subform for every order and then add everything to the other subform in oredr to print seperate receipt for evry order and then print the total receipt? I am confused. In general, should i create seperate table and form for every table of the restaurant or should i create multi instances of the same form even though the total in the subform does not work in other instances apart from the original form? Thank you very much for your support.
    70 pointsBadges:
    report
  • Darryn
    I would have seperate tables for each of the entities: table waiter invoice order order_notes (with memo field for people who request amendments to the menu item). customer (if customer history is required) product Then, when a customer is seated at a table, a new invoice record is created with PK invoice_id FKs table_no, waiter, customer_id (new or returning?) and other information i.e. date & time, Total_Value (0), Paid_Amount (0), override_reason (used here for discounts, unhappy customers, promotions etc), Paid (Y/N) When the customer places an order, a new order record is created with PK order_id FKs invoice_id, waiter (this can be different from the original waiter so that any waiter can take an order), product_id and other information e.g. date & time, number_ordered, Order_Value, Override order_number, Override_Order_Value, override_order_reason (this allows you to make adjustments and take orders off invoices, but still manage stock control and operational losses) . Now for the forms: Same form frm_Invoice for all tables, but a prompt needed to enter the table number. On entry, the form can be set using the table_id (entered) and the invoice_id i.e. unpaid invoice for that table number (there should only ever be 1 invoice unpaid at any one time - you might want to build some checks in for this). You could then have a sub_form subfrm_Invoice_Orders with all the orders showing that make up the invoice total. Everytime a new order is added, have that event open another form e.g. frm_order_print (presented for printing), clone the recordset, populate the form and print the record, then close the form and return to the invoice. At the end, just print the invoice with the subform. Quite a lot of work to do, but will present management with lots of MI and controls i.e. customer retention, table turnover, waiter performance, ops losses, stock control etc
    765 pointsBadges:
    report
  • Toli
    thanks Darryn. yes, that would be a solution. However, since many waiters will have to work at the same time, the same invoce form needs to open many times at the same time. I managed to create one original form for every table with the subfom for orders for that table and other instances of this form but however, the total in the subform is workign only on the original form but not in other instances of the original form. How can I have it work since this seems the most appropriate solution. Otherwise having form for every table does not seems a solution. Thank you a lot.
    70 pointsBadges:
    report
  • Toli
    i would really like to know how to create a form which could be opened as amny times as necessary, however, the total of the subform work i every of them???? since evry form is a copy of the original form then the total in the subform works only in the original form but not in other instances of that form. im trying to work this out for days now but iys useless. help would be much appreciated.
    70 pointsBadges:
    report
  • Darryn
    Hi Toli, Have you made sure that you subform is correctly linked to the main form, through the Link Master Fields and Link Child Fields (properties of the subform)? You should set both the links to invoice_id, assuming that you have these in your app (i.e. invoice.invoice_id is in your main form query and orders.invoice_id is in your subform query).
    765 pointsBadges:
    report
  • Toli
    Hi Darryn. Yes i have checked and they are just as you are suggesting. It's unbelievable, its not supposed to be so complicated. I've tried many ways but simply its not working. The total on the subform its always reflecting the total of the subform of the original form and not the total of the subform on the present instance!!!!!! Im about to give up of the whole thing. Thaks anyway for your help.
    70 pointsBadges:
    report
  • Darryn
    Yes, it sounds quite strange, but there are a few more things you can look at. Open your sub-form in a new window (select the subform, then go to the menu View->Subform in new window). Do you have a Form Footer? If not, add one, and then add a text box, name it something relevant e.g. txtOrdersTotal, and put in a formula that sums the total cost of your orders. Save that, close it down, and go back to the main form. In the text box that has your invoice total, make sure the Control Source points it to the subform total e.g. =[sfrmOrders].[Form]![txtOrdersTotal]) That should ensure that what is displayed in the total of the subform matches that displayed in the original form. The only other thing that I can suggest is if you send me the database, and I can have a quick look at it to see if I can find out what is not working. Cheers, Darryn
    765 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following