How to update the subform rows from parent form control (1 Viewer)

[nector]

I have a subform and a parent form, I have put a control for exchange rate such that when I change the exchange rate from the parent form control I expect the corresponding exchange rate control in the subform to update all the line with data. But what is happening now is that only one line of the subform is updated the rest are not. Where am missing it

Here is my code

Private Sub FCRate_AfterUpdate()
If (Forms!frmJournalHeader![sfrmVoucher Subform].Form.FCRate <> Me.FCRate) Then
Forms!frmJournalHeader![sfrmVoucher Subform].Form.FCRate = Me.FCRate
End If
End Sub

 

[ADIGA88]

You can write a function in the subform to update all the related records as follows:

Sub UpdateExchangeRate(Rate As Currency)

    Me.Recordset.MoveFirst
    Do While Not Me.Recordset.EOF
        FCRate = Rate
        Me.Recordset.MoveNext
    Loop
End Sub

then call it from the parent form:

Private Sub Text4_AfterUpdate()
    [sfrmVoucher Subform].Form.UpdateExchangeRate (Me.Text4.Value)
End Sub

this will ensure that only the related records in the subform to the parent form are updated.

 

[nector]

I have tried it is now through an error "Wrong number of argument or invalid property assignment"

Private Sub Text4_AfterUpdate()
    [sfrmVoucher Subform].Form.FCRate_AfterUpdate (Me.FCRate.Value)
End Sub




Public Sub FCRate_AfterUpdate()
 Me.Recordset.MoveFirst
    Do While Not Me.Recordset.EOF
        Me.FCRate = Me.Parent!FCRate
        Me.Recordset.MoveNext
    Loop
End Sub

Where am I making a mistake again

 

[ADIGA88]

what is the line that gives you the error?

 

[nector]

Here is the

Private Sub Text4_AfterUpdate()
[sfrmVoucher Subform].Form.FCRate_AfterUpdate (Me.FCRate.value)
End Sub

 

[ADIGA88]

I think the issue with the name of the function in the subform Public Sub FCRate_AfterUpdate()
it shouldn't have an underscore in it you can make it like "UpdateExchangeRate()":

(Parent Form)

Private Sub FCRate_AfterUpdate()

    If (Forms!frmJournalHeader![sfrmVoucher Subform].Form.FCRate <> Me.FCRate) Then

               Forms!frmJournalHeader![sfrmVoucher Subform].Form.UpdateExchangeRate (Me.FCRate)

    End If

End Sub

(SubForm)

Public Sub UpdateExchangeRate()

    Me.Recordset.MoveFirst

    Do While Not Me.Recordset.EOF
        Me.FCRate = Me.Parent!FCRate
        Me.Recordset.MoveNext
    Loop
End Sub

 

[Pat Hartman]

When you refer to a subform from a main form (or from any other form), you are referring to the subform's CURRENT record whatever it is. If the subform has been scrolled and the pointer is sitting on the 12th record, that record is the current record. If the subform hasn't been scrolled, the current record will default to the first record. If you want to update all the records of the subform, you need to either open a recordset using the subform's recordset clone and loop through updating each record OR preferably run an update query. HOWEVER, the real problem is a flawed table design. If the Exchange rate applies to all child records, then it belongs in the parent record rather than the child record. So you NEVER have to update the child records to change them all.

Then the calculation query needs to join to the parent table to pick up the rate.

Select ParentTable.ExchangeRate * ChildTable.Amt As ConvertedAmt
From ParentTable Inner Join Child table on ParentTable.YourPK = ChildTable.YourFK;

Schema design is critical. Get it right and it will minimize the code you need to write. Get it wrong and you are constantly writing code to overcome a problem.

 

[nector]

Many thanks Pat Hartman!

I wish your comments came earlier it would have served me a lot time. Just for the sake of the readers I would like to bring up this issue to those who have developed a typical accounting package in Microsoft Access like I have done. The issues here are listed below:

(1) How to allow accountants to park their financial journal before going for lunch and come back to it after lunch.
(2) Ensure that, if the exchange rate field on the parent form change also the subform entries change

The problem here was that the changes in the exchange field in the parent form were only updating the first line leaving other lines unchanged. That result into journal imbalance at the trial balance stage , hence causing a nightmare to reconcile.

The cure was to redesign the journal as below:

SELECT tblVoucher.YYDate, tblaccounts.BSID, tblaccounts.AccountCode, tblaccounts.AccountName, tblVoucher.AccountID, ((([tblVoucher].[Dr]-[tblVoucher].[Cr]))*[tblJournalHeader].[FCRate]) AS Total, tblVoucher.Descriptions
FROM tblaccounts INNER JOIN (tblJournalHeader INNER JOIN tblVoucher ON tblJournalHeader.CreateID = tblVoucher.CreateID) ON tblaccounts.AccountID = tblVoucher.AccountID
WHERE (((tblVoucher.Authority)="1"));






Private Sub CboJvEditing_AfterUpdate()
Dim LTAudit As String
LTAudit = Nz(DLookup("Status", "tblJournalHeader", "CreateID =" & Me.CboJvEditing))
Me.Filter = "CreateID  = " & Me!CboJvEditing.value & ""
If (LTAudit <> "") Then
    Beep
    MsgBox "This document is already approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    Me.FilterOn = False
Else
    Me.FilterOn = True
End If
Dim Records As DAO.Recordset


    Set Records = Me![sfrmVoucher Subform].Form.RecordsetClone


    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
              
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
End Sub

 

[Pat Hartman]

I wish your comments came earlier it would have served me a lot time.

Six hours wasn't early enough

Glad to see you moved the rate to the header.