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

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
...


On Thu, 09 Feb 2006 04:32:37 GMT, "IM" <izmNOSPAM@xxxxxxx> wrote:


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

"Nick Coe (UK)" <classicnickNOSPAMAT@xxxxxxxxxxxxxxxxxx> wrote in message
news:ObkGNlILGHA.3164@xxxxxxxxxxxxxxxxxxxxxxx
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 VcfGf.8874$Gg1.2318@trnddc03">news:VcfGf.8874$Gg1.2318@trnddc03,
IM typed:
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





--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Relevant Pages