Pass variable to append query

Taras
Taras used Ask the Experts™
on
I  have append query that I want to append records from one table to another plus one extra filed that I called “FileName” which I get from a variable.

I created Function in module

Public Function GetCSVFileName(strFileName)
    Dim strPassValue As String
   
    strPassValue = strFileName
    GetCSVFileName = strPassValue
End Function


On my form  in general section I defined variable as
Public strFileName as string

Then in sub procedure I assigned

 value to variable and passed that variable to function

                 strFileName = "AAA"                

                 GetCSVFileName strFileName
               
Then I run query


                DoCmd.OpenQuery "QryAppAppendToTblArchivedMaster"
                DoCmd.Close acQuery, "QryAppAppendToTblArchivedMaster"


Or in SQL view:

INSERT INTO tblArchivedMaster ( SECTION, ROUTE, CSVFileName )
SELECT tblMaster.SECTION, tblMaster.ROUTE, GetCSVFileName([strFileName]) AS Expr1
FROM tblMaster;
I can follow execution function goes trough, however when it run query I stopped and

I am prompted all time for strFileName as parameter for query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this -


Define your variable in the same module as your function (instead of defining it in your form), and change your function as follows:


Global strFileName as string

Public Function GetCSVFileName()
    GetCSVFileName = strFileName
End Function

Open in new window



And change your SQL to this:

INSERT INTO tblArchivedMaster ( SECTION, ROUTE, CSVFileName )
SELECT tblMaster.SECTION, tblMaster.ROUTE, GetCSVFileName() AS Expr1
FROM tblMaster;

Open in new window



Your code for setting the variable in your sub is fine:

                 strFileName = "AAA"      

Open in new window



But the following line needs to be removed:          

              '   GetCSVFileName strFileName  '<----- Remove this line of code

Author

Commented:
Thanks a lot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial