Access subform will not refresh

[Akagami]

Hello All,

I have a bit of a problem. I have a main form with two subforms. The subforms are linked to the main form my two fields. The main form and subforms are each based upon a different query.

I have written VBA code that filters the SQL statements of the subforms based upon the value of the the REV field on the main form. When the user changes to different record on the main form, the subforms are supposed to change accordingly.

The problem is that the subforms on the main form will not refresh each time the user chooses a new record on the main form. If I change to a new record on the main form and then open the subform outside of the main form, the correct information is shown. The problem however is that the subform shown on the main form does not automatically refresh. I have tried numerous strategies, but to no avail. If I am on a record in the main form, and I exit the main form, and then re-open the main form, the subform data on the main form will reflect that of the record I was on prior to previously exiting the main form.

Here is some of the code that I have tried:

Me![qryRevText_ sf].Form.Requery
Me![qryAdminRevText_sf].Form.Requery
Me.Refresh

or

Me.[qryRevText subform].Form.Refresh
Me.[qryAdminRevText subform].Form.Refresh

or

DoCmd.Close acForm, "qryRevText subform"
DoCmd.Close acForm, "qryAdminRevText subform"
Please help me if you can. I would appreciate it greatly.
Thank you in advance.

Akagami

 

[johndoomed]

Hi,

I've had the same problem. You need to use Requery instead of Refresh.

This should solve your problems!

If you need more help just search this forum for Requery. Many posts on this problem.

 

[Akagami]

Thanks for the speedy reply, Johndoomed. I will read the posts you suggested. I have tried both Refresh and Requery, both to no avail. I will keep on trying though.

Akagami

 

[johndoomed]

Hmm..

This is the code I use.

Forms!MainFormName!SubFormName.Form.Requery

Try this code with a button, if it works you need to find the event to run it. Or with your save button if you use one.

 

[FireStrike]

I ran into this exact same problem, and nothing worked. the way I got around it was to use something similar to the following code.

dim temp as string
temp = ""
temp = subForm.SourceObject
subForm.SourceObject = temp

 

[Akagami]

Firestrike,

Thanks for the info. I don't quite understand what you wrote. If I want to refresh my subforms, why would I create a text string? Thanks.

Akagami

 

[FireStrike]

First just paste those lines after your sql statments. Change the word subForm to the actual name of your subform. It will work.

Reason:

There is a bug in access that will not requery your subform. If you try to just do something like subForm.SourceObject=subForm.sourceObject, it kicks out an error. However the compiler will allow you to set a SourceObject to a variable and then back. The string is the least memory intensive variable that will hold a sourceobject(a variant will do as well, but will use more memory).

It is a very strange solution to a very unusual problem. But with microsoft finding strange solutions to bugs, is a common place

 

[Akagami]

Thanks Firestrike. Your code worked like a charm. I would have never figured that out on my own.

Akagami

 

[CEO76]

Thanks

FireStrike,

This short line of code does actually work really well. Appreciate your tips. It makes my day.

Thanks

CEO76

 

[ceez]

After I use this solution all textboxes from the main form contain "#Error". They use a value from the subform. Why does this happen?

 

[ceez]

I reset the Control Source of the textbox.

 

[evanscamman]

When a query uses a control on a form to filter results, the value of that control is 'hard-coded' into the query when it loads. A requery does not go back to your form and look for a new value, it uses the existing one and checks the table for new data.

Instead of requery try:

Me.RecordSource = Me.RecordSource

This basically 'reboots' the query.

HTH,
Evan

 

[noppojp]

I've spent more time on this than i'd like to ever admit to anyone. But, when i found that the following weren't populated, and populated them, it worked perfectly. You must populate the Link Master Fields and Link Child Fields in the Properties sheet.

In order to see the Properties sheet that has the above 2 fields, bring up the property sheet of the subform. Click the border of the subform. Click the Data tab, and you'll see the following:

Link Master Fields
Link Child Fields

Populating those fields fixed my problem!!! Don't forget to go back and delete all those useless *.Requery statements you put all over the place.

Good luck!!

 

[Gatorman]

I Did this but while it will filter the subform, it does not requery or refresh if the form is closed then reopened.

I've spent more time on this than i'd like to ever admit to anyone. But, when i found that the following weren't populated, and populated them, it worked perfectly. You must populate the Link Master Fields and Link Child Fields in the Properties sheet.

In order to see the Properties sheet that has the above 2 fields, bring up the property sheet of the subform. Click the border of the subform. Click the Data tab, and you'll see the following:

Link Master Fields
Link Child Fields

Populating those fields fixed my problem!!! Don't forget to go back and delete all those useless *.Requery statements you put all over the place.

Good luck!!

 

[QuietDude]

I have the same issue.
FireStrike, where exactly do I need to put this code and what other SQL statement would I need to make sure the subform gets updated?
My subform (call it subform1) is based on a query that has a Totals field (Sum), which does not update as I change data in subform2. However, when I move to a new record and go back to previous, the data is updated.
I've tried different things like putting an unbound text field in the footer to calculate the sum and then show it on the main form, but nothing works.
Any suggestions?

dim temp as string
temp = ""
temp = subForm.SourceObject
subForm.SourceObject = temp

 

[Darrylles]

Bit late, but....

I seemed to have an identical problem to the above.

My solution: I had set one of my sub forms (form not container) 'data entry' properties to True.

Setting it back to False fixed this.

ATB,

Darrylle

 

[lulrich]

Thank you for your explanation and then the fix. I spent a lot of time with this. I was familiar with requerying and have used it many times, but this one did not work. THe code that I used for getting this to work from one subform to another is here in case another might find it useful.

Forms.MainForm.Subform.Form.RecordSource = Forms.MainForm.Subform.Form.RecordSource

 

[jmkeuning]

I have the same issue.
FireStrike, where exactly do I need to put this code and what other SQL statement would I need to make sure the subform gets updated?
My subform (call it subform1) is based on a query that has a Totals field (Sum), which does not update as I change data in subform2. However, when I move to a new record and go back to previous, the data is updated.
I've tried different things like putting an unbound text field in the footer to calculate the sum and then show it on the main form, but nothing works.
Any suggestions?

dim temp as string
temp = ""
temp = subForm.SourceObject
subForm.SourceObject = temp

I just came out of lurking to thank you for this. I have literally been working on this problem for three hours.

I ended up using this on my main form's On Current event:

Private Sub Form_Current()

Forms.[MainForm].[SubForm].Form.RecordSource = Forms.[MainForm].[SubForm].Form.RecordSource

Dim temp As String
temp = ""
temp = frmTaskProPro.SourceObject
frmTaskProPro.SourceObject = temp

End Sub

 

[JustinS1981]

This is what worked for me.

1 | Go to the properties for the subform/subreport
2 | Select the Data Tab
3 | Set "Filter On Empty Master" to No

Hope this helps!