How do I execute my VBA code in my Macro?

[vurna]

Hi!

I've have a bit of VBA code which I want to execute in a Macro.
My macro is linked to a UI button and serveral other things are run in the same macro.

In the middle of the macro I need to execute a bit of VBA code. I've figured out that I should use macro action "RunCode" but I do not know how I should write the syntax. Every way I try results in an error.

My knowledge on how to write syntax in very limited so I was hoping to get some help here.

The code I'm attempting to execute is in a "module" and it looks like this:
[VBA]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 w1ill 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[/VBA]


As you can see there are two pieces of code. The point of all the code is to rename the headers of one of my tables in my access data base.
The error message I get is:
"The expression you entered has a function containing the wrong number of arguments"


My version of MS Access is: MS Access 2003

Thanks in advance for any help!

Regards, Erik

[Gollem]

Hi,

a function returns a value. I don't see anything returning from "changefieldnames". If it doesn't return anything you should make it a sub.

 
Sub changefieldsnames(...

[vurna]

Hi,

Thanks for your reply Gollem. I changed it from a function to a sub. But then when I try to run it as a "RunCode" in my macro I get a different message, this time stating that there is no object in the database with the name I have specified.

The only option there is in "RunCode" is "Function Name". So maybe my code still has to be a function for it to be executed in a macro?

Back to square one..

[OBP]

You also have to pass oldname and newname to the Function as Text Strings.
I never use Macros, but if you could explain what the macro does we could probably provide VBA code to do it and run the Function.
Or you could Convert the Macro to VBA by using the File>Save As>Module converter.

[Imdabaum]

This is something that I just spent quite a bit of time researching. I was able to get my function working.

What version of Access are you in?
Do you use the BuildWizard?

I had my function stored in a Module. Keep in mind, if it's private, you won't be able to access it from the Macro, at least that's what I've experienced.

If you use the build wizard it gives you folders, Built-In Functions and then a folder with the database name. Look in that one after making the function public and then browse to the module or form where the function resides.

[vurna]

I use MS Access 2003.
My function is stored in a Module and from a macro I try to call there function. I can see the function from the Build Wizard, so it's not a private one.

My Macro does a couple of things. Action by Action it does the following:

• OpenQuery: 1_1_1_Purge imp_SMT
• OpenQuery: 1_1_2_Purge trans2_SMT
• OpenQuery: 1_1_3_Purge trans3_SMT
• TransferText: Import Delimited, SMT import specification, table: "imp_SMT", filenamepath, Has field names: "no".
• OpenQuery: 1_2_1_Create trans1_SMT
• OpenQuery: 2_1_3_Purge conv_FieldNames
• OpenQuery: 2_1_4_Append field names to conv_FieldNames1 (UNION append query)
• OpenQuery: 2_1_4_Append field names to conv_FieldNames2
  (UNION append query, had to use two queries as there was a limit to how much I could put in one UNION query)
• RunCode: This is where I fail to execute the my function. The purpose of the function being to rename the fields in my table "trans1_SMT" to what is specified in my table "conv_FieldNames".
  But it says I need to give it arguments to work, which I do not fully understand what I should give it.
• OpenQuery: 2_2_1_Append trans1 to trans2
• OpenQuery: 2_2_2_Append trans2 to trans3
  (doing some washing of the data between queries)
• OpenQuery: 3_1_1_Append trans to prod
  Last my data is appended to a main table which contains all historichal data and is updated monthly with a new load of data.

Basically my problem to begin with was that the import file I have to work with has 52 fields, these 52 fields can change order every time. And the person who will be loading the database with new data once a month has no knowledge of MS Access at all. Getting this person to manually name the fields every time is a big hassle when all they should be required to do is use the UI forms I have built and more or less just press a button and vola the database is updated with new data and our Excel pivot / queries can be refreshed showing our data washed and structured.

I have used the Build Function wizard and I can see my function called "changefieldnames" but I cannot execute it. I think its because I need to use arguments. The wizard gives me this to play with when chosing my function: "changefieldnames («oldname»; «newname») "
I've tried a few things like writing the name of my table & field that contain the headers i want to rename to and from but then it just says the object doesn't exist that I have to write the name of a form or a report.

When converting my macro to VB (with just the RunCode command in it) I get the following VB code:
[vba]'------------------------------------------------------------
' Macro2
'
'------------------------------------------------------------
Function Macro2()
On Error GoTo Macro2_Err
Call changefieldnames («oldname»; «newname»)

Macro2_Exit:
Exit Function
Macro2_Err:
MsgBox Error$
Resume Macro2_Exit
End Function[/vba]

My problem seems to be what to write as an argument, and I really don't know what to give as arguments to the function.

[OBP]

Can we take one step back here?
You said "Basically my problem to begin with was that the import file I have to work with has 52 fields, these 52 fields can change order every time"
Why does it matter what order the Fields are in?
Do the Field names change, or don't you have field names for the Import File?
What is the Structure of the Import File, does it have "Headings" or "Field Names"?
Normally I import the data to a Temporary Table and then Append the data in to the correct Table using a Query. But I can normally find names for the Fields in the Temporary Table.

[OBP]

I think I can get your Module to work by removing the arguements that it needs to be passed to it. In actual fact you are trying to use it incorrectly as it is designed to Rename 1 Field and you want to rename 52 of them, although how you know what order the Imported ones are compared to the names in your table I am not quite sure.
I can certainly loop through the names in the Table but how can I match them up to the names in the Imported Table?

[vurna]

Can we take one step back here?
Why does it matter what order the Fields are in?
Do the Field names change, or don't you have field names for the Import File?
What is the Structure of the Import File, does it have "Headings" or "Field Names"?
Normally I import the data to a Temporary Table and then Append the data in to the correct Table using a Query. But I can normally find names for the Fields in the Temporary Table.

Good morning! Or Lunch here in Sweden.

Normally I also import raw files into a temp table before moving the data along.
This time it is a little trickier for me though. To specify what the import file looks like:
The file does have a first row containing Headers.
The headers/fields change order every time. (but the names are the same, just different order)

I have tried the usual way I normally do it; Creating a import specification and then using it to automate the import process thru a macro which first drops the old import table then creates a new one while importing the new raw file.
The problem is though when importing automatically the data ended up in the wrong colums/fields. For example the Contents of the field "Status" ended up in "Description". Basically the import always took the fields exactly how the were specified when I created the import specification.

This is why I attempted to find a different way of doing it;
1. Always import the file with headers "Field1", "Field2" etc.
2. Then read the text of the first row (headers).
3. Concenate these headers into a table with two fields, oldfieldname and newfieldname.
4. Use a bit of VB code that reads the table which is specifying the old and new header names and do the renaming of the imported table.

Maybe i'm going at it wrong. But one thing is clear the setup I've come up with works. If I just could get the function to run from a macro or a button on a form.

I think I can get your Module to work by removing the arguements that it needs to be passed to it. In actual fact you are trying to use it incorrectly as it is designed to Rename 1 Field and you want to rename 52 of them, although how you know what order the Imported ones are compared to the names in your table I am not quite sure.
I can certainly loop through the names in the Table but how can I match them up to the names in the Imported Table?

I tried just removing the argument statement from the function as so:
But when I run it then in the VB Editor, nothing happens at all.
[vba]Function changefieldnames()
'----- 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[/vba]

In actual fact you are trying to use it incorrectly as it is designed to Rename 1 Field and you want to rename 52 of them, although how you know what order the Imported ones are compared to the names in your table I am not quite sure.
I can certainly loop through the names in the Table but how can I match them up to the names in the Imported Table?

I do know the order of the of the field names because of the way I created my Union query, It looks like this;
SELECT "Field1" AS oldfieldname , Field1 AS newfieldname from 2_1_1_read_header_row_from_import
UNION SELECT "Field2", Field2 from 2_1_1_read_header_row_from_import
UNION SELECT "Field3", Field3 from 2_1_1_read_header_row_from_import
-- and it goes on for 52 fields.

This is what I append to the table with oldfieldname and newfieldname which looks like this in a dataview after its been filled:
oldfieldname | newfieldname
Field1 | SR #
Field10 | Status
Field11 | Created
Field12 | Created By
Field13 | Follow-Up Time

[OBP]

Can you supply me with a copy of the text file to have a play around with?
I may be able to "Read" the file using VBA and append the data directly in to your Table without actually importing anything.
I would also like to see how the file imports without a Spec.
I will private mail you my email address.

PS. that is odd you do not appear to have Private Mailing on the Forum.

[vurna]

Can you supply me with a copy of the text file to have a play around with?
I may be able to "Read" the file using VBA and append the data directly in to your Table without actually importing anything.
I would also like to see how the file imports without a Spec.
I will private mail you my email address.

PS. that is odd you do not appear to have Private Mailing on the Forum.

That would be really helpfull of you. I think there was an issue in my profile setting, you should be able to send me your e-mail now.

[OBP]

The Private Mail option is not showing up, can you send me an email at

osborn dot ac at googlemail dot com
I am sure that you can work out the correct email address.

[vurna]

The Private Mail option is not showing up, can you send me an email at

osborn dot ac at googlemail dot com
I am sure that you can work out the correct email address.

I think I figured out your e-mail. e-mail sent