TempVars within TempVars (1 Viewer)

[GrandMasterTuck]

Is it possible to store a TempVar that contains another TempVar within it? For instance:

I want to store a line of greeting in a TempVar, and allow this TempVar to fetch a person's name from another TempVar and embed it in the right place. Like this:

TempVars!Greeting.Value = "Good Afternoon, " & TempVars.TheName & ", how are you today?"

TempVars!TheName = "Jeffrey"

And have the resulting line output like so: "Good Afternoon, Jeffrey, how are you today?"

Note that I do NOT want to STORE the word JEFFREY in the Greeting TempVar, I just want the Greeting TempVar to fetch the data that's stored in the TempVar TheName and place it in the right spot inside that textbox on my form, a textbox who's control source is the TempVar called Greeting.

The purpose of doing this is so that I may create a report that has an introductory paragraph, in which SEVERAL other variables are called (Employee Name, Hire Date, First Day of Work, Scheduled Days Off, etc...) throughout the paragraph. I want to allow the owner to change the wording of the paragraph easily (I provide a form with a field into which he can view and edit the code that generates that paragraph of text, and he can rewrite it as needed) without forcing him to go into design view for the form and edit the code from there.

Is this possible?

 

[Minty]

They don't need to be temp vars - any variable can do what you are looking at.

 

[static]

A class or type.

Private Type tv
    greeting As String
    TheName As String
End Type

Private Sub Command0_Click()
    Dim TempVars As tv
    TempVars.TheName = "Jeffrey"
    TempVars.greeting = "Good Afternoon, " & TempVars.TheName & ", how are you today?"
    
    Debug.Print TempVars.greeting
End Sub

 

[GrandMasterTuck]

A class or type.

OK, I see you used an OnClick event to set the value of the TempVar. What if I were to want to store the value of TempVars!Greeting into a table, and have a DLookUp fetch it on the report? How would I store that TempVars in such a way that it would go fetch TempVars!TheName when called?

I want to store this in the table: "Good Afternoon, " & TempVars.TheName & ", how are you today?" ... say tblGreetings, with primary key GreetingID, row 1.

Then use DLookUp("[Greeting]", "tblGreetings", "[GreetingID]=1") on the report, and the report will print "Good Afternoon, Jeffrey, how are you today?" because the value "Jeffrey" is stored in TempVars.TheName.

Is it possible to store that line (or some permutation thereof) in a table and have it function correctly when displayed on the form? What I do NOT want to do is store the PIECES in the table (like store "Good Afternoon, " as PIECE1, and store ", how are you today?" as PIECE2, then try to assemble the line of text on the report with =[PIECE1] & [TempVars]![TheName] & [PIECE2] or something like that.

I want the boss to easily be able to rewrite that paragraph, and easily call the TempVars he chooses in the spots he wants them to appear, and I don't want to allow him to go into Design view on the report and muck with the code. because he will utterly wreck it, and then I will have to fix it.

Essentially I'm looking to store a template for a sentence in a table, and this template needs to contain variables (TempVars), but if I do it the way I think it should work, it doesn't work. Instead, on my report, I get exactly this: Good Afternoon, " & TempVars.TheName & ", how are you today? because it's interpreting that line of code as a string in and of itself, and not executing the function of assembling a string using the embedded TempVar.

 

[static]

If the user has no control of the main string you can do something like

Good $0$, $1$, how are you today?

You would then pass the string to a function have the function replace the placeholder characters with the actual values.

 

[static]

eg

Private Sub Command0_Click()
    
    Debug.Print MyReplace("Good $0$, $1$ how are you today?", "morning", "Jefferey")
    
    Debug.Print MyReplace("Good $0$, $1$ how are you today?", "afternoon", "Jefferey")
    
    Debug.Print MyReplace("Good $0$, $1$ how are you today?", "evening", "Jefferey")

End Sub

Public Function MyReplace(str As String, ParamArray vals() As Variant)
    MyReplace = str
    For i = 0 To UBound(vals)
        MyReplace = Replace(MyReplace, "$" & i & "$", vals(i))
    Next
End Function

Where the strings come from makes no difference.

 

[GrandMasterTuck]

If the user has no control of the main string

I think that's my problem. I want to give him control over the main string without giving him access to Design View on the form/report. So if the string right now is "Good Morning, " & TempVars!TheName & ", how are you today?" but he wants to change it to "Good Afternoon, " & TempVars!TheName & ", today's date is " & Date(), he can do that.

I want to store that bit of code up there in a table, and fetch the value of that record on my form/report.

 

[static]

Unless this is more complicated than your example suggests, just split the string up.

 

[GrandMasterTuck]

The purpose of doing this is so that I may create a report that has an introductory paragraph, in which SEVERAL other variables are called (Employee Name, Hire Date, First Day of Work, Scheduled Days Off, etc...) throughout the paragraph. I want to allow the owner to change the wording of the paragraph easily (I provide a form with a field into which he can view and edit the code that generates that paragraph of text, and he can rewrite it as needed) without forcing him to go into design view for the form and edit the code from there.

Yeah, unfortunately it's way more complex. Variables may need to change places often, and there are about six of them in the paragraph of text. I need to enable the boss to make edits WITHOUT letting his destructive rear-end into Design View.

 

[static]

Well, you can't put myform!mycontrol.value (or whatever) into a string. Access wont recognise it. You would need to parse it out, find those objects, make sure they are open/exist, validate their data... you don't want to go there.

If the users have full control of the string then you'll need to educate them on how they have to use it.

 

[GrandMasterTuck]

I'm not looking to put a CONTROL into a string, so I'm pretty sure I don't need to check if anything is open. I find that I'm once again supplying examples that aren't adequately illustrating what I hope to accomplish. Allow me to try again, with a better example.

I have an access app that prints REPORTS for each employee in the database. The REPORT has an introductory paragraph at the top of it, like this:

TO WHOM THESE PRESENTS MAY COME:

By virtue of the power in me vested by the Statute in which such case made and provided, I, Jeffrey Williams, District Court Judge for the 212th District of Missouri, do hereby appoint AMY SMITH Court Reporter, during her tenure as, and by virtue of her occupying the position of DISTRICT COURT CLERK during the term of my elected office, which ends DECEMBER 21st, 2019, to serve during my pleasure so long as valid insurance and bond requirements are maintained.

Everything in BOLD RED in the paragraph above are variables that need to be supplied to the report. Now, under normal circumstances, I'd just put a TEXT BOX on the report with it's source code set to code that will generate this kind of string of text. The problem is that my boss may want to CHANGE THAT PARAGRAPH'S WORDING after I'm gone.

So what I want to do is store that PARAGRAPH in code form in a TABLE, and allow the boss to view and edit the code in a FIELD on a FORM in the APP, but WITHOUT GRANTING HIM THE ABILITY TO GO INTO DESIGN VIEW. Because, quite frankly, he doesn't know what he's doing, and will screw the report up.

If I can figure out how to store the CODE in a table, wherein several TempVars or other variable types are fetched, and get the report to EXECUTE the code (which assembles a string of normal text) and display the RESULTING string on it, that would solve my problem.

Whether we're talking about embedding TempVars in that code, or simply using field references or DLookUps, I don't care. Makes no difference. The important thing is that this CODE (which has references to TempVars, or references to DLookUps, or references to fields, or any other type of variable reference) is able to be edited by a person that DOES NOT HAVE ACCESS TO DESIGN VIEW.

Isn't there any way to do this?

 

[static]

You don't store CODE in tables. You store DATA.

There are 2 ways of changing a string. Adding bits together or replacing certain parts.

If your boss wants to change some wording, you let him change the parts of the wording that he's allowed to change. You then concatenate all the strings together.

If you want him to be able to change the whole thing you have to come up with a plan like my MyReplace function. But you would have to tell him how to use it. If he didn't use it properly it would generate incorrect results but that would be his fault.

 

[stopher]

Save your paragraph like this:

TO WHOM THESE PRESENTS MAY COME:

By virtue of the power in me vested by the Statute in which such case made and provided, I, [COLOR="Red"][name1][/COLOR], 
District Court Judge for the 212th District of Missouri, do hereby appoint [COLOR="red"][name2] [task][/COLOR], during her tenure as, and by virtue of her 
occupying the position of [COLOR="Red"][position][/COLOR] during the term of my elected 
office, which ends [COLOR="red"][date1][/COLOR], to serve during my pleasure so long as valid insurance and bond 
requirements are maintained.

Then replace the key fields using a replace function as require. The paragraphs can be stored in their own table and editable via a form. The user will need to know the relevant field names (I've chosen poor names but obviously you could choose more appropriate one).

The values of the field can be stored in another table - again editable.

I don't see any need for tempvars or the like. Just a nice function that takes the paragraph and field name values as input and churns out the paragraph with the replacements done. If there are many possible field names then consider having an array as an input or having arguements as optional pairs fieldname as string, fieldvalue as string,...

A lot of this is what static is saying so hopefully a different perspective will help.

hth

 

[static]

Thanks stopher

that would be my MyReplace function

$0$ , $1$ is just a short and easy way of writing the code but if you wanted to get verbose/more complex you could use names.

 

[stopher]

Thanks stopher

that would be my MyReplace function

$0$ , $1$ is just a short and easy way of writing the code but if you wanted to get verbose/more complex you could use names.

Yes, your function as a good example. I'd recommend names because the user will see them in the text they are editing. For users it is helpful if they are meaningful so they can write their paragraph naturally choosing the fields they want to use as they see fit.

The user does not need to be aware of the function or how to use it. Only the syntax of the fieldnames.

 

[jdraw]

I agree with stopher and the adjustment by static, BUT beware-- you have her within the text-- you'll have to make sure that all adjectives/pronouns etc etc match your other text.

I had some experiences where there was "boiler plate" texts and positional identifiers to be mass produced with name/address lists. It is important to analyze your requirement; test it until it works consistently.

And these days, political and gender correctness gets lots of attention so be prepared, and check it twice.

Good luck.

 

[static]

@stopher

Yep. And if you want to go with field names

Public Function MyReplace(str As String, qry As String)
    Dim rs As dao.Recordset, f As dao.Field
    Set rs = CurrentDb.OpenRecordset(qry)
    
    MyReplace = str
    
    For Each f In rs.Fields
        MyReplace = Replace(MyReplace, "[" & f.Name & "]", f.Value)
    Next
End Function

it starts to get interesting. Because you can have multiple variations of the string(s).

 

[The_Doc_Man]

Another way of doing this is a MailMerge using a Word Application Object, with the boilerplate in a base Word document that could be edited with Word, and the fields to be replaced are specially marked for word. The problem is that again, someone who follows behind you will need to actually know something about Office.

Face it, in our business there are those who can put strings together to get an intelligible sentence, and those whose visceral grunts speak words of a different scale of articulation reminiscent of dyspeptic dragons. Your FINAL barrier is not the ability to set this up. It will be the necessity of your follower to READ THE F***ING MANUAL. (UNIX sys admins are already familiar with the RTFM problem.)

 

[static]

actually that code is a load of pap as it stands but, it could be quite flexible.

 

[GrandMasterTuck]

Stopher and static, you guys win the internet. The REPLACE function does exactly what I was hoping for. It just took Stopher to explain it in a way in which it clicked for me... I wasn't getting it when you explained it at first, static. Sorry for that!

In related news, I figured out another way to do it, with the EVAL function. As it turns out, EVAL won't work if I use TempVars as the variables, but if I use a DLookUp to fetch the value from a table, it works perfectly. So my stored code is as follows:

TO WHOM THESE PRESENTS MAY COME:

By virtue of the power in me vested by the Statute in which such case made
and provided, I, " & [COLOR="Red"]DLookUp("[JudgeName]", "tblAffidavitRecord", "[AffidavitID]=" & [AffID])[/COLOR]
& ", District Court Judge for the 212th District of Missouri, do hereby appoint " & 
[COLOR="red"]DLookUp("[EmployeeName]", "tblAffidavitRecord", "[AffidavitID]=" & [AffID])[/COLOR] & " " & 
[COLOR="red"]DLookUp("[EmployeeTitle]", "tblAffidavitRecord", "[AffidavitID]=" & [AffID])[/COLOR] & ", 
during her tenure as, and by virtue of her occupying  the position of " & 
[COLOR="red"]DLookUp("[EmployeeTitle]", "tblAffidavitRecord", "[AffidavitID]=" & [AffID])[/COLOR] & " 
during the term of my elected office, which ends " & [COLOR="red"]DLookUp("[TermExpires]", "tblJudgeInfo", 
"[JudgeID]=" & [JdgID])[/COLOR] & ", to serve during my pleasure so long as valid insurance and bond 
requirements are maintained.

And my field's source code is:

=Eval(DLookUp("[AffidavitParagraph]","tblAffidavitSettings"))

Now it turns out that the REPLACE function is way more elegant, so I'll be implementing that solution instead, because frankly, this block of code is ugly and complicated. True, it does what I want it to do, but it's nasty looking and inefficient. Plus it will be way harder for the boss to figure out where to put all the ampersands and quote marks. REPLACE is much nicer.

These kinds of solutions have far-reaching application possibilities, because I can use the knowledge I gain as to what works and why it works to solve other unrelated issues I come across. This has taught me a lot about the Eval function, as well as about the Replace function.

Thanks again, you guys.

I agree with stopher and the adjustment by static, BUT beware-- you have her within the text-- you'll have to make sure that all adjectives/pronouns etc etc match your other text.

Yep, that's my tendency to use utter crap examples and fail to think them through before posting. You'll notice I'm rather skilled at that. Agreed, though, I'll have to code in a way for the system to adjust the pronouns depending on the gender of the person, or perhaps convince the boss to allow some clever vagueness with said pronouns, otherwise I'll sooner or later be forced to answer to a cranky warrants clerk whom I've chosen to refer to as 'she' whom also happens to urinate standing, lift weights and practice muai thai (though hopefully not all at once). A battle for another day, methinks.

Face it, in our business there are those who can put strings together to get an intelligible sentence, and those whose visceral grunts speak words of a different scale of articulation reminiscent of dyspeptic dragons.

Though my vocabulary skill level only permits me to understand around 72% of this comment, I can't help but wholeheartedly agree on faith alone. LOL

EDIT: I'M WRONG AGAIN! (Reeeeeeealy good at that!) As it turns out, the EVAL function DOES allow TempVars within the code! I was getting blanks when I viewed the Report because the TempVar I was calling in that code hadn't been set, because it gets set when the FORM opens, and I wasn't pulling the report from the form (whereupon there is a button that opens the report)! So yes, I can do it with TempVars in an EVAL function, like so:

TO WHOM THESE PRESENTS MAY COME:

By virtue of the power in me vested by the Statute in which such case made
and provided, I, " & [COLOR="Red"][TempVars]![JudgeName][/COLOR] & ", District Court Judge for the 
212th District of Missouri, do hereby appoint " & [COLOR="red"][TempVars]![EmployeeName][/COLOR] & " " &
[COLOR="red"][TempVars]![EmployeeTitle][/COLOR] & ", during her tenure as, and by virtue of her occupying
the position of " & [COLOR="red"][TempVars]![EmployeeTitle][/COLOR] & "during the term of my elected office, 
which ends " & [COLOR="red"][TempVars]![TermExpires][/COLOR] & ", to serve during my pleasure so long as 
valid insurance and bond requirements are maintained.