Update calculated field in subform based on Parent form Combo Box (1 Viewer)

Prodriguez

New member
Local time
Today, 22:46
Joined
May 1, 2013
Messages
11
Dear all,

I have attached an example of a database that has an invoice form linked to a invoice line sub-form.

Once the amount is updated in the invoice line the tax amount is calculated by multiplying the amount with the tax rate in the combo box of the parent form. So this works very well when I am adding one line at a time in the subform.

By using VBA I would like to update the tax amount field of all the records linked to the current invoice being displayed if I decide to update later the tax rate in the combo box (after update event in the combo box)

Thank you in advance for your comments.
 

Attachments

  • Example.accdb
    548 KB · Views: 83

JHB

Have been here a while
Local time
Today, 15:46
Joined
Jun 17, 2012
Messages
7,732
Use an Update query.
 

Prodriguez

New member
Local time
Today, 22:46
Joined
May 1, 2013
Messages
11
Thanks JHB for your prompt reply but I was looking to find a solution using VBA, if possible.

With regards
PAR
 

Eugene-LS

Registered User.
Local time
Today, 17:46
Joined
Dec 7, 2018
Messages
481
... Once the amount is updated in the invoice line the tax amount is calculated by multiplying the amount with the tax rate in the combo box of the parent form ...
Code:
Private Sub cmboTaxRAte_AfterUpdate()
'
Dim strSQL As String
Dim rst As DAO.Recordset
Dim cTaxRate As Currency

    If Me!cmboTaxRAte.ListIndex = -1 Then
        cTaxRate = 0
    Else
        cTaxRate = Me!cmboTaxRAte
    End If
    
    strSQL = "SELECT Amount, TaxAmount FROM tbl_InvoiceLine WHERE InvoiceID=" & Me!InvoiceID
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)  '
'
    With rst
        Do Until .EOF = True '
            .Edit
            !TaxAmount = Nz(!Amount) * cTaxRate
            .Update
            .MoveNext
        Loop
    End With
    
    On Error Resume Next
    Me!Child17.Form.Requery
    
    rst.Close
    Set rst = Nothing

End Sub
 

Attachments

  • Example_v02.zip
    33.8 KB · Views: 86
Last edited:

JHB

Have been here a while
Local time
Today, 15:46
Joined
Jun 17, 2012
Messages
7,732
Look at the code behind cmboTaxRAte After Update event.
 

Attachments

  • Example-13.accdb
    520 KB · Views: 93

Eugene-LS

Registered User.
Local time
Today, 17:46
Joined
Dec 7, 2018
Messages
481
And shorter way :

Code:
Private Sub cmboTaxRAte_AfterUpdate()
'
Dim strSQL As String
Dim cTaxRate As Currency, sVal$

    If Me!cmboTaxRAte.ListIndex = -1 Then
        cTaxRate = 0
    Else
        cTaxRate = Me!cmboTaxRAte
    End If
    sVal = Replace(CStr(cTaxRate), ",", ".") ' to SQL format
    
    strSQL = "UPDATE tbl_InvoiceLine SET tbl_InvoiceLine.TaxAmount = Nz([Amount]) * " & sVal & _
            " WHERE InvoiceID=" & Me!InvoiceID
    'Debug.Print strSQL
    
    CurrentDb.Execute strSQL
    Me!Child17.Form.Requery
    
End Sub
 

Prodriguez

New member
Local time
Today, 22:46
Joined
May 1, 2013
Messages
11
And shorter way :

Code:
Private Sub cmboTaxRAte_AfterUpdate()
'
Dim strSQL As String
Dim cTaxRate As Currency, sVal$

    If Me!cmboTaxRAte.ListIndex = -1 Then
        cTaxRate = 0
    Else
        cTaxRate = Me!cmboTaxRAte
    End If
    sVal = Replace(CStr(cTaxRate), ",", ".") ' to SQL format
    
    strSQL = "UPDATE tbl_InvoiceLine SET tbl_InvoiceLine.TaxAmount = Nz([Amount]) * " & sVal & _
            " WHERE InvoiceID=" & Me!InvoiceID
    'Debug.Print strSQL
    
    CurrentDb.Execute strSQL
    Me!Child17.Form.Requery
    
End Sub

Dear Eugene-LS,

Thank you for the two responses submitted under this thread.

The code provided has become very useful to my current project.

Thanks for quick turn around.

With regards
PAR
 

Users who are viewing this thread

Top Bottom