Set the value of a public variable whose name is stored in a string

I ran up against a problem not too far removed from ArvInd’s not so long ago.

The idea of using Eval is neat, but Eval seems to have a couple of limitations. It looks as if Eval will evaluate user-defined functions only if these are defined in an ordinary module rather than in a form’s class module. Also, Eval doesn’t seem to like being asked to evaluate strings that contain variable names: it seems unable to translate these names into values or references, which is fair enough. Evaluating user-defined functions is already pretty good.

Running with one of The_Doc_Man's suggestions, why not get Access to write the code that you need for Eval as you need it? E.g. suppose your user is working on form1 and wants to set the value of var1. When the user specifies this, code based around the following is run:

Code:
Dim mdl as Module

Set mdl = Modules("bas_Get_Set")

If not mdl.Find("form1_var1",1,0,mdl.CountOfLines,1) then
    add_code_for "form1_var1"
End If

The module that will store all the code you need for this functionality (bas_Get_Set, say) is opened. VB searches though it to see if "form1_var1" exists. If not, add_code_for adds the required code -


Code:
mdl.AddFromString "Public form1_var1  As Integer"

mdl.AddFromString "Public Function SET_form1_var1(var_val)" & vbcr & _
                  "    form1_var1 = var_val" & vbcr & _
                  "End Function"

DoCmd.Save acModule, "bas_Get_set"
Set mdl = nothing

The variable has now been declared, and a function to set the value of this variable has been written.

When the user supplies the value to which this variable should be set, (101, say) code similar to the following is run:

Code:
Eval "SET_" & form_name & "_" & var_name_supplied_by_user & "(" & user_Supplied_Value & ")"

This would evaluate to: Eval "SET_form1_var1(101)"
Which in turn calls the automatically written function SET_form1_var1(101), which sets the value of the variable form1_var1 to 101.

There are at least a couple of problems with this approach. As far as I know, the variables will need to be stored in an ordinary module rather than in a form’s class module. However if the variable names are prefixed by the name of the form, this shouldn’t be too difficult to sort.

The other problem is, code that declares a new variable and inserts a function to set its value must finish running before it will be possible to use it to assign a value to the variable you’ve just created. This means you’ll need to do the thing in two steps: the user specifies the name of the variable, code runs to check if this exists, creating the necessary declaration and functions if it doesn’t. Then, in a separate event, the user supplies the value that this variable should be set to.
 
ChrisO, u have proved that u r consistent in ur vulgarity too and i was right that u r uncivil. U have a vulgar,weak and defeated mind trying to defend itself by claiming of having experiences with others. I do not have loads of free time like u and am too busy a man to indulge in mud-slinging.

Edited to add: And i must say u have a great sense of humor and it would be great if everyone started using the same language as u. It is for the other members to decide and try out but certainly not me. I maintain my own dignity even though u prove urself a hooligan.

Back to work, Adam_fleck i will certainly try to look into the workaround posted by u.

Let me clarify two things :-

Adam wrote

I ran up against a problem not too far removed from ArvInd’s not so long ago.


So my problem is not merely hypothetical. I am at a stage of design in my software where i needed to do this (as stated by my first post) although i cannot describe the exact purpose behind it. Also, i knew that there was little chance of a direct solution (but i couldn't be sure as i have been helped a lot by forums) and it needed a workaround.

I am trying out those suggested by our no-nonsense members apart from ChrisO and am grateful to them particularly the Doc_Man and Adam_Fleck.
 
Last edited:
ReAn…

I believe you have a great sense of humor, and I personally love it. :)

It is, in my view, refreshing to hear someone speak that way.
(BTW…how did you know about my balls?…they dropped so long ago they actually fell off. :( )

[Shift frequency up one octave]
In the future I will try to be a good boy…(ah well, too late for that in more ways than one. ;) )
[/Shift frequency up one octave]

I sense you are a delightful young lady, good at math and ‘C’ too…
I never got the chance to welcome you to this site so, belated as it is…welcome. :D

Mile-O…
“This is better than Pay Per View.” No it’s not…I’m not getting paid. :(
I read another post that asked for something like a Religious and/or Political forum…under the circumstances, do we really need it??? :rolleyes:


Rich…
Seconds may reenter the ring. :D

Adam…
Good luck. ;)

Arvindn…



Regards to all,
Chris.
 
arvindn,

Interesting problem ... and discussion.

I have no intention of ever using this, but it is possible with
some VBA to write VBA. I just had to try this because lately
I've been using T-SQL & VBA to write SQL Server Stored Procedures.
I don't think that Access likes these techniques much; it caused
numerous crashes while developing, but it does work.

On a form named "MyForm" declare the Public Variable:

Public varTest As String

Then call a private function/sub with the name of the variable
and a value to assign it:

cmdSet("varTest", "HelloWorld")

Code:
Private Sub cmdSet (WhatVariable As String, TheValue As String)
Dim mdl As Module
Dim lngCount As Long

'
' Get the Module, find the function, delete it, rewrite it.
' Set the variable WhatVariable to "HelloWorld"
'
  DoCmd.OpenModule "Module1"
  Set mdl = Modules("Module1")
  lngCount = mdl.ProcStartLine("DummyFunction", 0)
  mdl.DeleteLines lngCount, 4
  mdl.InsertText "Public Function DummyFunction()" & vbCrLf & _
                 "   Forms![frm_Search]." & WhatVariable & " = """ & TheValue & """" & vbCrLf & _
                 "End Function" & vbCrLf
  DoCmd.Close acModule, "Module1", acSaveYes
  DoEvents
  SetVariable
  MsgBox (varTest)
End Function

Module1:

Code:
Option Compare Database

Public Function SetVariable()          <-- This function is always here and exists
  DummyFunction                            just to call DummyFunction.  This way 
End Function                               the calling form has a valid function to call.

Public Function DummyFunction()        <-- This function is recreated every time.
   Forms![frm_Search].varTest = "HelloWorld"
End Function

Personally, this wasn't really worth the effort, and I don't think that
I'll ever programatically work with modules again.

Also, I don't understand all the bickering. I missed a few of the posts on
this thread. Have to read them now ... I need a good chuckle.

Wayne
 
Damn!

Just reread this whole thing and see that Adam had the same answer a
couple posts ago.

Oh well ...

Wayne
 
Can it be done? Yes, because it (or something like it) has been done.

Sorry for the long line of code

There is a line of code somewhere in this thread and at this line; -
Code:
vbNewLine & vbTab & vbTab & vbTab & "strFunctionCalled = " & Chr$(34) & "=MouseMoveNames(" & Chr$(34) & " " & Chr$(38) & " " & "Chr$(34)" & " " & Chr$(38) & " strFieldName " & Chr$(38) & " " & "Chr$(34)" & Chr$(38) & " " & Chr$(34) & ")"
Produces in the Form_Open event handler: -

Code:
strFunctionCalled = "=MouseMoveNames(" & Chr$(34) & strFieldName & Chr$(34) & ")"
Which for any give line of source code at runtime equates to: -

"=MouseMoveNames("Box5”)”…(or whatever number Box iteration.)

so that the event handler for Box5, for example, looks like: -
=MouseMoveNames("Box5”)

In the Form_Open event handler, it redirects all MouseMove events to a common event handler where strFieldName is a Local variable to the class Module that has already been constructed in code.

In effect, that long line of code is code… it is writing code that subsequently writes code that when the Form is opened...writes code. Four levels of a “slight of hand”.
(Please remember that the long line of code is also dealing with embedded variables even though they are Local to a Private Subroutine within a Forms Class Module.)

Can it be done? Sure!

But I think you may be chancing your arm, for many reasons, at stupidly.

That sort of thing is NOT for production code.

It is little more than a ‘slight of hand’.

Regards,
Chris.
 
What's fascinating about this thread is that the Doc Man, dancing around during the heat of the battle, managed to receive not even a glancing blow. That's fancy footwork.

Regards,
Tim
 

Users who are viewing this thread

Back
Top Bottom