VBA Help

[gmazza via AccessMonster.com]

Hey there,
Here is a sample of my code. I just want to know if there is an easierr way
of writing 1 IF statement, then 5 of them.

Dim VisitFlag1 As Integer
Dim VisitFlag2 As Integer
Dim VisitFlag3 As Integer
Dim VisitFlag4 As Integer
Dim VisitFlag5 As Integer
Dim VisitNo As Integer

VisitNo = Forms!Patient.Text51

Set rs = CurrentDb.OpenRecordset("Select * from VisitCriteria where
ClinicalTrialId = '" & study & "'")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields

If fld.Name = "Visit1" Then
If fld.Value = False Then
VisitFlag1 = 0
Else
VisitFlag1 = 1
End If
End If

I am doing this for EACH Visit, 1-5

If fld.Name = "CriteriaValue" Then
If IsNull(fld.Value) Then
Else

If VisitNo = VisitFlag1 Then
intCount = intCount + 1
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
Else
If VisitNo = VisitFlag2 Then
intCount = intCount + 1
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
Else
If VisitNo = VisitFlag3 Then
intCount = intCount + 1
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
End If
End If
End If

I only did an example here for 3 visits. Is there anyway I can make this IF
in just 1 statement, since its doing the same thing anwyay, it just needs to
know what the VisitFlag equals.
Thanks for your help!

 

[Dorian]

Look up SELECT in Access help screens.

Select Case Statement


Executes one of several groups of statements, depending on the value of an
expression.

Syntax

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]

End Select

The Select Case statement syntax has these parts:

Part Description
testexpression Required. Any numeric expression or string expression.
expressionlist-n Required if a Case appears. Delimited list of one or more
of the following forms: expression, expression To expression, Is
comparisonoperator expression. The To keyword specifies a range of values. If
you use the To keyword, the smaller value must appear before To. Use the Is
keyword with comparison operators (except Is and Like) to specify a range of
values. If not supplied, the Is keyword is automatically inserted.
statements-n Optional. One or more statements executed if testexpression
matches any part of expressionlist-n.
elsestatements Optional. One or more statements executed if testexpression
doesn't match any of the Case clause.



Remarks

If testexpression matches any Case expressionlist expression, the statements
following that Case clause are executed up to the next Case clause, or, for
the last clause, up to End Select. Control then passes to the statement
following End Select. If testexpression matches an expressionlist expression
in more than one Case clause, only the statements following the first match
are executed.

The Case Else clause is used to indicate the elsestatements to be executed
if no match is found between the testexpression and an expressionlist in any
of the other Case selections. Although not required, it is a good idea to
have a Case Else statement in your Select Case block to handle unforeseen
testexpression values. If no Case expressionlist matches testexpression and
there is no Case Else statement, execution continues at the statement
following End Select.

You can use multiple expressions or ranges in each Case clause. For example,
the following line is valid:

Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber

Note The Is comparison operator is not the same as the Is keyword used in
the Select Case statement.

You also can specify ranges and multiple expressions for character strings.
In the following example, Case matches strings that are exactly equal to
everything, strings that fall between nuts and soup in alphabetic order, and
the current value of TestItem:

Case "everything", "nuts" To "soup", TestItem

Select Case statements can be nested. Each nested Select Case statement must
have a matching End Select statement.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".