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

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

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

Thanks, Tom.

--
Dianne

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


"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

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

Thanks onedaywhen, that worked a treat!

I've used collections before, but never with a key. Nice.

--
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

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.

--
Regards,
Tom Ogilvy


onedaywhen wrote in message
om...
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