Join string with variable name to get variable value

  • Thread starter Thread starter Dianne
  • Start date Start date
D

Dianne

For the first time ever, I'm trying to get some Excel97 data into
Access97 with VBA.

I have a module with some module-level variables declared, e.g.
Dim mstrProjectName as String

In one of my procedures, I select some data from Access into a
recordset. What I want to do is to go through the fields in the
recordset, get the name of the field, the value from Access and the
value from my Excel variable and load them into a listbox on a form and
show it to the user. The Excel variables have the same name as the
Access fields, except that they're prefixed by mstr (or whatever).

I can't get the listbox's List property to accept "mstr" & fieldName as
a variable name -- it treats it as a string "mstrProjectName", and my
listbox has "mstrProjectName" as column 2 instead of the value the
variable holds.

Any advice is appreciated...

If rs.RecordCount <> 0 Then 'If this record exists in the database
rs.MoveFirst

For Each fldLoop In rs.Fields

strFieldName = fldLoop.Name
vntFieldValue = fldLoop.Value
If IsNull(vntFieldValue) Then vntFieldValue = ""

With frmExistingProject.lstDifferences
.AddItem intCounter
.List(intCounter, 0) = strFieldName
.List(intCounter, 1) = vntFieldValue
If strFieldName = "ProjectValue" Then
'This is my only non-string variable and works OK
.List(intCounter, 2) = mcurProjectValue
Else
'This doesn't work
.List(intCounter, 2) = "mstr" & strFieldName
End If
End With

intCounter = intCounter + 1

Next fldLoop
End If
 
You can't construct a string and have it interpreted as a variable. You can
do that in foxpro, but not excel.
 
Instead of storing the text in string variables, how about adding them
to a Collection object i.e. instead of your existing approach,
something like this:

Private mstrCol1 As String
Private mstrCol2 As String
Private mstrCol3 As String
...
mstrCol1 = Range("A1").Value
mstrCol2 = Range("A2").Value
mstrCol3 = Range("A3").Value
...
.List(intCounter, 2) = "mstr" & strFieldName 'This doesn't work

Try this approach:

Private colColumnValues As Collection
...
Set colColumnValues = New Collection
With colColumnValues
.Add Range("A1").Value, "Col1"
.Add Range("A2").Value, "Col2"
.Add Range("A3").Value, "Col3"
End With
...
.List(intCounter, 2) = colColumnValues(strFieldName)

--
 
Thanks onedaywhen, that worked a treat!

I've used collections before, but never with a key. Nice.
 
If using a numeric index as you illustrate and it would be hard to imagine
anything else, then using an array would seem like the short path around the
block.
 
Back
Top