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

[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 “txtVEH1VI N”. 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).