Hi Phil,
You could solve your scope problem and your parameter passing problem in one shot by using CallByName().
Code:
Private Sub Command0_Click()
Call DoIt("tblRecords", "GenericCodeStub1")
End Sub
Sub DoIt(TableName As String, CodeStub As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim nRecords As Long, r As Long
Set db = CurrentDb()
Set rs = db.OpenRecordset(TableName, dbOpenDynaset)
rs.MoveLast
nRecords = rs.RecordCount
rs.MoveFirst
For r = 1 To nRecords
rs.Edit
Call CallByName(Me, CodeStub, VbMethod, rs, r)
rs.Update
rs.MoveNext
Next r
rs.Close
db.Close
End Sub
Public Sub GenericCodeStub1(rs As DAO.Recordset, recnum As Long)
'== Code to process individual records of a table
End Sub
CallByName()'s last argument is a paramarray, so you can pass as many parameters as you like. Only catch is the first argument of CallByName() takes an object reference, so it can only be used in some type of class module (an Access form will do). You can't use it in standard modules.
If form some reason you don't want your DoIt method in a form module, create a custom class and call DoIt with something like:
Code:
' Instantiate class
Dim objMyObject As clsMyClass
Set objMyObject = clsMyClass
' Call DoIt()
Call objMyObject.DoIt("tblRecords", "GenericCodeStub1")
CallByName() would then have its object reference. Only catch is all your CodeStub methods in clsMyClass would need to be declared with public scope so you'd loose encapsulation. They'd all be exposed. Which maybe isn't a problem. But CallByName() can only see public class members.
HTH,
Bob