locked
How do run a sub module using a macro? RRS feed

  • Question

  • Basically I need to run a module i made that deletes a record out of a certain data table. But i need this to happen when i press a button on a form pretty much. So i figure i should just make a macro that will run this module. This is what the module looks like :

    Option Compare Database


    Public Function DeleteRecords() As Object

    Dim db As DAO.Database

    Dim rcset As DAO.Recordset

    Dim str As String

    Set db = CurrentDb

    Set rcset = db.OpenRecordset("TEST TABLE")

    rcset.MoveFirst

    rcset.Delete

    rcset.Close

    db.Close

    End Function

    And whenever i try the RunCode command and put DeleteRecords as the function name, an error pops up everytime I try running the command that says "Microsoft access cannot find the name 'DeleteRecords' you entered in the expression'

    • Edited by R_West Monday, July 9, 2012 6:19 PM
    Monday, July 9, 2012 6:10 PM

Answers

  • You need to add the parentheses, even if there are no arguments:

    DeleteRecords()

    That being said, you'd be far better off simply running a Delete query. Failing that, try

    Option Compare Database
    Option Explicit
    Public Function DeleteRecords() As Object
      CurrentDb.Execute "DELETE FROM [Test Table]", dbFailOnError
    End Function
    BTW, the fact that your sample code did not include the Option Explicit statement indicates that you have not told Access to require declaration of all variables. Doing that can save you literally hours of debugging when you make a little typo! Go into the VB Editor and select Options from the Tools menu. Check the Require Variable Declaration box on the Editor tab. All future modules will automatically include the Option Explicit statement. You'll need to go and add it to all existing ones.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by R_West Monday, July 9, 2012 7:10 PM
    Monday, July 9, 2012 6:54 PM

All replies

  • I have read the name of the module and the name of the function within the module needs to be different.  Are they different in your case?

    If this reply answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Monday, July 9, 2012 6:24 PM
  • Could it be that your "DeleteRecords" is not just the name of the function, but also the name of the module in which you've stored it?  If so, change the module name to something else;  "modDeleteRecords", perhaps.  The module name must not be the same as the name of any procedure it contains.

    BTW, there doesn't seem to be any reason for the function to return an Object reference, so I would not declare it "As Object".  If you don't intend to return anything meaningful, just leave the "As ..." part off the declaration.  it will default to "As Variant", but people reading your code won't be confused.

    I assume, though, that at some point your code in the function will be more elaborate than what you've posted.  Otherwise it seems like odd code.  Also, BTW, as written it will raise an error on this line:

        rcset.MoveFirst

    if the recordset is empty.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, July 9, 2012 6:29 PM
  • The module name is just the default Module1. And i just basically want to clear a table once im done using it, so i want to make a macro that runs this module multiple times basically. The only thing is the RunCode command cannot seem to find the function name.


    And the weird thing is if i view the module itself and press run, it does exactly what i want it to do, only problem is i cant get a macro command to actually work.
    • Edited by R_West Monday, July 9, 2012 6:37 PM
    Monday, July 9, 2012 6:35 PM
  • You need to add the parentheses, even if there are no arguments:

    DeleteRecords()

    That being said, you'd be far better off simply running a Delete query. Failing that, try

    Option Compare Database
    Option Explicit
    Public Function DeleteRecords() As Object
      CurrentDb.Execute "DELETE FROM [Test Table]", dbFailOnError
    End Function
    BTW, the fact that your sample code did not include the Option Explicit statement indicates that you have not told Access to require declaration of all variables. Doing that can save you literally hours of debugging when you make a little typo! Go into the VB Editor and select Options from the Tools menu. Check the Require Variable Declaration box on the Editor tab. All future modules will automatically include the Option Explicit statement. You'll need to go and add it to all existing ones.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by R_West Monday, July 9, 2012 7:10 PM
    Monday, July 9, 2012 6:54 PM
  • The module name is just the default Module1. And i just basically want to clear a table once im done using it, so i want to make a macro that runs this module multiple times basically. The only thing is the RunCode command cannot seem to find the function name.


    And the weird thing is if i view the module itself and press run, it does exactly what i want it to do, only problem is i cant get a macro command to actually work.

    I'll bet you didn't include the parentheses when you specified the function name for the RunCode action.  The parentheses are required.  So not this:

        RunCode
            DeleteRecords

    But instead this:

        RunCode
            DeleteRecords()

    By the way, if your intention is to delete *all* records in the table (which admittedly may only contain one record), then your code could be simplified to this:

    Public Function DeleteRecords()
    
        CurrentDb.Execute "DELETE FROM [TEST TABLE]", dbFailOnError
    
    End Function


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Monday, July 9, 2012 7:00 PM
  • Always those darn parenthesis! Thanks for everyones help.

    Monday, July 9, 2012 7:10 PM