By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

P: 32
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.
Jun 27 '18 #1
Share this Question
Share on Google+
30 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
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!
Jun 27 '18 #3

P: 32
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
Jun 27 '18 #4

P: 32
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.
Jun 27 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,158
We cannot see your image. Many folks cannot access linked images from their work computers. You must upload it under the advances tab.
Jun 27 '18 #6

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 27 '18 #7

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 27 '18 #8

P: 32
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.
Jun 27 '18 #9

twinnyfo
Expert Mod 2.5K+
P: 3,158
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).
Jun 27 '18 #10

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 27 '18 #11

P: 32
[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]
Jun 28 '18 #12

P: 32
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.
Jun 28 '18 #13

P: 32
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,
Expand|Select|Wrap|Line Numbers
  1. If (IsNull(SUP1) = True) Then
  2. SUP1.Visible = False
  3. Else
  4. SUP1.Visible = True
  5. End If
  6. End Sub
Jun 28 '18 #14

NeoPa
Expert Mod 15k+
P: 31,308
Instead of all that you could just say :
Expand|Select|Wrap|Line Numbers
  1. SUP1.Visible = (Not IsNull(SUP1))
Jun 28 '18 #15

P: 32
Thanks...That line of code is RED when entered as below;
Expand|Select|Wrap|Line Numbers
  1. Private Sub IRselection_AfterUpdate()
  2. DoCmd.OpenForm "INCIDENT_REPORT", acNormal, , "IRnumber = " & Me![IRselection].Value
  3. Me.MAIN.SetFocus
  4. Me.IRlist.Visible = False
  5. SUP1.Visible = (Not IsNull(SUP1)
  6. End Sub
Jun 28 '18 #16

NeoPa
Expert Mod 15k+
P: 31,308
Only because you've not followed the golden rule of Copying & Pasting accurately. You've left off the closing parenthesis _)_.
Jun 28 '18 #17

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 28 '18 #18

twinnyfo
Expert Mod 2.5K+
P: 3,158
@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!
Jun 28 '18 #19

NeoPa
Expert Mod 15k+
P: 31,308
Yes indeed, my friend :-)
Jun 28 '18 #20

P: 32
SUP1.Visible = (Not IsNull(SUP1)) and nulls are still made visible
Jun 28 '18 #21

twinnyfo
Expert Mod 2.5K+
P: 3,158
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,
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 28 '18 #22

NeoPa
Expert Mod 15k+
P: 31,308
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 :
Expand|Select|Wrap|Line Numbers
  1. SUP1.Value Is Null
  2. IsNull(SUP1) ==> True
  3. (Not IsNull(SUP1)) ==> False
  4. SUP1.Visible ==> False
How does that make anything visible?
Jun 28 '18 #23

P: 32



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.
Jun 29 '18 #24

twinnyfo
Expert Mod 2.5K+
P: 3,158
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 29 '18 #25

P: 32
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?
Jun 29 '18 #26

NeoPa
Expert Mod 15k+
P: 31,308
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub IRselection_AfterUpdate()
  2.     Dim strVar As String
  3.     Dim blnVar As Boolean
  4.     Dim pgVar As Page
  5.  
  6.     With Me
  7.         strVar = Replace("([IRnumber]=%N)", "%N", .IRSelection.Value)
  8.         Call DoCmd.OpenForm(.Name, acNormal, , strVar)
  9.         Call .MAIN.SetFocus
  10.         .IRlist.Visible = False
  11.         For Each pgVar In .[Choose Section Tab].Pages
  12.             With pgVar
  13.                 If .Name <> "MAIN" _
  14.                 And .Name <> "IRList" Then
  15.                     strVar = Replace("sfm%N", "%N", .Name)
  16.                     With .Controls(strVar).Form.Recordset
  17.                         blnVar = (Not (.BOF And .EOF))
  18.                     End With
  19.                     .Visible = blnVar
  20.                 End If
  21.             Next pgVar
  22.         Next pgVar
  23.     End With
  24. 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.
Jun 29 '18 #27

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 29 '18 #28

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 29 '18 #29

P: 32
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).
Jun 29 '18 #30

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jun 29 '18 #31

Post your reply

Sign in to post your reply or Sign up for a free account.