Dynamic Variable Names in Visual Basic and Eval()

  • Thread starter Thread starter Tom Rogers
  • Start date Start date
T

Tom Rogers

I have a set of form fields with helpful names like PxName, PxAddress, etc.,
where x is 1, 2, 3, or 4.

There are quite a few fields per x, and I would like to write this the
elegant way, along these lines:

-----
For i = 1 To 4
aThisRecord = GetParents(iStudentID, i) ' returns an array with record
info
With Reports![SR4 - VBC]
Eval ("!P" & i & "Name = aThisRecord(0)")
Eval ("!P" & i & "Address = aThisRecord(1)")
End With
Next i
-----

However, when I run the code I get the following error on the line with the
first Eval statement:

Run-time error '2423':
The expression you entered has an invalid . (dot) or ! operator or invalid
parenthesis.

I've tested my construction with a MsgBox and got what I wanted:
!P1Name = aThisRecord(0)
When I hard code that line myself, it works just fine. I've also tested the
value of aThisRecord(0) and it's exactly what I want.

Anyone know what I'm doing wrong?
 
You might have to forego the With part:

Eval ("Reports![SR4 - VBC]!P" & i & "Name = aThisRecord(0)")
Eval ("Reports![SR4 - VBC]!P" & i & "Address = aThisRecord(1)")
 
Nope.

When I try this:
---
For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![SR4 - VBC]!P" & i & "Name = aThisRecord(0)")
Next i
---
I get the following error:
---
Run-time error '2425':
The expression you entered has a function name that Microsoft Access can't
find.
---

I tried spitting out the value, just to make sure, and the function call to
GetParents works. I tried unqualifying the name of the form field and I got
an error saying Access could not find the name 'P1Name' I entered in the
expression. As I increased qualification, it broke on the leftmost
qualifier. Once it becomes fully qualified it thinks it's a function again.
\
--
Tom Rogers
(e-mail address removed)
"The great enemy of clear language is insincerity."
Douglas J. Steele said:
You might have to forego the With part:

Eval ("Reports![SR4 - VBC]!P" & i & "Name = aThisRecord(0)")
Eval ("Reports![SR4 - VBC]!P" & i & "Address = aThisRecord(1)")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tom Rogers said:
I have a set of form fields with helpful names like PxName, PxAddress, etc.,
where x is 1, 2, 3, or 4.

There are quite a few fields per x, and I would like to write this the
elegant way, along these lines:

-----
For i = 1 To 4
aThisRecord = GetParents(iStudentID, i) ' returns an array with record
info
With Reports![SR4 - VBC]
Eval ("!P" & i & "Name = aThisRecord(0)")
Eval ("!P" & i & "Address = aThisRecord(1)")
End With
Next i
-----

However, when I run the code I get the following error on the line with the
first Eval statement:

Run-time error '2423':
The expression you entered has an invalid . (dot) or ! operator or invalid
parenthesis.

I've tested my construction with a MsgBox and got what I wanted:
!P1Name = aThisRecord(0)
When I hard code that line myself, it works just fine. I've also tested the
value of aThisRecord(0) and it's exactly what I want.

Anyone know what I'm doing wrong?
Click to expand...
Click to expand...
 
Tom said:
When I try this:
---
For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![SR4 - VBC]!P" & i & "Name = aThisRecord(0)")
Next i
Click to expand...


It's the array reference that is causing the problem. VBA
variables are not included in the expression service's name
space.

It appears from the way you wrote it, that the array
contains numeric values so it should be:

Eval("Reports![SR4 - VBC]!P" & i & "Name = " _
& aThisRecord(0))

if these names are text strings, then it would be:

Eval ("Reports![SR4 - VBC]!P" & i & "Name = """ _
& aThisRecord(0) & """")
 
Thanks, I'll try that out.

And they're all strings, thus far.

--
Tom Rogers
(e-mail address removed)
"The great enemy of clear language is insincerity."
Marshall Barton said:
Tom said:
When I try this:
---
For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![SR4 - VBC]!P" & i & "Name = aThisRecord(0)")
Next i
Click to expand...


It's the array reference that is causing the problem. VBA
variables are not included in the expression service's name
space.

It appears from the way you wrote it, that the array
contains numeric values so it should be:

Eval("Reports![SR4 - VBC]!P" & i & "Name = " _
& aThisRecord(0))

if these names are text strings, then it would be:

Eval ("Reports![SR4 - VBC]!P" & i & "Name = """ _
& aThisRecord(0) & """")
Click to expand...
 
Here's an unexpected result: when I try your suggestion out, I get no error.
It also doesn't seem to do anything.

I used the following code:

Dim aThisRecord() As String
Dim i As Integer

For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![Pre-Filled Enrollment Form]!P" & i & "NAME = """ &
aThisRecord(0) & """")
Next i

This should fill in one field per group, for all four groups. Instead, no
fields are filled in. I added a MsgBox call to the For loop so I could make
sure it wasn't just that the records were blank, and that's not the problem;
GetParents is returning the correct values.

I tried hard coding the idea to fill in just one field, like this:

Dim test as String
test = "whee"
Eval ("Reports![Pre-Filled Enrollment Form]!P1NAME = "" & test & """)

And nothing happened.

Any more help you can give me would be greatly appreciated.

Also - why are there two extra sets of quotation marks? I realize the
expression I want run is: object = "value", and adding quotes around it
would yield: "object = "value"", so I assume that the extra quotes function
as escape characters, but I'd like to make sure that's the case.

--
Tom Rogers
(e-mail address removed)
"The great enemy of clear language is insincerity."
Marshall Barton said:
Tom said:
When I try this:
---
For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![SR4 - VBC]!P" & i & "Name = aThisRecord(0)")
Next i
Click to expand...


It's the array reference that is causing the problem. VBA
variables are not included in the expression service's name
space.

It appears from the way you wrote it, that the array
contains numeric values so it should be:

Eval("Reports![SR4 - VBC]!P" & i & "Name = " _
& aThisRecord(0))

if these names are text strings, then it would be:

Eval ("Reports![SR4 - VBC]!P" & i & "Name = """ _
& aThisRecord(0) & """")
Click to expand...
 
Tom said:
Here's an unexpected result: when I try your suggestion out, I get no error.
It also doesn't seem to do anything.

I used the following code:

Dim aThisRecord() As String
Dim i As Integer

For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![Pre-Filled Enrollment Form]!P" & i & "NAME = """ &
aThisRecord(0) & """")
Next i

This should fill in one field per group, for all four groups. Instead, no
fields are filled in. I added a MsgBox call to the For loop so I could make
sure it wasn't just that the records were blank, and that's not the problem;
GetParents is returning the correct values.

I tried hard coding the idea to fill in just one field, like this:

Dim test as String
test = "whee"
Eval ("Reports![Pre-Filled Enrollment Form]!P1NAME = "" & test & """)

And nothing happened.
Click to expand...

Well, you are not doing anything with the result of the
Eval. Your current explanation leads me to believe that
you're trying to run an assignment statement using Eval,
instead of just evaluate an expression (in this case, a
comparison that returns a True or False). If you really do
want an assignment statement, then we've wandered way off
the path. Try using this syntax instead of using Eval:

Me("P" & i & "NAME") = aThisRecord(0)

but shouldn't that 0 be an i?


Also - why are there two extra sets of quotation marks? I realize the
expression I want run is: object = "value", and adding quotes around it
would yield: "object = "value"", so I assume that the extra quotes function
as escape characters, but I'd like to make sure that's the case.
Click to expand...

I think you got it right. The rule is that you have to use
two quotes to one quote inside a quoted string. E.g. to
get
Jon said "Hello" to Bob
as a result, you need to type the literal string
"Jon said ""Hello"" to Bob"
 
but shouldn't that 0 be an i?

Nope. GetParents() returns an array that contains information from a record
in the database. Each element in the array corresponds to a specific field.
Names are stored in element 0.

And thanks very much - this worked perfectly.

--
Tom Rogers
(e-mail address removed)
"The great enemy of clear language is insincerity."
Marshall Barton said:
Tom said:
Here's an unexpected result: when I try your suggestion out, I get no error.
It also doesn't seem to do anything.

I used the following code:

Dim aThisRecord() As String
Dim i As Integer

For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![Pre-Filled Enrollment Form]!P" & i & "NAME = """ &
aThisRecord(0) & """")
Next i

This should fill in one field per group, for all four groups. Instead, no
fields are filled in. I added a MsgBox call to the For loop so I could make
sure it wasn't just that the records were blank, and that's not the problem;
GetParents is returning the correct values.

I tried hard coding the idea to fill in just one field, like this:

Dim test as String
test = "whee"
Eval ("Reports![Pre-Filled Enrollment Form]!P1NAME = "" & test & """)

And nothing happened.
Click to expand...

Well, you are not doing anything with the result of the
Eval. Your current explanation leads me to believe that
you're trying to run an assignment statement using Eval,
instead of just evaluate an expression (in this case, a
comparison that returns a True or False). If you really do
want an assignment statement, then we've wandered way off
the path. Try using this syntax instead of using Eval:

Me("P" & i & "NAME") = aThisRecord(0)

but shouldn't that 0 be an i?


Also - why are there two extra sets of quotation marks? I realize the
expression I want run is: object = "value", and adding quotes around it
would yield: "object = "value"", so I assume that the extra quotes function
as escape characters, but I'd like to make sure that's the case.
Click to expand...

I think you got it right. The rule is that you have to use
two quotes to one quote inside a quoted string. E.g. to
get
Jon said "Hello" to Bob
as a result, you need to type the literal string
"Jon said ""Hello"" to Bob"
Click to expand...
 
Hi,

I am trying something similar ie creating dynamic variable names an
assigning them values. I tried the following but getting "Unspecifie
error " on the line where Me is used.

I have a simple for loop
for i=0 to 9
Me("tempx" & i) = i
next

Am I missing something?

Thanks

-Jo

Tom said:
*Here's an unexpected result: when I try your suggestion out, I ge
no error.
It also doesn't seem to do anything.

I used the following code:

Dim aThisRecord() As String
Dim i As Integer

For i = 1 To 4
aThisRecord = GetParents(iStudentID, i)
Eval ("Reports![Pre-Filled Enrollment Form]!P" & i & "NAME = """ &
aThisRecord(0) & """")
Next i

This should fill in one field per group, for all four groups
Instead, no
fields are filled in. I added a MsgBox call to the For loop so
could make
sure it wasn't just that the records were blank, and that's not th
problem;
GetParents is returning the correct values.

I tried hard coding the idea to fill in just one field, like this:

Dim test as String
test = "whee"
Eval ("Reports![Pre-Filled Enrollment Form]!P1NAME = "" & test
""")

And nothing happened.

Any more help you can give me would be greatly appreciated.

Also - why are there two extra sets of quotation marks? I realiz
the
expression I want run is: object = "value", and adding quotes aroun
it
would yield: "object = "value"", so I assume that the extra quote
function
as escape characters, but I'd like to make sure that's the case.

--
Tom Rogers
(e-mail address removed)
"The great enemy of clear language is insincerity."
Marshall Barton said:
Tom Rogers wrote: can't[vbcol=seagreen]


It's the array reference that is causing the problem. VBA
variables are not included in the expression service's name
space.

It appears from the way you wrote it, that the array
contains numeric values so it should be:

Eval("Reports![SR4 - VBC]!P" & i & "Name = " _
& aThisRecord(0))

if these names are text strings, then it would be:

Eval ("Reports![SR4 - VBC]!P" & i & "Name = """ _
& aThisRecord(0) & """")
Click to expand...
Click to expand...


-
jubym
 
jubyma said:
I am trying something similar ie creating dynamic variable names and
assigning them values. I tried the following but getting "Unspecified
error " on the line where Me is used.

I have a simple for loop
for i=0 to 9
Me("tempx" & i) = i
next
Click to expand...


That should work as long as tempx1, etc are text boxes on
the report

You used the word "variable" above, and if you really meant
VBA variables declared with Dim tempx1 in a module, then
that will not work. Use an array instead.
 
Back
Top