Running dynamic vba

[robert.thompson.1702]

Hi,

I have a form which has 34 unbound text boxes which are populated with
status messages from various processes that have been run. I want to
save the messages into a history table so that a complete log of all
the messages is kept.

To keep the amount of code I have to write to a minimum I've been
trying to loop round all the text boxes and insert their values into
the history table. The problem I'm having is obtaining the value from
the various text boxes as part of the loop. I'm trying to dynamically
create a reference to the text boxes but can't get vba to recognise
this.

The code I have so far is below. All suggestions as to how to do this
will be gratefully received as I don't want to have to write code for
each text box.

Shout if you need any more info.


Cheers
Rob.

Sub SaveRunHistory()

On Error GoTo errSaveRunHistory

Dim intCounter As Integer
Dim intLoopCount As Integer
Dim strSQL As String
Dim strTxtBox As Variant
Dim strTxtBoxValue As String

intLoopCount = 1

Do While intLoopCount <= 34 ' which is the number of text boxes

'----------- This is the section where I get the problems as I can't
extract the value of the textbox
'----------- currently referenced by the loop to go into the SQL
statement
strTxtBox = "strTxtBoxValue =
Forms!frmrunconvertprocess!txtProcessStatus" & intLoopCount & ".Value"

'strTxtBoxValue = Forms!frmrunconvertprocess!txtProcessStatus &
intLoopCount.Value
'strTxtBoxValue = Forms!frmrunconvertprocess!strTxtBox.Value
'-----------
strSQL = "INSERT INTO RunHistory(ProcessMessage) " & _
"SELECT " & strTxtBox & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

intCounter = intCounter + 1
Loop

Forms!frmrunconvertprocess!txtProcessStatus32 = Now() & ": " &
"Process status messages successfully saved to table RunHistory"
Forms!frmrunconvertprocess!txtProcessStatus32.FontBold = False
Forms!frmrunconvertprocess!txtProcessStatus32.ForeColor =
QBColor(0) ' Black

Forms!frmrunconvertprocess.SetFocus
Forms!frmrunconvertprocess.Repaint

ExitNormal:
Exit Sub

errSaveRunHistory:
MsgBox "Error saving process status messages" & Chr(10) & Chr(10) &
Err.Number & " - " & Err.Description & Chr(10), _
vbMsgBoxHelpButton, _
"Saving Run History", _
Err.HelpFile, Err.HelpContext

Forms!frmrunconvertprocess!txtProcessStatus32 = Now() & ": " &
"Process status messages not saved"
Forms!frmrunconvertprocess!txtProcessStatus32.FontBold = True
Forms!frmrunconvertprocess!txtProcessStatus32.ForeColor =
QBColor(4) ' Red
Resume ExitNormal

End Sub

 

[TC]

You can get it from this simple example:

intCount = 18
msgbox me("txtProcessStatus" & intCount) ' shows value of
txtProcessStatus18

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Albert D.Kallal]

Is is code being run from the particlar form?

use "me" as the form ref here.....

for intLoopCount = 1 to 34
strTPtr = "txtProcessStatus" & intLooCount
strSQL = "INSERT INTO RunHistory(ProcessMessage) " & _
"values (" & me(strTPtr) & ");"
currentdb.Execute strSQL
next i
me.txtProcessStatus32 = Now() & ": " & _
"Process status messages successfully saved to table RunHistory"
me.txtProcessStatus32.FontBold = False
me.txtProcessStatus32.ForeColor = QBColor(0) ' Black
doevents

ExitNormal:
Exit Sub


If the above code is not in the form, then pass the form ref....

Call RunSaveHistory(me)

Sub RunSaveHistory (f as form)


use "f" in place of "me" in the code....

Anway, the way to ref a field in code is

strF = "lastname"

forms!MyFormName(strF)

Or, if the code is running in the form..use me....

me(strF)

Both of the above would ref the lastname field.....

 

[robert.thompson.1702]

Thanks Albert.

I've tried the code and it works like a dream. I knew it was something
simple but just couldn't see it.


Cheers
Rob.