Eval Function & Variable

[Michael H]

Hi.

I have many lines of code similar to this:
txtT = Replace(txtT, "<<UserEmail>>", DLookup("Email", "Users", "ID=" &
UserID))

where UserID is a variable whose value is set within the function before the
above such code is run (see below).

I have attempted to automate this by creating a table called FieldSubs, with
fields: ID (AutoNumber), SubCode (Text), and SubWith (Text).
In the SubCode field, I have values such as: <<UserEmail>>
And in the SubWith field: DLookup("Email", "Users", "ID=" & UserID)

So, my code is now as follows:



Public Function ProcessFieldSubstitutions(txtT As String, lngLogID As
Integer) As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("FieldSubs", dbOpenDynaset, dbReadOnly)

Dim UserID As Long
UserID = DLookup("UserID", "qryLastLog", "ID=" & lngLogID)

Do Until rs.EOF
txtT = Replace(txtT, rs.Fields("SubCode"), Eval(rs.Fields("SubWith")))
rs.MoveNext
Loop

ProcessFieldSubstitutions = txtT

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function



However, the string now being Evaluated within the loop is exactly this:
DLookup("Email", "Users", "ID=" & UserID)
when I would like it to be this:
DLookup("Email", "Users", "ID= 13")

How do I get this to work where I can use a variable in the Eval string that
is given a value within the Procedure? It must be able to handle the exact
string in the SubWith field, as not all values in that field make use of a
variable in the Procedure.

By the way, this does work as expected if the value in the SubWith field is
something like this:
DLookup("Email", "Users", "ID= 13")

I hope I have explained this well enough, and provided enough information
for someone to be able to point me in the right direction. Any help would be
appreciated.

Thanks.

-Michael

 

[Damian S]

Hi Michael,

After reading and re-reading your post several times, I have found your
issue...

You need to use "replace" to replace the UserID portion of your SubWith with
the proper field, like this:

txtT = Replace(txtT, rs.Fields("SubCode"),
Eval(replace(rs.Fields("SubWith"), "UserID", UserID)))

Hope this helps.

Damian.

 

[Michael H]

Hi Damian.

That solves the problem. Thanks very much for your help.

-Michael