Re: Trying to create dynamic VBA code (or wasting my time?)
- From: "Nick Coe \(UK\)" <classicnickNOSPAMAT@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 9 Feb 2006 10:01:09 -0000
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 FBzGf.148399$7l4.21134@trnddc05">news:FBzGf.148399$7l4.21134@trnddc05,
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
"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
.
- References:
- Prev by Date: Re: Compact & Repair Database by VBA Code??
- Next by Date: Re: Trying to create dynamic VBA code (or wasting my time?)
- Previous by thread: Re: Trying to create dynamic VBA code (or wasting my time?)
- Next by thread: Re: Trying to create dynamic VBA code (or wasting my time?)
- Index(es):
Relevant Pages
|
Loading
