I'll have a play with this - thanks.

--
Dianne

In om,
onedaywhen typed:
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)


"Tom Ogilvy" wrote in message
...
You can't construct a string and have it interpreted as a variable.
You can do that in foxpro, but not excel.

--
Regards,
Tom Ogilvy


"Dianne" wrote in message
...
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

--
Thanks,
Dianne