Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > < TechHelp
Back to Access TechHelp    Comments List
Upload Images   Link   Email  
Create Invoice from Esimate
James Hopkins 
     
12 months ago
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
            
            rsInvoiceDetail.Update
            rsEstimateDetail.MoveNext
        Loop

        ' 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 Hopkins OP 
     
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
        
            rsInvoiceDetail.Update
            rsEstimateDetail.MoveNext
        Loop

        ' 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

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.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/15/2025 2:19:57 PM. PLT: 1s