Update calculated field in subform based on Parent form Combo Box

[Prodriguez]

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.

 

[JHB]

Use an Update query.

 

[Prodriguez]

Thanks JHB for your prompt reply but I was looking to find a solution using VBA, if possible.

With regards
PAR

 

[Eugene-LS]

... 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 ...

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

 

[JHB]

Look at the code behind cmboTaxRAte After Update event.

 

[Eugene-LS]

And shorter way :

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]

Use an Update query.

Dear JHB,

Thank you for this prompt response it is exactly what I was looking for.

It was very helpful
PAR

 

[Prodriguez]

And shorter way :

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