Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

Parent / Sub - refresh the sub


I am trying to refresh the sub form.
Is the below the correct?


2 Solutions
pdvsaProject financeAuthor Commented:
The parent is frmLetterOfCredit_Cont
Rey Obrero (Capricorn1)Commented:
remove the .Form


why not just use .Requery or .Recalc
pdvsaProject financeAuthor Commented:
Requery seems to be the only one that does not give me an error like below.  
However, it does not requery the subform.  

"Object does not support this property or Method"  runtime error 438

Do you have another idea to requery/refresh/recalc the subform?  
I am placing the code in the frmLetterOfCredit_Cont on a fields AfterUpdate event: Private Sub Amount_AfterUpdate()

I want the calculation in the subform subParticipatingBanks to update based on the amended amount in the parent frmLetterOfCredit_Cont .
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Rey Obrero (Capricorn1)Commented:
make sure that you use the value displayed in the "Name" property of the subform

open the form "frmLetterOfCredit_Cont" in design view and select the subform control by clicking on the outer edge of the control, then hit F4
select the ALL tab and view what is on the "Name" property, use that on the line

pdvsaProject financeAuthor Commented:
CAp:  It seems as though the name is correct?  Maybe after seeing the screenprint you will have another idea?

pdvsaProject financeAuthor Commented:
maybe if the form is in the footer it makes a difference?
Rey Obrero (Capricorn1)Commented:
so what is the problem with .requery ?
Refresh will commit data you have entered in your form to the underlying table.
Requery will make your form 'current' after editing, adding or deleting records that you may have added through code in the background or direct data entry into the table (ie: changes that were made sneakily in the background, not through the form)

Where is the code being run from?

This should work:

If the code is being run from the mainform, you can also try this:

Also, have you tried requerying the main form if any data has changed that affects that?

Finally, if any changes you have made in the background are recordset based off of the subform's data, you should:
1. Refresh the subform before executing the recordset code
2. Requery the subform after the recordset code has completed

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Refresh will commit data you have entered in your form to the underlying table."
I don't think 'commit' (aka Save) is the intent of Refresh.  Per Help File:

"The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment."

"The Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. "

Whereas Refresh will effect a 'save', I don't think that is the intent ... just an FYI.

pdvsaProject financeAuthor Commented:
Cap:  requery does not seem to give me an error but nothing happens.

The code is running in the Parent form on the afterupdate of [Amount]

maybe I can put a where condition with this
 Forms!frmLetterOfCredit_Cont.subParticipatingBanks.Requery  WHERE the LCID = the LetterOfCreditID of the Parent.  

I think you know what I mean.  Or requery the calculated field CalcAmount on  subParticipatingBanks (this is the field I want to recalculate on the AFterUpdate Event in the Parent)
this is the formula in CalcAmount...it points to the Parent frmLetterOfCredit_Cont
=[Percent]*DLookUp("[Amount]","tblLetterOfCredit","[LetterOfCreditID] = " & [Forms]![frmLetterOfCredit_Cont]![LetterOfCreditID])

maybe it is clearer now...let me know.  I am certain the names are correct.

Rey Obrero (Capricorn1)Commented:

try this

pdvsaProject financeAuthor Commented:
that was not it either.

Here is my code in the AfterUpdate of Amount.
Maybe you see something there that is causing the issue...
Private Sub Amount_AfterUpdate()

        Dim strSQL As String
 DoCmd.RunCommand acCmdSaveRecord
  CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError

  strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterOfCreditID, EndUserID, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
  Debug.Print strSQL
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

    MsgBox "Dont forget to double click this field and enter in the reason why the LC Amount changed.", vbInformation

    If IsNull(Me!Currency) Then
        MsgBox "Dont forget to enter the Currency", vbInformation
    End If
End Sub
Rey Obrero (Capricorn1)Commented:
what is the RecordSource of subform "subParticipatingBanks" ?

how about placing these lines


just before the Msgbox
pdvsaProject financeAuthor Commented:
<RecordSource of subform "subParticipatingBanks" ?
SELECT tblBanks_Participating.* FROM tblBanks_Participating;

well that probably does not help...here is all fields

SELECT tblBanks_Participating.BankEnterID, tblBanks_Participating.BankID, tblBanks_Participating.LCID, tblBanks_Participating.BankType, tblBanks_Participating.Comments
FROM tblBanks_Participating;

I also tried moving the Forms! code to before the msgbox but to no avail.  Same issue...nothing happens

Maybe the records source will give you an idea...

Mike EghtebasDatabase and Application DeveloperCommented:
It looks odd, but should do the job:

pdvsaProject financeAuthor Commented:
Ok i will try that and let you know... Thanks
If issue is not resolved.
Try to reporduce it using a sample database with a simple form and subform. Attach the sample database.
Give instructions on what to do and to expect what.
pdvsaProject financeAuthor Commented:
it was a  combination Cap's setfocus and eghtebas record source
works great now!  thx

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now