[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

How / can I disable or hide one tab on a tab control based on the field values of a sub report on another tab of the same tab control

How / can I disable or hide one tab(#2) on a tab control based on the field values of a sub report on another tab(#1) of the same tab control?  I have a form that uses the tab control with several tabs.  There is a subreport on tab#1 linked to the main form using the order# from the order table as the main link and a related orderid foreign key as the child link from the projects table.  What I need to do is to have tab#2 and / or controls on tab#2 hidden or disabled when some fields on the subreport on tab#1 are not filled in.  I've been trying references to the subreport fields using the subreport control name and display the values, for example, but continue to have problems when trying disable tab#2 or controls on tab#2 when those subreport fields are blank.  I think the difficulty may be in selecting the event(s) to ensure that tab#2 is visible when it should and not visible when it shouldn't be.  Perhaps, moving subreport fields to the mainform would be better??  Or, setting an indicator on the main form if subreport fields are blank and disabling tab#2 based on that.
0
conardb
Asked:
conardb
  • 7
  • 4
1 Solution
 
mbizupCommented:
Try this in the Current Event of the subform, and in the After Update event of any textboxes, etc that need to trigger this change:

Me.Parent.Page1.Visible = (Me.txtYourTextbox  & "" <> "")

Open in new window



Replace "Page1" with the actual name of the tab that needs to be hidden.
0
 
conardbAuthor Commented:
Looks like this may do it.
Regarding OnCurrent: How / would it need to be changed to account for if any of 6 fields are blank?  I'm using If  Then ElseIf for the 6

I'm opening the form from a seperate 'search' form where I double click on the order# and open the main form for that Order# and when double-click on the Order# from the search form the Value for the field on the subform is returning Isnull = true eventhough there is a value in the field.  

Is using if then elseif a method you would suggest for handling combo of any 6 field values blank and Would putting the if then elseif on the dblclick event that is used to open the main form for the order# what you would suggest to test the oncurrent when loading the main form?
0
 
mbizupCommented:
Are you hiding the same tab regardless of which field is blank?

I'd suggest this instead of using elseifs (from the subform's current event):


If "" & Me.Field1 = "" Or "" & Me.Field2 = "" Or "" & Me.Field3 = "" Or "" & Me.Field4 = "" Or ..etc  Then
    Me.Parent.TabName.Visible = false
End if

Open in new window



Alternatively if you want this check to occor when the MainForm loads or changes records, try this from the mainform's current event:

With Me.SubformControlName.Form
    If "" & .Field1 = "" Or "" & .Field2 = "" Or "" & .Field3 = "" Or "" & .Field4 = "" Or ..etc  Then
        Me.TabName.Visible = false
    End if
End With

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
mbizupCommented:
The important thing to note with Tab controls is that they do not affect your form references.  Controls (including pages) on tab controls are effectively on the form itself, so you refer to them as such -- without any intermediate reference to the tab control.  This differs from subforms in that references to controls on subforms need to include a reference to the subform.
0
 
conardbAuthor Commented:
Ok, thanks.  Yes, I'm hiding the same tab regardless of which field is blank.
0
 
mbizupCommented:
Okay :)

Then there are a few possible solutions here.


From the subform's current event:

If "" & Me.Field1 = "" Or "" & Me.Field2 = "" Or "" & Me.Field3 = "" Or "" & Me.Field4 = "" Or ..etc  Then
    Me.Parent.TabName.Visible = false
Else 
    Me.Parent.TabName.Visible = True
End if

Open in new window

OR From the mainform's current event:
With Me.SubformControlName.Form
    If "" & .Field1 = "" Or "" & .Field2 = "" Or "" & .Field3 = "" Or "" & .Field4 = "" Or ..etc  Then
        Me.TabName.Visible = false
    Else
        Me.TabName.Visible = True

    End if
End With

Open in new window

0
 
mbizupCommented:
And you can write either of those in a 'short-hand' format.

From the subform:

    Me.Parent.TabName.Visible = NOT("" & Me.Field1 = "" Or "" & Me.Field2 = "" Or "" & Me.Field3 = "" Or "" & Me.Field4 = "" Or ..etc )

Open in new window



OR From the MainForm:

With Me.SubformControlName.Form
        Me.TabName.Visible = NOT ("" & .Field1 = "" Or "" & .Field2 = "" Or "" & .Field3 = "" Or "" & .Field4 = "" Or ..etc)
End With

Open in new window

0
 
conardbAuthor Commented:
Well, the tab continues to be disabled even if the subform fields do have values.  

I added a message box showing the test of whether one of the fields is null on load of the main form and on current of the subform and they are resolving to be null.  

The mainform is opened / loaded from a listbox on a seperate search form that opens the mainform table as a recordset and assigns the form fields to table fields.  

How / would the test(s) and events used need to be modified to handle this case?  Could it be some sort of delay between the event and data load?
0
 
mbizupCommented:
If you post a sample copy of your database, I can look at it later today.

Did you try all of my suggestions?   They should be tested separately from each other.
0
 
conardbAuthor Commented:
yes, I'm applying the last suggestion only and when toggling the setting of the tab.enabled from false to true the tab.enabled property does alternate accordingly.
0
 
mbizupCommented:
Okay - so the code I suggested to use in the mainform works, then?

The reason, I think does indeed have to do with the order of events between main forms and sub forms.

This is a good reference:

http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx

Look under the heading 'Working with subforms'  (subforms load before mainforms).
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now