Trying to create dynamic VBA code (or wasting my time?)

  • Thread starter Thread starter IM
  • Start date Start date
I

IM

I don't know if this has come up before. I'd like to dynamically create a
line of VBA code at run time based on values from a table, or a textbox. I
want to use this code to get data from a recordset. The code would vary
depending upon another table which specifies the field name and data type.
Is there a way to do this? Below is a sample code fragment. Clearly, I can
use this approach to build a text string that is displayable in a msgbox,
but can't build the same string as an executable line of code... or can I?

===Begin code fragment======================================================
Private Sub Command8_Click()
Dim strSrcTbl, strTargTbl, strSrcFld, strTargFld, strFldType, strAction As
String
strSrcTbl = Me.SourceTable
strSrcFld = Me.SourceField
strTargTbl = Me.TargetTable
strTargFld = Me.TargetField
strAction = Me.Action
strFldType = Me.FieldType

'Buffers to hold different data types
Dim stringBuff As String
Dim doubleBuff As Double
Dim dateBuff As Date

'Define RecordSets
Dim Db As DAO.Database
Dim rsBrioFeed, rsProgram, rsImportActions As DAO.Recordset
Set Db = CurrentDb()
Set rsBrioFeed = Db.OpenRecordset("BrioFeed", dbOpenTable)
Set rsProgram = Db.OpenRecordset("tblProgram", dbOpenTable)
Set rsImportActions = Db.OpenRecordset("tblImportActions", dbOpenTable)

'I want the next statement (surrounded by asterisks) to build dynamically
based on the values in _
strFldType and strSrcFld so that code is generated like: _
stringBuff=rsBrioFeed.Name or _
dateBuff =rsBrioFeed.StartDate
'*************************************************
'strFldType & "Buff" & "=rsBrioFeed." & strSrcFld 'this won't even compile
'*************************************************
'as in the following message box, which works just fine as a string
MsgBox strFldType & "Buff" & "=rsBrioFeed." & strSrcFld

'OTHER CODE...

===End code fragment======================================================
Am I going bonkers, or is this possible?

Thanks for any help.
IzM
 
Yes it's possible. The first thing with your code is that
you are not getting any particular value from the
recordsets. You're not pointing to any row within the
recordsets. To get the values into the *Buff variables you
need to move to a particular row then set the variable.

Currently it's trying to set a string to contain the whole
of a recordset which, since it hasn't seen a MoveLast, could
be empty or indeterminate. The default used to be MoveFirst
IIRC but don't make that assumption, my memory is random
<g>.

Also this line
'strFldType & "Buff" & "=rsBrioFeed." & strSrcFld
should probably be:
strFldType = strFldType & "Buff"
strFldType = strFldType & "=rsBrioFeed." & strSrcFld

I'd try setting the recordset contents using sql first of
all, for example:-
Set rsBrioFeed = Db.OpenRecordset("SELECT MyField,
AnotherField FROM BrioFeed WHERE MyField = 'xyz''")

For other ideas, in the help look up the Find and Move
Methods for DAO.

And I'd put in break points to check the values of the
variables as it goes along to make sure the right things are
where you want them.
--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades

In IM typed:
 
Thanks very much for your detailed and patient response. Yes, I'm aware of
how to manipulate recordsets, having done so before. Perhaps I was not
clearly expressing what I'm trying to accomplish. I was asking if it is
possible to have a string variable that contains a string of what is
actually a valid VBA statement, and then somehow to run the VBA code
contained in the string. For example, the string below strRunMe actually
consists of a valid VBA statement.

strMsg="Hello"
strRunMe="Msgbox strMsg"

How would I instruct Access to run the VBA statement contained in the string
variable strRunMe?
Now, you may ask why do this? Obviously, the above example is an
oversimplification. What I'm actually trying to do is build dynamic VBA code
that can be set or changed at run time, as dictated by the contents of
variables, fields, etc.

Can this be done in Access VBA? If so, how?

Thanks again for any responses.

IzM
 
VBA isn't designed to handle self-modifying code. There's an Eval()
function which is worth experimenting with, though it's not a
full-powered one like Perl eval.

But if you've got stuff in tables you can get a long way by using the
generic features of recordsets.

You can't generate a line like
DateBuff = rsXXX.StartDate
but you can build a SQL statement and open a recordset on it, and you
can pass a field name into a fixed line of code like this:
VariantBuffer = rsXXX.Fields(strFieldName).Value

And you can have functions like this:
Public Function Operate(X as Variant, Y As Variant, _
Operator As String) As Variant
Select Case Operator
Case "+"
Operate = X + Y
Case "-"
Operate = X - Y
...
 
Ah, I see... No I don't think you can.

As John said, look at Eval() for starters. Use SQL to fill
rst's.

You might consider using Select Case in a function as John
has suggested but pass in an integer to choose the function
to run (which you'll have to spell out) and it's parameters.
OK if you're only interested in a few possible functions but
cumbersome to type and set up parameters if you need a lot
of Cases.

Good luck.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades

In IM typed:
 
Nick and John: Thanks so much for your responses! After Nick's first reply
I continued researching and found the Eval function, which, as you both
pointed out can be used to execute a string but not to the degree of
complexity that I was looking for. I will try your suggestions as an
alternative. Thanks again for your help.
For anyone else reading this, Access help provides info on Eval as does the
following MSDN article:
http://msdn.microsoft.com/library/d...y/en-us/vbaac11/html/acfctEval_HV05186386.asp

IzM
 
Back
Top