Subform not showing

[UT227]

I have a form with 4 subforms. The subforms are not linked to the form nor each other. They are based on queries. If the query results are completely null, the subform will not appear on the form. I need it to appear whether or not it has any information on it. In the can shrink, I have it set to NO. The can grow is set to YES. How can I get them to show even if there is no information on them?

[June7]

I did a test. My subform still shows.

[pbaldy]

It's a known issue that forms/subforms that neither have records nor allow new records will show blank. You can test the HasData property to show something else:

https://msdn.microsoft.com/en-us/vba...roperty-access

[UT227]

Where would I enter that code to make the form visible even with no data?

[pbaldy]

I doubt you can, unless you show a fake record or let it add new records. You could make a textbox Visible that said "no records to show" or whatever.

[UT227]

I'm not sure why it would not be visible

[pbaldy]

Here's another

http://allenbrowne.com/casu-20.html

[UT227]

That answers my question as to why it goes blank. Unfortunately, there doesn't seem to be an answer other than to have a message appear saying that there is no records. Thanks.

[pbaldy]

Are you reading the links? It goes blank because there are no records to display and it doesn't allow new records. Whether any of us like it or not, that's how they work, so we work around it.

[John_G]

What you could do is check the HasData property of the subform, and if it is false, set the AllowAdditions property of the subform to "Yes". The subform will then show a new, but blank, record.

[UT227]

Yes. I read the links. I added the code to the Before Insert, however it still didn't show. I added the code to the On Open and that did work. I have 2 forms that won't show if there is no data. They are based on calculated queries, so there is no need to lock the fields or change the Allow Additions to NO. I have the fields unlocked and Allow Additions are YES. According to the article, that means the forms will not show with no records. I tried the workarounds and all I could get was the "No Records" window to show.

[UT227]

Where is the HasData property of the subform? I can't find it to try it.

[pbaldy]

It's not a property you can change, so I doubt it's in the property sheet. It can be read with code.

[John_G]

Oops! I did a little checking with Google, and found that forms don't have a HasData property - only Reports do.

So, you can use something like this:

  If Me![SubformControlName].Form.Recordset.RecordCount > 0 Then
    Me![SubformControlName].Form.allowAdditions = False
  Else
    Me![SubformControlName].Form.allowAdditions = True
  End If

However:

According to the article, that means the forms will not show with no records. I tried the workarounds and all I could get was the "No Records" window to show.

But if the subform's record source is not updatable (e.g. a Calculated query), then setting AllowAdditions = True won't work (i.e. Access won't do it) because record additions ARE updates.

So it appears that if there are no records to display AND the recordsource is not updatable, there is no workaround - that's just the way Access works.

What you could do is create a textbox with a message in it something like "There are no whatever records for this job number". In design view, put the textbox on top of the subform, and set it's visible property to "No". Add something like this to the After Update event of the Job_Number control of the main form:

  If Me![SubformControlName].Form.Recordset.RecordCount > 0 Then
       Me![SubformControlName].Visible = True
       Me![TextboxName].Visible = False
  Else
  Me![SubformControlName].Visible = False
       Me![TextboxName].Visible = True
  End If

so the subforms don't show at all (even blank) if there is no data for them. The textboxes will tell users will know why they don't see them.