I have created an Code that Copy the Estimate Record and its Estimate Detail Child Records into the Invoice Table, and then Open the Invoice Form to finish the Invoice Details. But I fiquire out why the Invoice Form does not open up to the New Invoice ID.
Here's the Code:
DetailsPrivate Sub BtnCreateInvoicefromEstimate_Click()
Dim db As DAO.Database Dim rsEstimate As DAO.Recordset Dim rsEstimateDetail As DAO.Recordset Dim rsInvoice As DAO.Recordset Dim rsInvoiceDetail As DAO.Recordset Dim strSQL As String
' Open the current database Set db = CurrentDb
' Open the Estimate recordset Set rsEstimate = db.OpenRecordset("SELECT * FROM EstimateT WHERE EstimateID = " & Me.EstimateID)
' Check if the Estimate recordset is not empty If Not rsEstimate.EOF And Not rsEstimate.BOF Then ' Open the Estimate Detail recordset Set rsEstimateDetail = db.OpenRecordset("SELECT * FROM EstimateDetailT WHERE EstimateID = " & Me.EstimateID)
' Add a new record to the Invoices Table Set rsInvoice = db.OpenRecordset("InvoiceT") rsInvoice.AddNew rsInvoice!InvoiceTitle = rsEstimate!EstimateName rsInvoice!ClientID = rsEstimate!ClientID rsInvoice!PropertyID = rsEstimate!PropertyID rsInvoice!EstimateID = rsEstimate!EstimateID rsInvoice!ContractID = rsEstimate!ContractID rsInvoice!PaymentTermsID = rsEstimate!PaymentTermsID ' Add more fields from the estimate record to the invoice record as needed
' Save the new Invoice Record rsInvoice.Update
' Loop through the Estimate Detail records and copy them to the Invoice Detail Table Do Until rsEstimateDetail.EOF
Set rsInvoiceDetail = db.OpenRecordset("InvoiceDetailT") rsInvoiceDetail.AddNew rsInvoiceDetail!InvoiceID = rsInvoice!InvoiceID rsInvoiceDetail!ProductID = rsEstimateDetail!ProductID rsInvoiceDetail!ProductName = rsEstimateDetail!ProductName rsInvoiceDetail!Quantity = rsEstimateDetail!Quantity rsInvoiceDetail!UnitPrice = rsEstimateDetail!UnitPrice rsInvoiceDetail!ProductDiscountPercentage = rsEstimateDetail!ProductDiscountPercentage rsInvoiceDetail!ProductSalesTaxPercentage = rsEstimateDetail!ProductSalesTaxPercentage ' Add more fields from the Estimate Product Detail record to the Invoice Product Detail record as needed
rsInvoiceDetail!ServiceID = rsEstimateDetail!ServiceID rsInvoiceDetail!ServiceName = rsEstimateDetail!ServiceName rsInvoiceDetail!Hours = rsEstimateDetail!Hours rsInvoiceDetail!Rate = rsEstimateDetail!Rate rsInvoiceDetail!BudgetedHours = rsEstimateDetail!BudgetedHours rsInvoiceDetail!ServiceDiscountPercentage = rsEstimateDetail!ServiceDiscountPercentage rsInvoiceDetail!ServiceSalesTaxPercentage = rsEstimateDetail!ServiceSalesTaxPercentage ' Add more fields from the Estimate Service Detail record to the Invoice Service Detail record as needed
' Close recordsets rsEstimateDetail.Close rsInvoiceDetail.Close
' Inform the user that the Invoice has been generated MsgBox "Invoice generated successfully!", vbInformation
' Open the Invoice Form DoCmd.OpenForm "InvoiceF", , , "InvoiceID = " & rsInvoice!InvoiceID, acFormEdit Else ' Inform the user if the Estimate Record is not found MsgBox "Estimate record not found!", vbExclamation End If
' Close the Estimate recordset rsEstimate.Close rsInvoice.Close
' Clean up Set rsEstimate = Nothing Set rsEstimateDetail = Nothing Set rsInvoice = Nothing Set rsInvoiceDetail = Nothing Set db = Nothing
End Sub
Sami Shamma 12 months ago
You can do that when you issue the command to open the form.
DoCmd.OpenForm "YourFormF",,,,acFormAdd
James HopkinsOP 12 months ago
Thanks Sami, I think I figured it out. Here's what I came up with.
Details Private Sub BtnCreateInvoicefromEstimate_Click()
Dim db As DAO.Database
Dim rsEstimate As DAO.Recordset
Dim rsEstimateDetail As DAO.Recordset
Dim rsInvoice As DAO.Recordset
Dim rsInvoiceDetail As DAO.Recordset
Dim strSQL As String
Dim InvoiceID As Long
' Open the current database
Set db = CurrentDb
' Open the Estimate recordset
Set rsEstimate = db.OpenRecordset("SELECT * FROM EstimateT WHERE EstimateID = " & Me.EstimateID)
' Check if the Estimate recordset is not empty
If Not rsEstimate.EOF And Not rsEstimate.BOF Then
' Open the Estimate Detail recordset
Set rsEstimateDetail = db.OpenRecordset("SELECT * FROM EstimateDetailT WHERE EstimateID = " & Me.EstimateID)
' Add a new record to the Invoices Table
Set rsInvoice = db.OpenRecordset("InvoiceT", dbOpenDynaset)
rsInvoice.AddNew
rsInvoice!InvoiceTitle = rsEstimate!EstimateName
rsInvoice!InvoiceDate = Date
rsInvoice!ClientID = rsEstimate!ClientID
rsInvoice!PropertyID = rsEstimate!PropertyID
rsInvoice!EstimateID = rsEstimate!EstimateID
rsInvoice!ContractID = rsEstimate!ContractID
rsInvoice!PaymentTermsID = rsEstimate!PaymentTermsID
' Add more fields from the estimate record to the invoice record as needed
' Save the new Invoice Record
rsInvoice.Update
' Get the ID of the newly created invoice
InvoiceID = DMax("InvoiceID", "InvoiceT")
' Loop through the Estimate Detail records and copy them to the Invoice Detail Table
Do Until rsEstimateDetail.EOF
Set rsInvoiceDetail = db.OpenRecordset("InvoiceDetailT", dbOpenDynaset)
rsInvoiceDetail.AddNew
rsInvoiceDetail!InvoiceID = InvoiceID
rsInvoiceDetail!ProductID = rsEstimateDetail!ProductID
rsInvoiceDetail!ProductName = rsEstimateDetail!ProductName
rsInvoiceDetail!Quantity = rsEstimateDetail!Quantity
rsInvoiceDetail!UnitPrice = rsEstimateDetail!UnitPrice
rsInvoiceDetail!ProductDiscountPercentage = rsEstimateDetail!ProductDiscountPercentage
rsInvoiceDetail!ProductSalesTaxPercentage = rsEstimateDetail!ProductSalesTaxPercentage
' Add more fields from the Estimate Product Detail record to the Invoice Product Detail record as needed
rsInvoiceDetail!ServiceID = rsEstimateDetail!ServiceID
rsInvoiceDetail!ServiceName = rsEstimateDetail!ServiceName
rsInvoiceDetail!Hours = rsEstimateDetail!Hours
rsInvoiceDetail!Rate = rsEstimateDetail!Rate
rsInvoiceDetail!BudgetedHours = rsEstimateDetail!BudgetedHours
rsInvoiceDetail!ServiceDiscountPercentage = rsEstimateDetail!ServiceDiscountPercentage
rsInvoiceDetail!ServiceSalesTaxPercentage = rsEstimateDetail!ServiceSalesTaxPercentage
' Add more fields from the Estimate Service Detail record to the Invoice Service Detail record as needed
' Close recordsets
rsEstimateDetail.Close
rsInvoiceDetail.Close
' Inform the user that the Invoice has been generated
MsgBox "Invoice generated successfully!", vbInformation
' Open the Invoice Form
DoCmd.OpenForm "InvoiceF", , , "InvoiceID=" & InvoiceID, acFormEdit
Else
' Inform the user if the Estimate Record is not found
MsgBox "Estimate record not found!", vbExclamation
End If
' Close the Estimate recordset
rsEstimate.Close
rsInvoice.Close
' Clean up
Set rsEstimate = Nothing
Set rsEstimateDetail = Nothing
Set rsInvoice = Nothing
Set rsInvoiceDetail = Nothing
Set db = Nothing
End Sub
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access TechHelp.
The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.