How do I get "Transfer Text" to recognize my query name

[wmdavidwynn]

This question refers to an Access 2003 project.

I'm trying to create a fixed length text file as a result of a query using the 'Transfer Text' method. I have a query that prompts for two input parameters. These will select the records that I want to output. As it displays the correct records on the screen, I want these same records to be output as a fixed length text file.

Using 'Transfer Text' in VBA, when I put in the table name, the query displays the selected records but the whole table is output to a fixed length file. If I specify the stored procedure name, the query displays the correct records and then I get a message that says that VBA can't find the stored procedure. If I remove the table name, I get a message that says that "The action or method requires a Table Name argument". I haven't figured out how to specify that just the output of the query and not the whole file should be output as text.

Here's my code....
Private Sub FileOut_Click()
On Error GoTo Err_FileOut_Click
Dim stDocName As String
stDocName = "File Submittal"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit
DoCmd.TransferText acExportFixed, , "MBS Input", "C:\Documents and settings\dwynn\desktop\m1234567-999.txt"
Exit_FileOut_Click:
Exit Sub
Err_FileOut_Click:
MsgBox Err.Description
Resume Exit_FileOut_Click

End Sub
----------------------------------
Any help will be greatly appreciated. Thanks.

 

[ScottGem]

Instead of having the query prompt you for parameters, enter the parameters on a form and set the criterias in the query to:

=Forms!formname!controlname

This will pull the filtering values from the named controls on the form before the TransferText processes it.

 

[wmdavidwynn]

Still having difficulties

Scott, thanks for your reply. I changed the criteria in the query to '=Forms!Text_File_Out!invoicedate' and '=Forms!Text_File_Out!contractnumber' which are my selection columns. I have a Create Output File control that calls the query after I accept the input values in the form. I put the 'TransferText' command in the control's module and I still get the message that it cannot find the query. My lack of experience with VBA is probably causing my lack of understanding. Please take a look at my query design to see if I've done something wrong. I am finding that Access 2003 is not consistent at all. Some things work differently in each form or query even if I input it the same way each time.

Thanks in advance...

 

[ScottGem]

What's the EXACT message you get?

 

[wmdavidwynn]

The exact messages

If I associate the name of the stored procedure in the 'Transfer Text' method, the message is "Microsoft Access can't find the object 'File Submittal'. If I put in the name of the table, the query doesn't select the required and the 'Transfer Text' method creates an output file that includes all of the records in the table. If I don't put in either the table name or the query name, I get the message "The action or method requires a Table Name argument".

Is there something wrong with my VBA code as it relates to the 'DoCmd' that I sent you?

 

[ScottGem]

I'm sorry, I missed that you were using a project (ADP) rather then a MDB. I haven't done much work with those and I'm not sure of the differences and how VBA works with them. Hopefully someone else here can pick up the slack.

 

[kevcri]

I'm having the same problem

I am getting the same error, when trying to import from a fixed length text file. it says "Microsoft Access can't find the object 'after.txt'."
someone help us

 

[ScottGem]

I am getting the same error, when trying to import from a fixed length text file. it says "Microsoft Access can't find the object 'after.txt'."
someone help us

First, its not a good idea to piggy back on someone elses thread unless you are dealing with exactly the same problem. its better to start a new thread.

Second, we would really need to see your code and have you confirmed that the file exists in the location you list?

I would suggest starting a new thread with more details so we can help.

 

[wmdavidwynn]

KevCri response

Here's what I found out. The TransferText method does not let you use STORED PROCEDUREs or QUERIES as the stDocName.... it accepts only tables. If you working in an Access project (.adp), as am I, you're stuck.

Here's what I did. I split the actions into 2 parts. I created an SP as a "Make-Table Query" to output just the records I selected into a temp table. Then I used TransferText to output a '.txt' file to my disk folder. A little kludgy but it works.