VBA Case Statment

[Guest]

Hello:

I need to evaluate a series of statements to determine whether to hide
tabs on a form. Orginally I placed the following code in the Form's load
event.

If DLookup("CustSpecs_Present", "CustSpecs", "CustSpecs_RoomID=11
And CustSpecs_Present = 1 And CustSpecs_CustID = " & Me.Header_CustID
& " And CustSpecs_PlanID = " & Me.Header_PlanID) Then
Me.Page1.Visible = True
Else
Me.Page1.Visible = False
End If

This code works without a problem. However, I need to evaluate a series of
conditions which are not dependent upon one another. I was advised to use a
case select statement. I constructed the following code

Select Case

Case 1

DLookup("CustSpecs_Present", "CustSpecs", "CustSpecs_RoomID=11
And CustSpecs_Present = 1 And CustSpecs_CustID = " & Me.Header_CustID
& " And CustSpecs_PlanID = " & Me.Header_PlanID) = Me.Page1.Visible = True

The code doesn't return an error but it doesn't work.

How should I structure this so it works?

Thanks
Brennan

 

[Brendan Reynolds]

Usually, Select Case ... End Select is used to evaluate a single condition
and take one of several different courses of action depending on the result.
For example, you might use it something like so ...

lngResult = DLookup(strFieldName, strTableName, strCriteria)
Select case lngResult
Case 1
'do something if lngResult = 1
Case 2, 3
'do something else if lngResult = 2 or 3
Case Else
'do something else if lngResult is anything other than 1, 2, or 3
End Select

While it is less common, sometimes, it can be useful to turn a Select Case
'on it's head' and test several conditions against a single result, like so
....

Select Case True
Case DLookup(strFieldName, strTablename, strCriteria) = 1
'do something if result of DLookup() expression is 1
Case X + Y < Z
'do something else if x + y < z
End Select

 

[Guest]

Thanks. So what would be the best way to accomplish what I need to do. I
have a tab control on a form that represents the rooms of a house. Depending
on the design of the house, some of the rooms are always present but any
house can conciveably have any room built into it.

I have approached the problem by building a tab control with each tab
representing a room. The plan and room definitions are stored in a table and
there is a bit field indicating whether or not that room is a part of the
customers plan. I was going to have some code look at the bit fields for the
customers chosen plan and hide the respective pages(rooms) based upon whther
the room is marked as being a part of the plan.

It doesn't look like an IF Then Else statement will work and from your
response, I t doesn't look like Case Select is the way to go either.

I basically want to say:

If the media room is not a part of the customers plan, hide the Media room
tab on the tab control. And I need to do this for each tab.

Any suggestions would be appreciated.

Thanks
Brennan

 

[Brendan Reynolds]

I suspect a schema design flaw here. I suspect those room fields should be
records. I suspect there should be two tables, houses related one to many to
rooms.

That said, I think you are right that what you need is not an If ... Then or
a Select Case ... End Select. It seems what you need is a loop. The details
will depend on how you associate the tab page with the corresponding field.
In this example, I've used the Tag property - the Tag property of each page
contains the name of the field that determines whether that page should be
enabled. I'm also setting focus to a control that is not on any tab page,
because you can't disable a control while it has focus ...

Private Sub Form_Current()

Dim pge As Page

Me.Text0.SetFocus

If Not Me.NewRecord Then
For Each pge In Me.TabCtl2.Pages
pge.Enabled = Me(pge.Tag)
Next pge
End If

End Sub

 

[Joe Williams]

Just use individual IF THEN statements, testing for each condition. The key
thing is to end the first test before moving on to the next one, otherwise
it will test for the first condition and ignore the rest of the IF statement
if it's true. The following should do what you want:

IF mediaplan = false then
me.tabmedia.visible = false
END IF

IF livingroomplan = false then
me.tablivingroom.visible = false
END IF

etc etc

Joe

 

[solex]

Brennan,

Here is a suggestion not knowing your data model. Make the name of the room
field and the room tabs identical. Then for a particular customer and plan
construct a query that will return all of the visible rooms for that
customer/plan. Then design the form so that all of the tabs are not
visible initially.

Then in the form load event:

Do While Not (rs.EOF())
For i = 0 To rs.Fields.Count -1
Me.Controls(rs.Fields(i).Name).Visible = True
Next
Loop

Dan

 

[Guest]

Thanks Brendan:

I'll give this a shot. I think you may be right in that the design might
have some problems. This database will be used to enter and track customer
selection and necessary choices for the home. Each room has a collection of
choices which the customer needs to make. (i.e. Interior wall color, ceiling
color, trim color flooring type and color would be the choices for a bedroom
for example) Each housing plan has a specific room collection and each of
these rooms has a specific set of selections that need to be made.

The monkey wrench in all of this is that although not every plan has every
room, any room can be added to any plan if the physical dimensions of the
lot will support it. I have been asked to ensure that the design center be
presented a data entry form with all the rooms and required selections for
any plan selected by the customer. So if Plan X doesn't have a wet bar as
part of it's standard spec, a customer can add if the lot can accomodate it.

I'll try your suggestion but I'll also take another hard look at the design
to see if their is anything that can be changed