String to VBA Code Part 2 - Access 2003

[Craig]

I have managed to get many strings to work but having following with the
following code:

Dim strA as string
Dim strMessage as string
Dim strLine as string

If Me.Text = "Yes" then
strA = "True"
Else
strA="False"
End If

So now, if I use a text statement from a table (tblText), where the ID = 1
"Hello world, this statement is " & strA

and call this within the module

strMessage = DLookup("Statement","tblText","ID = 1")
strLine = Eval(strMessage)
debug.Print strLine

I get an error: Run-time error '2482' application can't find the name
"strA" you entered in the expression.


The problem here is, that it will not call the value for strA. Is there a
way to get this data into the string. Combine the module and the text.


What I am trying to achieve here is, editable emails. I have a database
that produces emails templates based on the criteria entered into the client
form and calls data throughout the email. It is currently hard coded into
the module with a mixture of VBA to call the data and HTML to write/format
the email. The only way to update the template is to edit the module code,
which is only available by the one programmer.

If I can place the text (body of the email) into a table, then I can assign
users to update the email text.

Any help would be greatly appreciated

 

[Marshall Barton]

I have managed to get many strings to work but having following with the
following code:

Dim strA as string
Dim strMessage as string
Dim strLine as string

If Me.Text = "Yes" then
strA = "True"
Else
strA="False"
End If

So now, if I use a text statement from a table (tblText), where the ID = 1
"Hello world, this statement is " & strA

and call this within the module

strMessage = DLookup("Statement","tblText","ID = 1")
strLine = Eval(strMessage)
debug.Print strLine

I get an error: Run-time error '2482' application can't find the name
"strA" you entered in the expression.


The problem here is, that it will not call the value for strA. Is there a
way to get this data into the string. Combine the module and the text.


What I am trying to achieve here is, editable emails. I have a database
that produces emails templates based on the criteria entered into the client
form and calls data throughout the email. It is currently hard coded into
the module with a mixture of VBA to call the data and HTML to write/format
the email. The only way to update the template is to edit the module code,
which is only available by the one programmer.

If I can place the text (body of the email) into a table, then I can assign
users to update the email text.

VBA variables are unknown outside of VBA modules and Eval
expressions are evaluated by the expression service.

You need to find another way (possibly using the Replace
function?) to insert values in the text.

 

[Dirk Goldgar]

I have managed to get many strings to work but having following with the
following code:

Dim strA as string
Dim strMessage as string
Dim strLine as string

If Me.Text = "Yes" then
strA = "True"
Else
strA="False"
End If

So now, if I use a text statement from a table (tblText), where the ID = 1
"Hello world, this statement is " & strA

and call this within the module

strMessage = DLookup("Statement","tblText","ID = 1")
strLine = Eval(strMessage)
debug.Print strLine

I get an error: Run-time error '2482' application can't find the name
"strA" you entered in the expression.


The problem here is, that it will not call the value for strA. Is there a
way to get this data into the string. Combine the module and the text.


What I am trying to achieve here is, editable emails. I have a database
that produces emails templates based on the criteria entered into the
client form and calls data throughout the email. It is currently hard
coded into the module with a mixture of VBA to call the data and HTML to
write/format the email. The only way to update the template is to edit
the module code, which is only available by the one programmer.

If I can place the text (body of the email) into a table, then I can
assign users to update the email text.

Any help would be greatly appreciated

I don't believe you're going to be able to do this with any kind of
variable, even if you declare it with global scope. But you could create a
public function or property that returns the value of the variable, and
reference that in strMessage. However, that involves preprogramming for the
specific variables you're going to support. If you're going to do that, you
might as well preprogram *all* the acceptable variables, and just use a
series of calls to the Replace function to replace tags with values, like
this:

strMessage = _
Replace(strMessage, "[ClientName]", Me.ClientName)

strMessage = _
Replace(strMessage, "[ClientID]", Me.ClientID)

strMessage = _
Replace(strMessage, "[strA]", strA)

 

[Craig]

I have managed to get many strings to work but having following with the
following code:

Dim strA as string
Dim strMessage as string
Dim strLine as string

If Me.Text = "Yes" then
strA = "True"
Else
strA="False"
End If

So now, if I use a text statement from a table (tblText), where the ID =
1
"Hello world, this statement is " & strA

and call this within the module

strMessage = DLookup("Statement","tblText","ID = 1")
strLine = Eval(strMessage)
debug.Print strLine

I get an error: Run-time error '2482' application can't find the name
"strA" you entered in the expression.


The problem here is, that it will not call the value for strA. Is there
a way to get this data into the string. Combine the module and the text.


What I am trying to achieve here is, editable emails. I have a database
that produces emails templates based on the criteria entered into the
client form and calls data throughout the email. It is currently hard
coded into the module with a mixture of VBA to call the data and HTML to
write/format the email. The only way to update the template is to edit
the module code, which is only available by the one programmer.

If I can place the text (body of the email) into a table, then I can
assign users to update the email text.

Any help would be greatly appreciated

I don't believe you're going to be able to do this with any kind of
variable, even if you declare it with global scope. But you could create
a public function or property that returns the value of the variable, and
reference that in strMessage. However, that involves preprogramming for
the specific variables you're going to support. If you're going to do
that, you might as well preprogram *all* the acceptable variables, and
just use a series of calls to the Replace function to replace tags with
values, like this:

strMessage = _
Replace(strMessage, "[ClientName]", Me.ClientName)

strMessage = _
Replace(strMessage, "[ClientID]", Me.ClientID)

strMessage = _
Replace(strMessage, "[strA]", strA)


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Thanks Dirk

This will give me something to work with.

Craig