Ms Access Update Recordset Clone Access

Posted on by
Give More FeedbackMs Access Update Recordset

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. 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. Kodak 3200 Driver Mac.

Recordset Clone and Bookmark General. You can use this property to return a fixed recordset to navigate or update as you see fit. Microsoft Access MVP .

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 Please enter an answer.

Send me notifications when members answer or reply to this question. 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. 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.

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.