Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-11-2018, 11:20 PM   #1
Prodriguez
Newly Registered User
 
Join Date: May 2013
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
Prodriguez is on a distinguished road
Question Update calculated field in subform based on Parent form Combo Box

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.
Attached Files
File Type: accdb Example.accdb (548.0 KB, 19 views)

Prodriguez is offline   Reply With Quote
Old 12-11-2018, 11:39 PM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Update calculated field in subform based on Parent form Combo Box

Use an Update query.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 12-11-2018, 11:43 PM   #3
Prodriguez
Newly Registered User
 
Join Date: May 2013
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
Prodriguez is on a distinguished road
Re: Update calculated field in subform based on Parent form Combo Box

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

With regards
PAR

Prodriguez is offline   Reply With Quote
Old 12-12-2018, 12:34 AM   #4
Eugene-LS
Newly Registered User
 
Join Date: Dec 2018
Posts: 19
Thanks: 6
Thanked 3 Times in 3 Posts
Eugene-LS is on a distinguished road
Re: Update calculated field in subform based on Parent form Combo Box

Quote:
Originally Posted by Prodriguez View Post
... 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
Attached Files
File Type: zip Example_v02.zip (33.8 KB, 16 views)

Last edited by Eugene-LS; 12-12-2018 at 12:50 AM.
Eugene-LS is offline   Reply With Quote
Old 12-12-2018, 12:34 AM   #5
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Update calculated field in subform based on Parent form Combo Box

Look at the code behind cmboTaxRAte After Update event.
Attached Files
File Type: accdb Example-13.accdb (520.0 KB, 20 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Prodriguez (12-12-2018)
Old 12-12-2018, 12:46 AM   #6
Eugene-LS
Newly Registered User
 
Join Date: Dec 2018
Posts: 19
Thanks: 6
Thanked 3 Times in 3 Posts
Eugene-LS is on a distinguished road
Re: Update calculated field in subform based on Parent form Combo Box

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
Eugene-LS is offline   Reply With Quote
The Following User Says Thank You to Eugene-LS For This Useful Post:
Prodriguez (12-12-2018)
Old 12-12-2018, 05:46 PM   #7
Prodriguez
Newly Registered User
 
Join Date: May 2013
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
Prodriguez is on a distinguished road
Thumbs up Re: Update calculated field in subform based on Parent form Combo Box

Quote:
Originally Posted by JHB View Post
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 is offline   Reply With Quote
Old 12-12-2018, 05:54 PM   #8
Prodriguez
Newly Registered User
 
Join Date: May 2013
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
Prodriguez is on a distinguished road
Thumbs up Re: Update calculated field in subform based on Parent form Combo Box

Quote:
Originally Posted by Eugene-LS View Post
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

Prodriguez is offline   Reply With Quote
Reply

Tags
combo box , parent form , sub-form , update records

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update a main form from a calculated field in a subform fabio Forms 4 08-22-2013 01:50 PM
Filter dropdown of subform based on field in parent form. wchelly Queries 6 02-19-2013 08:48 AM
Update subform field based on primary form field sgagne Forms 9 08-26-2012 03:50 AM
Field on subform update based on field from main form Mark Patterson Forms 1 01-24-2012 05:47 PM




All times are GMT -8. The time now is 07:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World