Variables in User Defined Functions

[BamaColtsFan]

Once again, I can't quite figure out how to get what I need...

I am working with a user defined function that accepts 3 values. I am using TransferText to load files into specific tables in my database. If I code each one individually, my code works great (thanks to the help I get from you fine folks). The problem is that I have about 24 files to load. What I want to do is have just one function that takes in the file name, import specification, and table name and loads the file. I would then call that function in a macro (tied to a command button) that would load the specified file (or files).

What I am getting is an error that says "The expression you entered has a function name that Microsoft Access can't find."

So, back to my usual position... What bone-head mistake did I make this time?

Public Function AutoLoadFile(LoadFileName As String, LoadFileSpec As String, LoadFileTable As String)
Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim strPath As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
strPath = newPath!path & LoadFileName
DoCmd.TransferText acImportDelim, LoadFileSpec, LoadFileTable, strPath

End Function

 

[DCrake]

Have you remembered to insert a "\" between the path and the file name?

David

 

[BamaColtsFan]

It's in the newPath!path (stored value in a table)... The code works without the "\" if I hard-code the file name and such. Would it still be required here?

 

[DCrake]

DoCmd.TransferText acImportDelim, LoadFileSpec, LoadFileTable, strPath

If you do a debug.print on the parameters LoadFileSpec, LoadFileTable & strPath what do they return.

David

 

[BamaColtsFan]

David - I'm afraid I must further display my ignorance... I don't know how to do a "debug.print" or where to find the results if I did.... Can you please point me toward a primer on using that function?

 

[joh024]

David - I'm afraid I must further display my ignorance... I don't know how to do a "debug.print" or where to find the results if I did.... Can you please point me toward a primer on using that function?

You should try to use breakpoints then. Click over on the left side of the code where the grey bar is and the line should turn red. Now when you run the code it will stop at that line. Once it is stopped just hover your mouse over the different variables to see what values they hold.

 

[BamaColtsFan]

Once again, I can't quite figure out how to get what I need...

I am working with a user defined function that accepts 3 values. I am using TransferText to load files into specific tables in my database. If I code each one individually, my code works great (thanks to the help I get from you fine folks). The problem is that I have about 24 files to load. What I want to do is have just one function that takes in the file name, import specification, and table name and loads the file. I would then call that function in a macro (tied to a command button) that would load the specified file (or files).

What I am getting is an error that says "The expression you entered has a function name that Microsoft Access can't find."

So, back to my usual position... What bone-head mistake did I make this time?

Public Function AutoLoadFile(LoadFileName As String, LoadFileSpec As String, LoadFileTable As String)
Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim strPath As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
strPath = newPath!path & LoadFileName
DoCmd.TransferText acImportDelim, LoadFileSpec, LoadFileTable, strPath

End Function

Okay, this is just one of those really bone-headded mistakes....

I Googled the original error received and came up with this article (http://support.microsoft.com/kb/312851)... What it says in a nutshell is that you can't have a function that has the same name as any other function or module in the same application. In my case, both my function and module were named AutoLoadFile. As soon as I changed the name of the module, it worked just fine!

Thanks again gang for trying to get me in the right direction! This is one I won't soon forget!!!