Run VBA Sub or Function based on recordset 1

[bradmaunsell]

I have a table with records conatianing fields for a document code number, document name and other field stuff.

I want to create a recordset selecting some assortment of records.

Next I want to use a "Do until rs.EOF" routine to step through the recordset and execute VBA sub (or function) for each record. The subs stored in a module "WordEndorsementTemplates". There are dozens of templates and I have no problems with the Word routines when run one at a time.

For example, I want the recordset to drive the printing of a form FPE101, a form FPE106 , A form FPE120, etc.

I have failed with RunCode from VBA (I don't want a macro thing).

Thanks,
Brad
South Burlington, VT

 

[bradmaunsell]

PS: I know a CASE will handle this but I don't want "hard-code" a test every time we add a new word template.
Brad

 

[RobBroekhuis]

I'm not entirely clear on what you're trying to do or where your problem is, but if it involves calling a macro by it's name in a string variable, the run method does that:

s="MyMacro"
run s (,arguments)

Rob

 

[bradmaunsell]

Thanks for the response Rob. You are correct. I am trying to start a sub (or function) using a string to name it.

This code doesn't like the method. I have tried using a function instead of sub without success. Can you see my fault?

Thanks again.

Here is a some sample code.

Sub cmdRun_Click()
' THIS IS IN ACCESS 97. Although I am starting Word, the question is the same if all were Access subroutines.
' Sample Recordset
' AppID EndFunction EndFormTitle Etc.
' 5750 FPE101 General Change Endorsement
' 5750 FPE107 Discretionary Accounts Exclusion
' 5750 FPE109 Specific Planner/Advisor Exclusion

Dim dbs As Database
Dim rs As Recordset
Dim strSQL As String
Dim subroutine
Dim fn, x

Set dbs = CurrentDb()
strSQL = "SELECT tblEndorsements.AppID, tblEndorsementForms.EndFunction, tblEndorsementForms.EndFormTitle " _
& "FROM tblEndorsements " _
& "INNER JOIN tblEndorsementForms ON tblEndorsements.EndFormID = tblEndorsementForms.EndFormID " _
& "WHERE (((tblEndorsements.AppID)=5750));"

Set rs = dbs.OpenRecordset(strSQL)

Do Until rs.EOF
subroutine = "CreateWordDoc" & rs!EndFunction
Run subroutine
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

' collection of subroutines to create various Word documents

Sub CreateWordDocFPE101()
' use FPE101 template and populate with database stuff
MsgBox "FPE101"
CreateWordDocFPE101 = True
End Sub
Sub CreateWordDocFPE102()
' use FPE102 template and populate with database stuff
MsgBox "FPE102"
End Sub
Sub CreateWordDocFPE103()
' use FPE103 template and populate with database stuff
MsgBox "FPE103"
End Sub
Sub CreateWordDocFPE104()
' use FPE104 template and populate with database stuff
MsgBox "FPE104"
End Sub
Sub CreateWordDocFPE105()
' use FPE105 template and populate with database stuff
MsgBox "FPE105"
End Sub
Sub CreateWordDocFPE106()
' use FPE106 template and populate with database stuff
End Sub
Sub CreateWordDocFPE107()
' use FPE107 template and populate with database stuff
MsgBox "FPE107"
End Sub
Sub CreateWordDocFPE108()
' use FPE107 template and populate with database stuff
MsgBox "FPE108"
End Sub
Sub CreateWordDocFPE109()
' use FPE107 template and populate with database stuff
MsgBox "FPE109"
End Sub

 

[RobBroekhuis]

I don't see anything wrong off-hand. What error message do you get? If you debug, does the subroutine variable have the correct name?
Rob