Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 21st, 2006, 11:01 AM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default Function/Subroutine Pointer Parameter

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.

Does anybody have some insight into this or is this just not possible in VBA?

-Phil-
__________________
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-Phil-
Reply With Quote
  #2 (permalink)  
Old October 2nd, 2006, 01:16 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have discovered the Eval function! This enables me to solve my problem with the following code:

Public rsGeneric As Recordset
Public nGeneric As Long

Public Sub DoIt(TableName As String, CodeStub As String)
' Assumes that the function CodeStub is defined as follows:
'
' Public Function <CodeStub name> () as Boolean
' ...
' <CodeStub name> = True
' End Function
'
' and that it uses the 2 global variables:
' rsGeneric - recordset pointer for the table
' nGeneric - row number

Dim db As Database

Dim nRecords As Long

    If Not TableExists(TableName) Then Exit Sub
    If (CodeStub & "") = "" Then Exit Sub

    Set db = CurrentDb()
    Set rsGeneric = db.OpenRecordset(TableName, dbOpenDynaset)

    rsGeneric.MoveLast
    nRecords = rsGeneric.RecordCount
    rsGeneric.MoveFirst

    For nGeneric = 1 To nRecords
        rsGeneric.Edit
        Eval (CodeStub & "()") ' The return value of the function is not used

        rsGeneric.Update
        rsGeneric.MoveNext
    Next nGeneric

    rsGeneric.Close
    db.Close

End Sub

I'm not 100% happy with having to use global variables, but the eval function doesn't seem to completely handle parameters. Note that I haven't included the definition of the TabeleExists function, but that was very simple and is left as an exercise.


-Phil-
Reply With Quote
  #3 (permalink)  
Old October 2nd, 2006, 10:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old October 3rd, 2006, 09:07 AM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Even better!

Eventually I intend to place DOIT in a library db (along with some other useful code that I've developed) so that I can stop all the copy/paste when I create a new db.


-Phil-
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Like function with wild card and parameter prompt d-fxt Access 1 April 4th, 2007 01:20 AM
Passing function parameter to query kar22 Access 1 February 27th, 2007 08:28 AM
passing parameter to a perl subroutine crmpicco Perl 2 February 16th, 2007 08:07 AM
Dtabase function in command parameter object? jdang67 ADO.NET 0 October 7th, 2005 10:07 AM
pointer-to-function in C++ jacob C++ Programming 2 October 23rd, 2004 05:20 AM



All times are GMT -4. The time now is 08:16 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.