Hide Tabs Based on Value in Field

[Confused]

I have a table Customer Inventory that has a list of systems. I'm trying to
migrate customers off existing systems and on to new ones. I have subforms
set up for the tasks associated with each system migration by using check
boxes, which exist on a TAB. Different tasks are needed based on the system.
Systems A migrates to System E. But Systems B and C migrate together to
System F. I wanted to only show the TAB appropriate based on the name in
[SystemName].

I tried this with a Set Value macro set to the 'On Current'. Condition
[Forms]![CLEC Systems Inventory]![SystemName] In ("A") Set Value ITEM
[Forms]![Clec Systems Inventory]![Page179].[Visible] Expression False

Condition [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C") Set
Value ITEM [Forms]![Clec Systems Inventory]![Page180].[Visible]
Expression False

It works when I open the form. The first few records are on System A, and
it does change the tab when I get to a customer that has system B. But it
never changes back again after that no matter what system name is in the
[system] box.

Someone please help me have a nice weekend! Thanks!

 

[Dirk Goldgar]

I have a table Customer Inventory that has a list of systems. I'm trying
to
migrate customers off existing systems and on to new ones. I have
subforms
set up for the tasks associated with each system migration by using check
boxes, which exist on a TAB. Different tasks are needed based on the
system.
Systems A migrates to System E. But Systems B and C migrate together to
System F. I wanted to only show the TAB appropriate based on the name in
[SystemName].

I tried this with a Set Value macro set to the 'On Current'. Condition
[Forms]![CLEC Systems Inventory]![SystemName] In ("A") Set Value ITEM
[Forms]![Clec Systems Inventory]![Page179].[Visible] Expression
False

Condition [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C") Set
Value ITEM [Forms]![Clec Systems Inventory]![Page180].[Visible]
Expression False

It works when I open the form. The first few records are on System A,
and
it does change the tab when I get to a customer that has system B. But it
never changes back again after that no matter what system name is in the
[system] box.

Someone please help me have a nice weekend! Thanks!

I don't use macros much, finding VBA code easier and more powerful, but it's
clear to me that your macro is going to have to not only set tabs
*invisible* when sone conditions are true, but also set tabs *visible*
again, if other conditions are true. Otherwise, once you hide a tab, it's
going to stay hidden.

I suspect that you can modify your macro to set each tab page's Visible
property to the result of evaluating an appropriate condition, like this:

---------------------------------
Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page180].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] = "A"

Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page179].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C")

 

[Confused]

OK now it flips tabs. Thank you! But the form on the tab does not show any
fields. It seemed like it started doing that before what you gave me anyway.
When I flip to design view I see the fields. Any idea why the
subform/fields won't show up?

I have a table Customer Inventory that has a list of systems. I'm trying
to
migrate customers off existing systems and on to new ones. I have
subforms
set up for the tasks associated with each system migration by using check
boxes, which exist on a TAB. Different tasks are needed based on the
system.
Systems A migrates to System E. But Systems B and C migrate together to
System F. I wanted to only show the TAB appropriate based on the name in
[SystemName].

I tried this with a Set Value macro set to the 'On Current'. Condition
[Forms]![CLEC Systems Inventory]![SystemName] In ("A") Set Value ITEM
[Forms]![Clec Systems Inventory]![Page179].[Visible] Expression
False

Condition [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C") Set
Value ITEM [Forms]![Clec Systems Inventory]![Page180].[Visible]
Expression False

It works when I open the form. The first few records are on System A,
and
it does change the tab when I get to a customer that has system B. But it
never changes back again after that no matter what system name is in the
[system] box.

Someone please help me have a nice weekend! Thanks!

I don't use macros much, finding VBA code easier and more powerful, but it's
clear to me that your macro is going to have to not only set tabs
*invisible* when sone conditions are true, but also set tabs *visible*
again, if other conditions are true. Otherwise, once you hide a tab, it's
going to stay hidden.

I suspect that you can modify your macro to set each tab page's Visible
property to the result of evaluating an appropriate condition, like this:

---------------------------------
Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page180].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] = "A"

Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page179].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C")
---------------------------------

If that doesn't work, then you can create two SetValue actions for each
page, one to show under one condition, and one to hide it under the inverse
of that condition.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Confused]

Wait a second. I see it turns on and off one tab but never switches the tab.

OK now it flips tabs. Thank you! But the form on the tab does not show any
fields. It seemed like it started doing that before what you gave me anyway.
When I flip to design view I see the fields. Any idea why the
subform/fields won't show up?

I have a table Customer Inventory that has a list of systems. I'm trying
to
migrate customers off existing systems and on to new ones. I have
subforms
set up for the tasks associated with each system migration by using check
boxes, which exist on a TAB. Different tasks are needed based on the
system.
Systems A migrates to System E. But Systems B and C migrate together to
System F. I wanted to only show the TAB appropriate based on the name in
[SystemName].

I tried this with a Set Value macro set to the 'On Current'. Condition
[Forms]![CLEC Systems Inventory]![SystemName] In ("A") Set Value ITEM
[Forms]![Clec Systems Inventory]![Page179].[Visible] Expression
False

Condition [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C") Set
Value ITEM [Forms]![Clec Systems Inventory]![Page180].[Visible]
Expression False

It works when I open the form. The first few records are on System A,
and
it does change the tab when I get to a customer that has system B. But it
never changes back again after that no matter what system name is in the
[system] box.

Someone please help me have a nice weekend! Thanks!

I don't use macros much, finding VBA code easier and more powerful, but it's
clear to me that your macro is going to have to not only set tabs
*invisible* when sone conditions are true, but also set tabs *visible*
again, if other conditions are true. Otherwise, once you hide a tab, it's
going to stay hidden.

I suspect that you can modify your macro to set each tab page's Visible
property to the result of evaluating an appropriate condition, like this:

---------------------------------
Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page180].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] = "A"

Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page179].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C")
---------------------------------

If that doesn't work, then you can create two SetValue actions for each
page, one to show under one condition, and one to hide it under the inverse
of that condition.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Confused]

If you know the VBA Code that would be really helpful.

OK now it flips tabs. Thank you! But the form on the tab does not show any
fields. It seemed like it started doing that before what you gave me anyway.
When I flip to design view I see the fields. Any idea why the
subform/fields won't show up?

I have a table Customer Inventory that has a list of systems. I'm trying
to
migrate customers off existing systems and on to new ones. I have
subforms
set up for the tasks associated with each system migration by using check
boxes, which exist on a TAB. Different tasks are needed based on the
system.
Systems A migrates to System E. But Systems B and C migrate together to
System F. I wanted to only show the TAB appropriate based on the name in
[SystemName].

I tried this with a Set Value macro set to the 'On Current'. Condition
[Forms]![CLEC Systems Inventory]![SystemName] In ("A") Set Value ITEM
[Forms]![Clec Systems Inventory]![Page179].[Visible] Expression
False

Condition [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C") Set
Value ITEM [Forms]![Clec Systems Inventory]![Page180].[Visible]
Expression False

It works when I open the form. The first few records are on System A,
and
it does change the tab when I get to a customer that has system B. But it
never changes back again after that no matter what system name is in the
[system] box.

Someone please help me have a nice weekend! Thanks!

I don't use macros much, finding VBA code easier and more powerful, but it's
clear to me that your macro is going to have to not only set tabs
*invisible* when sone conditions are true, but also set tabs *visible*
again, if other conditions are true. Otherwise, once you hide a tab, it's
going to stay hidden.

I suspect that you can modify your macro to set each tab page's Visible
property to the result of evaluating an appropriate condition, like this:

---------------------------------
Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page180].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] = "A"

Condition: (none)
SetValue
Item: [Forms]![CLEC Systems Inventory]![Page179].[Visible]
Expression: [Forms]![CLEC Systems Inventory]![SystemName] In ("B,C")
---------------------------------

If that doesn't work, then you can create two SetValue actions for each
page, one to show under one condition, and one to hide it under the inverse
of that condition.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

OK now it flips tabs. Thank you! But the form on the tab does not show
any
fields. It seemed like it started doing that before what you gave me
anyway.
When I flip to design view I see the fields. Any idea why the
subform/fields won't show up?

A totally blank form (or subform) is usually a sign that the form's
recordsource returned no records to be displayed, and it is impossible to
add a new record because

(a) the form's AllowAdditions property is set to No,

(b) the recordsource query is not updatable, or

(c) the database itself is read-only for some reason.

Are you using subforms on these tabs? If so, I think they will also be
nonupdatable if the main form's AllowEdits property is set to No.

You haven't given enough information to let me be any more specific than
that.

 

[Dirk Goldgar]

Wait a second. I see it turns on and off one tab but never switches the
tab.

I'm not sure what you mean. What do you want to have happen, and how
exactly is it different from what is currently happening?

 

[Confused]

That fixed that problem! I got the VBA Code to work except when I add
multiple names to it, I get a type mismatch error on this statement in the
code below. Can you give me the correct syntax for this?


If Me.System = ("EDI" Or "TAG" Or "Direct XML") Then
Me.Page180.Visible = False:



Private Sub Form_Current()
If Me.System = "LENS" Then
Me.Page179.Visible = False

Else

Me.Page179.Visible = True

End If

If Me.System = ("EDI" Or "TAG" Or "Direct XML") Then
Me.Page180.Visible = False


Else
Me.Page180.Visible = True




End If

 

[Dirk Goldgar]

That fixed that problem! I got the VBA Code to work except when I add
multiple names to it, I get a type mismatch error on this statement in
the
code below. Can you give me the correct syntax for this?


If Me.System = ("EDI" Or "TAG" Or "Direct XML") Then
Me.Page180.Visible = False:



Private Sub Form_Current()
If Me.System = "LENS" Then
Me.Page179.Visible = False

Else

Me.Page179.Visible = True

End If

If Me.System = ("EDI" Or "TAG" Or "Direct XML") Then
Me.Page180.Visible = False


Else
Me.Page180.Visible = True




End If

You switched to VBA, I see. Yes, this syntax is wrong:

If Me.System = ("EDI" Or "TAG" Or "Direct XML") Then

This would be correct, for that one statement:

If Me.System = "EDI" _
Or Me.System = "TAG" _
Or Me.System = "Direct XML" _
Then

However, the Select Case statement is designed for just the sort of thing
you're doing. Try this:

'----- start of suggested code -----
Private Sub Form_Current()

Select Case Me.System

Case "LENS"
Me.Page179.Visible = False
Me.Page180.Visible = True

Case "EDI", "TAG", "Direct XML"
Me.Page179.Visible = True
Me.Page180.Visible = False

Case Else
Me.Page179.Visible = True
Me.Page180.Visible = True

End Select

End Sub
'----- end of suggested code -----

I hope I didn't get the logic backwards, but even if I did, you can see how
the Select structure simplifies and clarifies your code.

 

[Confused]

That was awesome! You saved me a great deal of time and got me intrigued by
VBA. I can't thank you enough. Happy Weekend!