How does a macro receive a function argument? (1 Viewer)

[arage]

How does a macro receive a function argument?
I’ve done a search on my problem but can’t find a relevant post to help me out but what I’m interested in is writing a macro that will execute a function that takes a string parameter. Now, how do I specify this argument in the macro if I’m using the RunCode() action for example.

 

[jessa_lee]

Has anyone found an answer to this? I am looking for the same answer and have eben unable to find it anywhere.

I've built my macro and my module (with respectively named function within), but when I try to run the macro, it says Microsoft Access is unable to find the named function. What am I missing??

 

[Mile-O]

Have you declared your function as Public and not Private?

 

[jessa_lee]

I'm not sure - how do I check that? It's not "Hidden" if that's what you mean?

 

[Mile-O]

I've built my macro and my module (with respectively named function within), but when I try to run the macro, it says Microsoft Access is unable to find the named function. What am I missing??

In your module:

Public Function MyFunc

These won't work:

Function MyFunc

Private Function MyFunc

Public Sub MyFunc

Private Sub MyFunc

Sub MyFunc

 

[jessa_lee]

Hrmm... I changed (and saved) the function (module)... still getting the same error... See below:


Public Function TableExists(ByVal TableName As String) As Boolean
On Error GoTo DO_FALSE
TableExists = True
DoCmd.OpenTable TableName
DoCmd.Close
Exit Function

DO_FALSE
TableExists = False
Err.Clear
End Function

My macro calls for "TableExists".

 

[jessa_lee]

The exact error I'm getting is "The expression you entered has a function name the Microsoft Access can't find" when I click on the macro.

 

[Mile-O]

Question: Why are you using a macro if you can code?

 

[Mile-O]

The macro won't do anything for you anyway as you are asking it to RunCode but can't do anything with the returned value.

 

[jessa_lee]

Hrmm... good question. I guess because I'm reading through a book that's supposed to be teaching me the benefit of using macros... maybe I'll just skip these chapters and get back to the coding...

It's rather sad - I've already found about 5 total errors in this "tutorial" book... I've had the right answer and it's been entirely wrong! Ah well, the price you pay in attempt to learn...

I'm just in the process of learning to code in VB. What are your thoughts on the use of macros verses modules? Any input you can give to the "new kid"?

Thanks for all your help!
Jessa

 

[jessa_lee]

The macro was built to runSQL if the result returned was False. But if it doesn't see the module, it can't get a result and determine what it is to do... *sigh* I have so much to learn.

 

[Mile-O]

If you can code, ditch macros.

There's this thread a few below this one that asks Why macros? although I haven't said much.

Basically, you can't trap an error with a macro, they are clunkier, and are for simple operations as you can't get the benefit of a full IF...THEN...ELSE or SELECT..CASE structure not to mention getting in about all the other objects you can't manipulate.


P.S. Here's a better function for you...if you use DAO

Public Function TableExists(ByVal strTable As String) As Boolean

    On Error GoTo Err_TableExists

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        If tdf.Name = strTable Then
            TableExists = True
            Exit Function
        End If
    Next
    
Exit_TableExists:
    Set tdf = Nothing
    Set db = Nothing
    Exit Function

Err_TableExists:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_TableExists

End Function

I'm guessing you are running your macro on the click of a button, so: say that the button is called MyButton:::

Private Sub MyButton_Click()
    If TableExists("MyTable") = True Then
        MsgBox "Yes, that table exists.", vbInformation
    Else
        MsgBox "No, that is not a table.", vbInformation
    End If
End Sub

 

[jessa_lee]

Quality. Very nice

Thank you for your help.

 

[Mile-O]

No problem...just ask!

P.S. Is the book, perchance, Beginning Access 2000 Programming by Wrox Press? I've hear that is full of mistakes.

 

[jessa_lee]

Actually, no, it's "Sams Teach Yourself Microsoft Access 2000 Programming in 24 Hours", but thanks - now I know to stay away from that "Wrox" one too!! hehe

Thanks again
Jessa