How do I execute my function in a macro?

[vurna]

Hi everyone!
I've got a function which I've found on the web wich does exactly what I need it to do.
When I run it manually from the Visual Basic Editor (Alt+F11) in MS Access it works perfectly. However I need to include this function in a macro I'm creating.
I've connected a Macro to a button on a form. I am doing serveral things in this macro like importing a spreadsheet, appending a few table's and so on.
In the middle of all this a want my function to be executed but I cannot seem to get it executed.
It seems I might not be able to call a function from a macro but even when I go into the VB code of the button and try to execute it there is doesn't work.

My function looks like this and it's actually two functions or one is connected to the other or how I should put it.

Option Compare Database
Function changefieldnames(oldname As String, newname As String)
'----- oldname and newname are passed to this function from
'----- wherever you are in your process. I would do it via a
'----- form, but if the field names you change are the same every
'----- time the process is run, why not store them in a table. The
'----- code you will need to pass the field names to be corrected
'----- from the table is given in Sub readinfieldnames() below.
Dim db As Database
Dim tdf As TableDef
Dim n As Object
Set db = CurrentDb
Set tdf = db.TableDefs("trans1_SMT")
For Each n In tdf.Fields
    If n.Name = oldname Then n.Name = newname
Next n
Set tdf = Nothing
Set db = Nothing
End Function
Sub readinfieldnames()
Dim rst_data As Recordset
'------ Assumes you have a table with 2 columns. Column 1 contains
'------- downloaded field names, column 2 has the names you want
Dim oldfieldname As String, newfieldname As String
Set rst_data = CurrentDb.OpenRecordset("conv_FieldNames")
With rst_data
    .MoveFirst
    Do Until .EOF
        oldfieldname = .Fields(0).Value
        newfieldname = .Fields(1).Value
        changefieldnames oldfieldname, newfieldname
        .MoveNext
    Loop
 End With
 Set rst_data = Nothing
End Sub

Any Idea's on what I should write to execute this? I've done a little trial and error but cant seem to run it unless I'm actually in Alt+F11 and doing it manually (and it works just as intended when executing it manually).

 

[ghudson]

Normally you would need to name an Action as "RunCode" and set the Argument or Function Name to the name of your function in your macro but you have to pass arguments in your custom function and I do not know if macros can do that. I suggest that you use VBA all the way with this step and you should not have any problems. You can convert you macro to code to save you some steps.

To call you custom function from another VBA event, it would look something like this...

Call ChangeFieldNames("OldFieldName", "NewFieldName")

 

[vurna]

Thanks for your reply GHudson.

I tried what you suggested and just as a start to see if I could get it to work without any other code interfering I put it by it self in a button on one of my forms, the code looked like this:

Private Sub Command33_Click()
Call changefieldnames("OldFieldName", "NewFieldName")
End Sub

However when I press the button nothing happens at all. No reaction from Access.

On the other hand if I go to the VB Editor and place my cursor in the code and run it then it works.
I do however get a poopup window that looks like this: see attachment
I press the Run button there then it executed correctly.

Any Idea's? Maybe that popup interferes and cannot popup when it's connected to a button.

 

[ghudson]

Is your button really tied to the event that you are trying to run? Place a simple message box in the onclick event of your button to see if the button is working.

The error you posted happens when you try to run code directly from the vba editor for a form module. You can only directly run code from a public module.

 

[vurna]

I double checked and added a little Msg box to the code for the button.
When I press it the my MsgBox text instantly pops up, but the function I'm calling is never run.

I added Public in front of the function as you suggested as well but it made no difference.


There's probably something else I'm doing wrong so I'll just to be clear and explain exactly how it looks on my end now.

First off I have a very simple Form and on that form I have the button which should execute the following code when I click on it:

Private Sub testbutton2_Click()
'trying to run the function here
Call changefieldnames("oldfieldname", "newfieldname")
MsgBox ("button operation done")
End Sub

I then have the "changefieldnames" function in a module called "Module2"
And the whole code for this function is:

Option Compare Database
Public Function changefieldnames(oldname As String, newname As String)
'----- oldname and newname are passed to this function from
'----- wherever you are in your process. I would do it via a
'----- form, but if the field names you change are the same every
'----- time the process is run, why not store them in a table. The
'----- code you will need to pass the field names to be corrected
'----- from the table is given in Sub readinfieldnames() below.
Dim db As Database
Dim tdf As TableDef
Dim n As Object
Set db = CurrentDb
Set tdf = db.TableDefs("trans1_SMT")
For Each n In tdf.Fields
    If n.Name = oldname Then n.Name = newname
Next n
Set tdf = Nothing
Set db = Nothing
End Function
Sub readinfieldnames()
Dim rst_data As Recordset
'------ Assumes you have a table with 2 columns. Column 1 contains
'------- downloaded field names, column 2 has the names you want
Dim oldfieldname As String, newfieldname As String
Set rst_data = CurrentDb.OpenRecordset("conv_FieldNames")
With rst_data
    .MoveFirst
    Do Until .EOF
        oldfieldname = .Fields(0).Value
        newfieldname = .Fields(1).Value
        changefieldnames oldfieldname, newfieldname
        .MoveNext
    Loop
 End With
 Set rst_data = Nothing
End Sub

As you can see I typed in Public in front of it and it still works on it's own when I run in from the VB Editor.

Edit:
Maybe I can't call a function which is a module from a form? Should I put the main function somewhere else perhaps?

 

[vurna]

Hey again,

I recieved a solution elsewhere just wanted to post it here in case someone else comes looking.

Alas the solution was very simple but it never occurred to me as the biginner at these things I am.

Anyhow to call my function properly from a button I simply use this code:

Private Sub Command0_Click()
Call readinfieldnames
End Sub

I never occured to me to try to execute the function by starting it with the second part.

 

[ghudson]

I thought I covered that in my first repsonse to your post?

Normally you would need to name an Action as "RunCode" and set the Argument or Function Name to the name of your function in your macro but you have to pass arguments in your custom function and I do not know if macros can do that. I suggest that you use VBA all the way with this step and you should not have any problems. You can convert you macro to code to save you some steps.

To call you custom function from another VBA event, it would look something like this...

Call ChangeFieldNames("OldFieldName", "NewFieldName")

 

[vurna]

I thought I covered that in my first repsonse to your post?

Hey GHudson,

Yes and no, the thing is that trying to call "changefieldnames" doesn't work. Apparently for the function to be execute you have to call the second part in the function "readinfieldnames".

I have no Idea why or how it is like this, my limited knowledge about VBA prevents me from understand it. All I know is that it works now and I'm a very happy camper.

Anyhow thanks for trying to help me out with this one GHudson.