VBA to copy data from one table to another (1 Viewer)

[peterkp1988]

I'm trying to create a database for an invoicing system. Sometimes a job is quoted for before being carried out. As not all jobs are quoted for first and not all quotes are carried out, I have this as two separate tables.

Can anyone suggest how I can use VBA to create a button to convert a quote into an invoice.

Data setup:

Table Quote -QuoteID -CustID -QuoteDate -JobAddress -Tipping -Invoice	Table QuoteItems -QuoteID -Description -Materials -Labour
Table Invoice -InvID -CustID -InvDate -Job Address -Tipping -Paid	Table InvoiceItems -InvID -Description -Materials -Labour

CustID relates back to a table with customer details, dates will automatically be today's date at time of creating the record. "Items" tables allows for multiple items to be quoted and invoiced within a single quote or invoice. Each item needs to be converted. "Paid" will automatically be yes/no field that defaults as "no".
Once a quote has been converted into a new invoice, the invoice number will be used to 1) state that the quote was converted to invoice and 2) link the quote to the created invoice (for ease of navigating)

This will all be done through forms. Any help will be appreciated.

 

[pbaldy]

I probably would have a single table with a "status" field, or perhaps a second table with a history of the statuses. To do what you describe though, create an append query that copies the values from one table to the other using the form for a criteria, and execute that query from your button.

 

[Pat Hartman]

We don't have anywhere near enough information and it sounds like you don't either. Start with the quoting process.
1. Could multiple quotes be created?
2. Do you need to maintain a history of each quote? If so, you need a process to copy a quote into a new quote so it can be modified. To complete this process, you need a QuoteSubmittedDT so you can "complete" the quote to prevent further changes.

Is there an order step or does a quote go right to invoicing if accepted? In this case, you would usually not have a child table with the details. the Invoice would link to the quote and the details would be obtained from there if necessary therefore no "copy" process is necessary. The tblInvoice just includes the additional information such as InvDT, TaxAmt, PaidDT, PaidAmt if you accept partial payments (which leads to other tables and processes)