Subform (tabbed page) visible on main form ONLY if subform has data

[mminieri]

My main form has many tabbed subform pages, and a button for NEW record and one for EXISTING record. A user can access a specific existing record from a dropdown combobox that opens from that command button. Selecting a record returns to the MAIN form and populates all fields including those on all subforms. The subform tabs are NOT VISIBLE when the main form is originally loaded. After the selected existing record loads and the main form reopens, I want ALL of those subforms (tabs) that have data in any field to become visible (i.e. available for selection using its tab) on the main form, when it reopens. Remember that none of the subforms will be the CURRENT form so I cannot use the ME/ME! function. Essentially, I need a correct VBA code along the following lines;

IF subform is not null, then subform visible = true


I seem to be having difficulty referencing a subform that is not open/current ... twice. My subform and its controltab have the same name.

I will write the correct code for each subform specifically. My current thought is to put these as an AfterUpdate event in the code behind the action that occurs when the selection is made buy the user from the combobox.

[Nauticalgent]

Give this a look over:

https://bytes.com/topic/access/answe...3-no-data-form

[twinnyfo]

mminieri,

Welcome to Bytes!

For clarification purposes:

My main form has many tabbed subform pages

I'm not sure what this means. Do you have:

• Many "Sub-Forms"?
• One Sub-Form with a Tab Control that has many pages?
• One Tab Control that has Sub-Forms on each page?

Please clarify, as that will help us best provide guidance toward a solution.

Thanks!

[mminieri]

TWINNYFO

[IMG]ftp://minieriassociates.com/public_ftp/Mainform_tabsview.jpg[/IMG]

Posted an image (LINKED above) to view. There are only data fields under each tab.

@twinnyfo

[mminieri]

NAUTICALGENT - The linked issue pertains to opening a subform by pressing the TAB. My post is without opening a subform at all .... only making it visible if it is not null.

[twinnyfo]

We cannot see your image. Many folks cannot access linked images from their work computers. You must upload it under the advances tab.

[NeoPa]

Hi M.

I hope and suspect that Referring to Items on a Sub-Form will give you the help you need for this one. Let us know if that doesn't do it for you.

[NeoPa]

By the way, it's the Page that houses the SubForm that you want to show and hide. Not the SubForm itself.

NB. Page is the correct name for the tabs on a Tab Control for anyone that doesn't realise.

[mminieri]

ftp://minieriassociates.com/public_f...m_tabsview.jpg

[IMG]ftp://minieriassociates.com/public_ftp/Mainform_tabsview.jpg[/IMG]

Sorry but the "ATTACHMENTS" function is not working for me.

[twinnyfo]

I still can't see the images. If you answer the questions in #3, I can probably assist a bit better (and so could others).

[NeoPa]

Hi MMinieri.

You've given an FTP link but it's secured with a name and password so no-one will be able to view it.

The attachments feature is working fine. If you're having difficulty with it and want some assistance please feel free to PM me with your issues and I'll endeavour to explain very clearly how to use it.

In the meantime, please revisit post #3 and answer each point directly.

Also let us know if the article I linked to in post #7 has resolved the issue for you. If not then perhaps you could explain why.

[mminieri]

[IMG]http://www.minieriassociates.com/FORMVIEW_atOnLoad.jpg[/IMG]Here are 2 accessible screen captures;

MAIN PAGE (default on load) shows TABCONTROLS
[IMGNOTHUMB]http://www.minieriassociates.com/Main.jpg[/IMGNOTHUMB]
Example TABBEDPAGE opened;
[IMGNOTHUMB]http://www.minieriassociates.com/TabPage.jpg[/IMGNOTHUMB]
for the later, property sheet uses the term "page".

This is the FORMVIEW at OnLoad
[IMG]http://www.minieriassociates.com/FORMVIEW_atOnLoad.jpg[/IMG]

[mminieri]

NeoPa - thanks. The link you provided does help understand the syntax for referencing the components at issue however, it does not provide a sample VBA for my desired functionality as in the original post. Faster for me to place the 2 images online then deal with the Attachment issue.

[mminieri]

Did not work entirely. The isnull element not working. Makes visible even if no data on that page.


I THINK I'VE SOLVED IT ..... will repost if no. I used,

If (IsNull(SUP1) = True) Then
SUP1.Visible = False
Else
SUP1.Visible = True
End If
End Sub

[NeoPa]

Instead of all that you could just say :

SUP1.Visible = (Not IsNull(SUP1))

[mminieri]

Thanks...That line of code is RED when entered as below;

Private Sub IRselection_AfterUpdate()
DoCmd.OpenForm "INCIDENT_REPORT", acNormal, , "IRnumber = " & Me![IRselection].Value
Me.MAIN.SetFocus
Me.IRlist.Visible = False
SUP1.Visible = (Not IsNull(SUP1)
End Sub

[NeoPa]

Only because you've not followed the golden rule of Copying & Pasting accurately. You've left off the closing parenthesis _)_.

[NeoPa]

Also, if you're seeing it in Red then it looks like you aren't compiling before trying to run your code. You'll benefit from doing that always.

[twinnyfo]

@NoePa (side note with ref to Posts #14 & 15):

Didn't we just cover this somewhere else? ;-)

This method has cleaned up hundreds of lines of my code!

[NeoPa]

Yes indeed, my friend :-)

[mminieri]

SUP1.Visible = (Not IsNull(SUP1)) and nulls are still made visible

[twinnyfo]

Are you trying to hide the Sub-Form itself, or make the Tab Control Page disappear?

Not IsNull(SUP1) implies that SUP1 is a value, but we have been referring to it as if it is a Sub-Form? or a Tab Control Page?

My thought here is that we need a control on the Sub-Form (if it is a Sub-Form on the Tab Control Page) or a control on the Tab Control Page that has the Null Value.

For the below, assume that the Page is named tabPageName

Example - A SubForm on A Tab Control Page):
A text box named txtID which has Parent-Child relationship to Main Form. When no records are found in this Form, txtID will be Null. Then,

Me.tabPageName.Visible = (Not IsNull(txtID))

should produce the desired results.

Again, I am unable to see the pics at work, but I did briefly see them earlier today, but can't recall exactly how they were laid out--nor do I understand the structure, as you have still not answered any of the direct questions I have asked about your main form.

Without knowing the structure of your Form and relationships between the Main Form and the Sub-Forms and Tab Controls, this is all just a guess.

[NeoPa]

MMinieri:
SUP1.Visible = (Not IsNull(SUP1)) and nulls are still made visible

You'll have to explain that one to me. It's certainly not the logic of the code.

Follow it through :

SUP1.Value Is Null
IsNull(SUP1) ==> True
(Not IsNull(SUP1)) ==> False
SUP1.Visible ==> False

How does that make anything visible?

[mminieri]

http://www.minieriassociates.com/Overview.jpg

After CONSIDERABLE experimentation, the problem APPEARS to be that I can not find a VBA expression that will determine if a tab control page IS or IS NOT null! All event actions for the functionality ALWAYS occur for BOTH pages with and without data.

[twinnyfo]

OK - I can see this images from home.

It “appears” that aside from image 4, your Pages do not have Sub-Forms embedded. For your solution, let’s use image 3 for your Page “VEH1”.

When you click on that page’s tab, in its properties, that page should have a Name—take note of it. For our purposes, we will assume it is “pgVEH1”. You must use the exact name.

Using one of the text boxes on that page, determine which field always has a value in a record. Not knowing the structure of your DB, I am simply going to presume that “VEH1VIN” is required. Take note of the name of that text box. For our purposes, we will assume that text box is named “txtVEH1VIN”. You must use the exact name of that control.

With these assumptions in hand, the following code will make the Page invisible if there is a null value (meaning there is no record) in the VEH1 Record:

Me.pgVEH1.Visible = (Not IsNull(Me.txtVEH1VIN))

I would think one would call such code in the OnCurrent Event of the Form, so that every time the main form goes to a different record, various pages would appear or disappear, based upon these values.

Hope this helps.

[mminieri]

Thanks twinnyfo .... I was shopping for a shortcut, but now resigned to do it that way.

This DOES NOT WORK for when applied to an ATTACHMENT field. Happen to know a solution?

[NeoPa]

MMinieri:
After CONSIDERABLE experimentation, the problem APPEARS to be that I can not find a VBA expression that will determine if a tab control page IS or IS NOT null! All event actions for the functionality ALWAYS occur for BOTH pages with and without data.

That's fine. Bear in mind my earlier suggestion was to replace your existing code that you believed, at that time, to be adequate for your purposes.

You're a fine member to work with, may I say, as you feed back information as you find it and that makes it much easier to get to the heart of the problem than it can be with some. Let's look at that then :

First, Twinny's suggestion is pretty sound. It's a very common approach and works (at least when you have such a Field in your data). However, as we now know that we're dealing with the Recordset on the SubForm contained on one of your Pages, we can check that more directly if you prefer.

Now, you've been very helpful with explanations of what's what and where, and also what most things are named. Very important. I'd suggest one change to that approach, which is to include as much text as possible in the text of the post rather than the picture. Sometimes text in pictures is hard for some of us old b*ggers to see very easily and we can't copy & paste it to see more clearly elsewhere. In this case though, I was able to decipher all the text. All good so far.

One set of items that is missing is the names of the SubForm controls you have on each Page of the Tab Control. For now, just so you can work out what I'm suggesting, I'll use a convention that makes the SubForm name the same as the Page name they're on except with "sfm" as a prefix. You can follow this approach if you choose, but for now it's merely so you can understand what I'm suggesting.

NB. This is all air-work as I don't have you database to play or test with. I hope to get it right first time but allow for the possibility I don't ;-)

Starting from the top, and I'm referring here back to your post #16, it seems that you're opening the Form called [INCIDENT_REPORT]. Now, from the picture in your post #24, it seems to me that your ComboBox [IRselection] is actually ON that same form. It's not exactly wrong to open a Form again from itself, but I wouldn't advise it. Typically what one does at that point is either move through the existing Recordset of the form to select the desired record, or change the Filter of the Form to allow that one record only. I'll leave that idea with you to progress with - with help if requested. NB. If my assumption is incorrect then please explain where I've got confused. For now, I'll use your existing code anyway.

Private Sub IRselection_AfterUpdate()
    Dim strVar As String
    Dim blnVar As Boolean
    Dim pgVar As Page
 
    With Me
        strVar = Replace("([IRnumber]=%N)", "%N", .IRSelection.Value)
        Call DoCmd.OpenForm(.Name, acNormal, , strVar)
        Call .MAIN.SetFocus
        .IRlist.Visible = False
        For Each pgVar In .[Choose Section Tab].Pages
            With pgVar
                If .Name <> "MAIN" _
                And .Name <> "IRList" Then
                    strVar = Replace("sfm%N", "%N", .Name)
                    With .Controls(strVar).Form.Recordset
                        blnVar = (Not (.BOF And .EOF))
                    End With
                    .Visible = blnVar
                End If
            Next pgVar
        Next pgVar
    End With
End Sub

When a Recordset is both .BOF AND .EOF at the same time then it's empty. When it's empty the Page in which its Form is contained should not be visible. This code determines that for you without needing to know the details of each Page. If there are any other Pages that should not be included in those you check and set this way then simply exclude them by extending the If .Name = "..." section.

Good luck and please let us know how this works for you.

[NeoPa]

MMinieri:
This DOES NOT WORK for when applied to an ATTACHMENT field. Happen to know a solution?

I'm afraid multiple questions in the same thread are not allowed as it makes the thread very confusing. Please feel free to post it separately though if you like. Links between the two threads are also perfectly acceptable if you choose to add them. Alternatively, I'm happy to do that for you if requested.

[NeoPa]

TwinnyFo:
I would think one would call such code in the OnCurrent Event of the Form, so that every time the main form goes to a different record, various pages would appear or disappear, based upon these values.

A good point. The same code should be run from the Form_Current Event procedure. The best way to handle this, I've found, is to put the common code in a separate, Private, procedure within the Forms's module and call it from all/both places where it's needed. It's never a good idea to repeat code if you don't have to. You nearly always end up amending it and are left with doing the job multiple times. Not only is that extra work but it's also a massive opportunity to get things muddled up and inconsistent. If ever you have your code reviewed then such an approach would certainly get you negative comments. Avoid all that trouble and create a separate procedure for it.

[mminieri]

Gentlemen.... First and foremost, I wish to THANK you for your persistence and patience with me .... quite admirable! Thanks of course for leading me to a workable solution (I will open a new topic for the ATTACHMENT issue). This is the MOST I have ever used a board like BYTES and it was a considerably positive experience. As was noted, my lack of recent experience with Access also prolonged the exchanges here while you pryed it out of me. This level of functionality - while rudimentary to you pros - is totally new to me (about 2 weeks ago). The whole FORM-SUBFORM-TABCTL-PAGE, Ad Nauseum - is almost understood enough to communicate with you properly.

Except for the ATTACHMENT field null check, my form now functions well beyond my own expectations. After a bit of tidying up I will tackle the REPORT component ..... looking forward to that .....NOT!

BTW twinnyfo ... This all kicks from the AFTER_UPDATE from the IRlist combobox because there is no other way to change the recordset being viewed. (IRnumber field is not accessible to users).

[NeoPa]

You're welcome. I think I said before that we could tell quite easily that you were making an effort to be as co-operative and helpful as possible bearing in mind much was new to you. That always makes it so much easier for us to help you - and more pleasant.

I'm very pleased you managed to get this part working adequately :-)

PS. For anyone interested in this saga and the follow-on question, you can find it at Determining if an ATTACHMENT field is null.