VBA Code to copy fieds from one table into another as new record

[Freddie81]

Hi everybody. Im a new access designr or want to be ut Im battling with a problem.



Iam creating a database or desktop app for my business that does qoutes and invoices for customers. I have a table for Qoutes with a Qoutesdetails table for the details. Then I have a table Invoices and a table for the details called InvoicesDetails. The idea is to have a datasheet showing all th qoutes and If the user clicks the qoute number it opens a new form showing all the details for the woute. So dar so good.

I would like to create a button th form for alowing th user to accept the qoute. When clicked it should copy every field in the qoute table and qoute dtails table t the invoice and invoice detai table as a new record so a new inocie is created. After the new invoice is created the original qoute must be deleted.

I wan to keep the tables seperate ...qoutes for qoutes and invoices for invoices.

[ranman256]

You don't need code, you want an append query.
design a select query that pulls the data from the selected record, then adds it to the target table.

...where ID = forms!myForm!txtID

[Freddie81]

That makes sense...Thank you Ranman256..i will try that

[Freddie81]

I se whn I change the select query to a append query I can only apped one table. So copying the fieds from Table1 (Qoutes) will work to copy to table 2(Invoices) but how do I get the qutes etails to move to invoice details table or do I create a second query? How do I run both queries same time?

[Freddie81]

Ok so I used vba instead and it works perfectly to a point.

The 4 fields from QoutesT copies exactly to InvoicesT.
I'm not getting the subform fields to copy tho. QoutesDetailsSubform is not copying to Invoicedetailssubform and I get an error that access cant find the field Im trying to copy i I use the same code as for the main form.

One of the fields in the subform is a combobox with 3 columns (Stock code,description and price)

[June7]

Reference to subform through the subform container control. I always name control different from the object it holds, like ctrQteDet and ctrInvDet.

If code is behind the main form:

Me.ctrQteDet!Code = Me.ctrInvDet!Code.Column(1)

Or run two append queries.

Either way, if you have defined relationships, have to make sure the parent record is first committed to table.

Note you have misspelled Quote in table and field names. Might want to fix that in table.

[CJ_London]

same question asked here https://www.accessforums.net/showthread.php?t=71900

[Freddie81]

freddie.zipfreddie.zip

[Freddie81]

You will see you hve to have form MainF running to get to the other fors. wo When you open a qoute and click the red button it open invice form and copies the data but it errors at the subform copy section

[June7]

As already advised, have to refer to subform through subform container control and commit the parent record to table before adding dependent record. Code is a number type field. Need to save ServicesID not Code text.

Private Sub AcceptBtn_Click()
DoCmd.OpenForm "InvoiceDataEntryF", acNormal, , , acFormAdd, acWindowNormal
Forms!InvoiceDataEntryF![CustomerID] = Me![CustomerID]
Forms!InvoiceDataEntryF![VAT] = Me![VAT]
Forms!InvoiceDataEntryF![InvoiceNotes] = Me![QouteNotes]

DoCmd.RunCommand acCmdSaveRecord

Forms!InvoiceDataEntryF.InvoicDetailsTSubform!Code = Me![QouteDetails Subform]!Code
Forms!InvoiceDataEntryF.InvoicDetailsTSubform![Qty] = Me![QouteDetails Subform]![Qty]
Forms!InvoiceDataEntryF.InvoicDetailsTSubform![IPrice] = Me![QouteDetails Subform]![QPrice]
End Sub

Again, suggest you correct spelling of Quote everywhere. Note you have a space instead of T in [QouteDetails Subform]. Advise no spaces and should be consistent in naming convention.

Missing an e in [InvoicDetailsTSubform]. Most experienced developers would place object identifier as a prefix in name. Common convention uses 3 letters. Consider shorter names. Ex: tblInvDet, frmInvDataEntry, qryCustomers.

There is nothing in code to prevent creating multiple invoices for the same quote. There is no way to know which quote an invoice was created from.

[Freddie81]

You make very good points June7...thank you for the advice. I missed some of those spelling mistakes...my bad. I will fix them immediately.

I'm not worried about the sae qoute making multiple invoices as Im planning to delete the origanl qoute once the invoice has been saved. Tha will be m next tep to try.

[June7]

However, the code you have accommodates only one detail record for each invoice. Your quotes have only 1 related detail record. If these tables are truly 1-to-1 relationship, why bother with the detail tables?

Deleting records should be a rare event. Why have separate Quotes and Invoices tables? Sure, if a quote never gets confirmed as an invoice there will be a lot of empty fields. It is a balancing act between data normalization and ease of data entry/output.

So unless there can be multiple invoices generated from a quote, you could simplify data structure and eliminate a bunch of code.

[Freddie81]

That is a valid point abut the empty spaces. To restrit the qoute from being processed more than once what about adding a counter to the qoute table so when I hit accept button it changes the 0 to 1 and checks everytime the button is pressed if a value =1...if so then end the procedure with a msgbox saying th qoute ghas been invoiced already? That way I dont have t delete the origanl qoute........

[Freddie81]

Hi again June7...I just trie the code you wrote or me and I get error messages. First one says access cant find the field QouteDetails Subform. So I renamed the form and updated the code. Now if I run it i get the error that says The expression you entered refers to an object that is closed or doesnt exist. I assume it means QouteDetailsSubForm is not open? I used DoCmd.OpenForm "InvoicDetailsTSubform", acNormal, , , acFormAdd, acWindowNormal and trie again...now getting eror that the openform opertion has been cancelled. I'm totally lost now lol.

[June7]

Can't 'open' a form used as a subform.

The code I posted is exactly as I have entered it into your db. It works. The code opens InvoiceDataEntryF. Why would you change it to open subform?

Yes, an IsInvoiced flag field would be appropriate.

Add as last line in the procedure to set the field: Me!IsInvoiced = True

Could apply filter to form so those records don't load onto form.

However, if there is no connection between quote and invoice maintained, retaining the quote serves little purpose. Still think you could save yourself a lot of grief by not have Quotes and Invoices tables, just one table and call it Jobs or Projects.