Subform not showing records sometimes

[hilltop804]

Hello Everyone,

I have a subform that displays records from a query. Usually, there is no issue. Sometimes, though, the subform stays blank for no apparent reason.

Observations:
-Open form and select relevant information (subform stays blank)
-Open query and 1 record is present
-Go form layout view and turn "Data Entry" on, then back off
(The form appears to refresh when this setting is changed and the subform is displaying the 1 record properly now)
-Close form, re-open, and select the same information as before (subform, again, stays blank)

Again, the subform usually displays the records just fine. It's only sometimes that it has this blank issue even though records are present in its source query. I've tried refresh and requery macros on the form and subform, but that doesn't get the information to show up (though, switching "Data Entry" on and off does).

I feel like I've overlooked something simple, but I can't seem to figure out what it is. I would greatly appreciate any advice anyone could give.

Thanks,
Brad

 

[burrina]

Do you have a lot of code for that form? On what event is the code firing if so?
Debugging is a process of elimination, so start disabling code and see if that makes a difference.

 

[hilltop804]

IMO, there's not a lot of code on the form (I try to keep them as light as possible for efficiency). There are 3 list boxes and when the last one is selected, there is a requery on the subform in the "after update" event.

I tried changing various settings, but it just doesn't make sense to me why only sometimes the records don't show up. The information is present in the source table, present in the linked query, but it just won't show up.

Side note: Is there a stronger refresh than form.refresh or control.requery? I ask because changing the "data entry" setting appears to refresh the form and cause the records to appear.

 

[burrina]

Me.YourFormName.Requery Try that and see if it helps!

 

[hilltop804]

Thanks, but that didn't seem to do it.

 

[burrina]

Sounds like their is something going on we can't see. You could try;

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryYourQueryName", acViewNormal, acHidden
DoCmd.Close acQuery, "qryYourQueryName"
DoCmd.SetWarnings True

 

[hilltop804]

Thanks, but that didn't fix it either.

Ultimately, I solved my problem by adding another button that copies * to a table and linking the subform to the table instead of the query. I don't have display issues anymore.

Still, I wish I could figure out what the deal with this was.

 

[burrina]

Glad you got it sorted out! Good Luck With Your Project!