I am using DoCmd.TransferText() to export query results to a text file.I am using an Export Specification and everything is working fine with the export. The existing routine uses a simple parameter-less query to get ALL records.

Now I need to allow the user to specify the scope of the query. They have the option of exporting ALL items or by Division. I built a new query that takes a Division parameter and have tested the query and it works as expected. I added logic in my VBA code to specify the Query that is passed to TransferText in the QueryName parameter.

I am having difficulty figuring out how to pass the parameter to the query because the query gets executed by the TransferText call.

Here is the code that I have:

Dim sExportPath As String
Dim sExportFileName As String
Dim sExportFileWithPath As String
Dim sExportSpecification As String
Dim sQueryName As String

Dim intExportItemsCount As Integer

' NEW - 26-NOV-2007
' Determine which query to use
If (sExportScope = "All") Then

   sTableName = "QueryExportInventoryItems"

Else

   sTableName = "QueryGetInventoryItemsByDivisionParam"

End If

sExportPath = "C:\Program Files\PTS\Data\"
sExportFileName = "Export_data.txt"
sExportFileWithPath = sExportPath + "\" + sExportFileName

sExportSpecification = "Inventory Export Specification"

DoCmd.TransferText acExportDelim, sExportSpecification, sQueryName, sExportFileWithPath

Right now, Access pops up an "Enter Prameter Value" dialog when the Export runs because the query needs the parameter. If I type in the parameter value, the Export works great BUT, this is NOT acceptable to the user. I have a comboBox that allows the user to select the Division parameter prior to executing the code shown above. I know how to get the value from the combobox (strExportScope = ComboExportDivision.Value) BUT I just can't figure how to pass it along to the Query.

Does anyone have any ideas on how to pass a query parameter to the Query being called by TransferText?

Thanks

I guess there is a way to accomplish this using a global variable and a function in the VBA code. The function just returns the global variable. The function is then referenced inside a query to get the parameter (global variable).  The global variable needs to get assigned somewhere in the code. I investigated this a bit, but didn't like the idea of using globals.

so................

I ended up trying something completely different that seems to be working fine and doesn't require any use of Global variables and/or functions.

I am posting this in case anyone else runs into this type of thing.


I changed my Parameter query to this:

SELECT [InventoryControlNumber], [ItemDesc], [ItemTypeDesc], [DivisionName], [LocationName], [StatusDesc]
FROM InventoryItems
WHERE [InventoryItems].[DivisionName]=[Forms]![Inventory]![ComboExportDivision];

So, inside the Query, I am pulling the paramter value directly from the from's combobox control (ComboExportDivision).