Parent / Sub - refresh the sub


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


pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 .
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 Access MVP)Database ArchitectCommented:
"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 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 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:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.