Phil wrote:
[Quoted Text] >I would like to pass a pointer to a function/subroutine written in VBA (I
>know you can pass one to a builtin DLL routine) and run it with data
>specified within the calling routine.
>
>Let me flesh it out a bit more for you with the following code:
>
>Public Sub DoIt(TableName As String, Optional CodeStub As Integer = 1)
>'== Skeleton code for processing all records in a table
>
>Dim db As Database
>Dim rs As Recordset
>
>Dim nRecords As Long, r As Long
>
> If CodeStub < 1 Or CodeStub > 9 Then Exit Sub
>
> Set db = CurrentDb()
> Set rs = db.OpenRecordset(TableName, dbOpenDynaset)
>
> rs.MoveLast
> nRecords = rs.RecordCount
> rs.MoveFirst
>
> For r = 1 To nRecords
> rs.Edit
>
> Select Case CodeStub
> Case 1
> GenericCodeStub1 rs, r
> Case 2
> GenericCodeStub2 rs, r
> Case 3
> GenericCodeStub3 rs, r
> Case 4
> GenericCodeStub4 rs, r
> Case 5
> GenericCodeStub5 rs, r
> Case 6
> GenericCodeStub6 rs, r
> Case 7
> GenericCodeStub7 rs, r
> Case 8
> GenericCodeStub8 rs, r
> Case 9
> GenericCodeStub9 rs, r
> End Select
>
> rs.Update
> rs.MoveNext
> Next r
>
> rs.Close
> db.Close
>
>End Sub
>
>Public Sub GenericCodeStub1(rs As Recordset, recnum As Long)
>'== Code to process individual records of a table
>End Sub
>
>Public Sub GenericCodeStub2(rs As Recordset, recnum As Long)
>'== Code to process individual records of a table
>End Sub
>...
>
>Public Sub GenericCodeStub9(rs As Recordset, recnum As Long)
>'== Code to process individual records of a table
>End Sub
>
>As far as it goes this works fine. I can simply place code to focus on one
>record at a time in one of the GenericCodeStub? routines and call Doit with
>the appropriate number. Nevertheless, this is not a very elegant (or totally
>practical) solution.
>
>I would really like to do away with that case statement and just pass a
>reference to a subroutine (or function) instead of the integer CodeStub. Then
>within DOIT I can call the subroutine (which will be defined with the same
>parameters as the GenericCodeStub routines).
Check the Run method in VBA Help. I think it might be at
least part of what you are looking for here.
While you're in Help check the Eval funtion. I don't think
Eval will be appropriate for this problem, but you should be
aware of its capabilities.
--
Marsh
MVP [MS Access]
|