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.

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

Processing your response...

Discuss This Question: 10 &nbspReplies

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

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

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

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

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

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.

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.

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

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.

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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