How to Reverence a variable then add value to it

[Lisab]

I have several global variables that are of type INTEGER with simular names
and would like to reference them in code. for example intPHOpen,
intPHDAOpen, intPHTROpen

my code loopes through records and I want the RecordType and Category (PH,
TR, DA) to determine which variable should have a value added to it.

I have a variable called strVarName that is the set to the name of the
globla variable that I want to reference

I am having trouble in the SELECT CASE section of my code. There is where I
want to add value to the global variable (strVarName) . currently my code
looks like this
--------------------------------------
Do Until SourceRS.EOF
If SourceRS!Type = "M" Then
'add value to Multifamily open count
intMFOpen = intMFOpen + SourceRS!countvalue
strVarName = "intMF"
ElseIf SourceRS!Type = "P" Then
'add value to PublicHousing opencount
intPHOpen = intPHOpen + SourceRS!countvalue
strVarName = "intPH"
End If

Select Case SourceRS!Category
Case "DA"
strVarName = strVarName & "DAOpen"
strVarName.Value = strVarName.Value + SourceRS!countvalue
Case "TC"
Case "TR"
End Select
....
----------------------------

I am having problems in the section of code that says:
strVarName.value = strVarName.Value + SourceRS!CountValue
I know it is something simple I should do instead

note: strVarName = intPHDAOpen or intMFDAOpen
this is the name of my global variable that I want to add value to

 

[Lisab]

I also tried this in my CASE statement. I declared strVarName as VARIANT and
glblVariable as VARIABLE
-------------------
strVarName = strVarName & "DAOpen"
Set glblVariable = CVar(strVarName)
glblVariable.Value = glblVariable.Value + SourceRS!countvalue
-------------------
I get a runtime error 424 Object Required
on the Set statement

Please Help

Thanks

 

[Dirk Goldgar]

In

I have several global variables that are of type INTEGER with simular
names and would like to reference them in code. for example
intPHOpen, intPHDAOpen, intPHTROpen

my code loopes through records and I want the RecordType and Category
(PH, TR, DA) to determine which variable should have a value added to
it.

I have a variable called strVarName that is the set to the name of the
globla variable that I want to reference

I am having trouble in the SELECT CASE section of my code. There is
where I want to add value to the global variable (strVarName) .
currently my code looks like this
--------------------------------------
Do Until SourceRS.EOF
If SourceRS!Type = "M" Then
'add value to Multifamily open count
intMFOpen = intMFOpen + SourceRS!countvalue
strVarName = "intMF"
ElseIf SourceRS!Type = "P" Then
'add value to PublicHousing opencount
intPHOpen = intPHOpen + SourceRS!countvalue
strVarName = "intPH"
End If

Select Case SourceRS!Category
Case "DA"
strVarName = strVarName & "DAOpen"
strVarName.Value = strVarName.Value +
SourceRS!countvalue Case "TC"
Case "TR"
End Select
...
----------------------------

I am having problems in the section of code that says:
strVarName.value = strVarName.Value + SourceRS!CountValue
I know it is something simple I should do instead

note: strVarName = intPHDAOpen or intMFDAOpen
this is the name of my global variable that I want to add value to

You cannot do this the way you're attempting -- the variable to be
incremented must be known at compile time. I could suggest some
possible workarounds:

1. Use a big Select Case function:

Select Case strVarName
Case "intMFDAOpen"
intMFDAOpen = intMFDAOpen + SourceRS!countvalue
Case "intPHDAOpen"
intPHDAOpen = intPHDAOpen + SourceRS!countvalue
' ... other possible cases ...
End Select

2. Use an array to store the count values, and set a numeric subscript
into the array instead of strVarName. In fact it seems to me that you
could use a two-dimensional array, of which the first dimension is MF
vs. PH, and the second dimension is Category, with the first subscript
in that dimension being defined as the "totals" for the first dimension.
For example,

------- start of example code -----

' Defined at module level in standard module

Public gOpenCounts(0 To 1, 0 to 3) As Integer

Public Const TypeMF = 0
Public Const TypePH = 1
Public Const CatTotal = 0
Public Const CatDA = 1
Public Const CatTC = 2
Public Const CatTR = 3

' Code elsewhere for your recordset processing:

Dim intType As Integer
Dim intCat As Integer

With SourceRS
Do Until .EOF

Select Case !Type
Case "M": intType = TypeMF
Case "P": intType = TypePH
Case Else: intType = -1 ' not a valid subscript
End Select

If intType < 0 Then
' decide what to do if type is not MF or PH
Else

' Add value to appropriate opencount
gOpenCounts(intType, CatTotal) = _
gOpenCounts(intType, CatTotal) + !countvalue

' Determine category.

Select Case !Category
Case "DA": intCat = CatDA
Case "TC": intCat = CatTC
Case "TR": intCat = CatTR
Case Else: intCat = -1 ' not a valid subscript
End Select

if intCat < 0 Then
' decide what to do if category is not DA, TC,
or TR
Else
gOpenCounts(intType, intCat) = _
gOpenCounts(intType, intCat) + !countvalue
End If

End If

' ...

.MoveNext
Loop

End With

------- end of example code -----

3. Other workarounds might include using a collection (which can have a
string index), or using hidden controls on a form (which are available
by the form's Controls collection, which has a string index), or even
storing the info in a table, which could have text keys.