Using VBA (or a Macro) to control a tab's visibility

[Jack G]

I'm trying to control which of two tabs will be visible in a form depending
on the first letter of the current record's "ProjectNumber". If the fist
letter of ProjectNumber is "U", a tab named "Details-USA" will be visible,
but "Details-Europe" will not be visible; if the first letter is "E", a tab
named "Details-Europe" would be visible, but "Details-USA" wouldn't.

I tried to define a function called DetailsTab to do this and then have the
function called from the "On Current" event of my form, but it has never
worked. One of my many failed attempts at the code looked something like
this:

Function DetailsTab()

If Mid(Forms!DetailsForm!ProjectNumber, 1, 1) = "U" Then _

Forms!DetailsForm!Details-USA.Visible = True

Forms!DetailsForm!Details-Europe.Visible = False

If Mid(Forms!DetailsForm!ProjectNumber, 1, 1) = "E" Then _

Forms!DetailsForm!Details-Europe.Visible = True

Forms!DetailsForm!Details-USA.Visible = False

End Function

I'd appreciate any direction someone could give me on this, including
changing direction altogether. It seems like this ought to be pretty
simple!

Jack

 

[Jack G]

Rick, thanks for the quick response!

Unfortunately, I'm a little dense on this - should these lines go into my
vba function or somewhere else? When I put them in the function, I get an
error message saying, "Invalid use of Me keyword". Should they part of a
"Private Sub" instead, or be inside a Macro maybe??

Jack

I'm trying to control which of two tabs will be visible in a form
depending on the first letter of the current record's
"ProjectNumber". If the fist letter of ProjectNumber is "U", a tab
named "Details-USA" will be visible, but "Details-Europe" will not be
visible; if the first letter is "E", a tab named "Details-Europe"
would be visible, but "Details-USA" wouldn't.
I tried to define a function called DetailsTab to do this and then
have the function called from the "On Current" event of my form, but
it has never worked. One of my many failed attempts at the code
looked something like this:

Function DetailsTab()

If Mid(Forms!DetailsForm!ProjectNumber, 1, 1) = "U" Then _

Forms!DetailsForm!Details-USA.Visible = True

Forms!DetailsForm!Details-Europe.Visible = False

If Mid(Forms!DetailsForm!ProjectNumber, 1, 1) = "E" Then _

Forms!DetailsForm!Details-Europe.Visible = True

Forms!DetailsForm!Details-USA.Visible = False

End Function

I'd appreciate any direction someone could give me on this, including
changing direction altogether. It seems like this ought to be pretty
simple!

Jack

Me![Details-USA].Visible = (Left(Me!ProjectNumber, 1) = "U")
Me![Details-Europe].Visible = (Left(Me!ProjectNumber, 1) = "E")

 

[Rick Brandt]

Rick, thanks for the quick response!

Unfortunately, I'm a little dense on this - should these lines go
into my vba function or somewhere else? When I put them in the
function, I get an error message saying, "Invalid use of Me keyword".
Should they part of a "Private Sub" instead, or be inside a Macro
maybe??

I would just put those lines into your current event. If you put them into a
function in a standard module then you would have to replace "Me" with
"Forms!NameOfForm...". If you put them in a function that is defined in the
module of your form then the "Me" can stay put.

I see no point in putting something that is specific to one form in a standard
module though.

 

[Jack G]

That's great! Works like a charm.

Jack

I would just put those lines into your current event. If you put them
into a function in a standard module then you would have to replace "Me"
with "Forms!NameOfForm...". If you put them in a function that is defined
in the module of your form then the "Me" can stay put.

I see no point in putting something that is specific to one form in a
standard module though.