passing parameter to query

[stockboy]

Hello,

I hope someone can help me out on this problem.

I need to use the TransferText command to transfer data from the access db to a text file. I got that to work but now the user wants to limit the records that get put into the text file. I have created a query with a parameter but I can't figure out how to pass that parameter to the query in the DoCmd.TransferText command.

Here is what I got so far:
DoCmd.TransferText acExportDelim, "", "qryExportHeader", "c:\invoiceheader.txt", True

Any ideas?

 

[LittleSmudge]

I assume "qryExportHeader" is a predefined Query stored in the Access collection of queries.

I'd suggest you copy the SQL version of that and then Dim a string variable in the code to be euqal to that SQL code.
Then modify it.

Then have a text box on the form that takes the user input number of records to send. Call it txtNumber

So :

Dim intNumber As Int
Dim strSQLExportHeader As String

If Not IsNumeric(txtNumber) Then
' Error msg to user for inputting wrong data to control on form
Else
intNumber = txtNumber
End If

strSQLExportHeader = "SELECT Top " & intNumber & " FROM tablename WHERE {where clause in here from SQL code in original query }"

DoCmd.TransferText acExportDelim, "", strSQLExportHeader, "c:\invoiceheader.txt", True

NOTE strSQLExportHeader Not in quotes becuase it is a 'string' variable that you want evaluated.
On a similar Note don't forget to bring variable used in the WHERE clause outside of the quotes as well.


'ope-that-'elps

G LS

 

[stockboy]

I thought about that but I read somewhere that TransferText will not take an SQL statement as the source.

I tried your example but it throws an error saying "MS Jet database engine could not find the object 'SELECT * FROM invoice. Make sure the object exists and that you spell its name and path name correctly."

Can you think of another way?

Thanks again for the idea!

 

[LittleSmudge]

Well you can MAKE a query on the fly in code using QueryDef Making it to the specification discussed above.)
Add it to the database Query collection and THEN call the TransferText and refer to this new stored Query.

G LS

 

[CosmoKramer]

Stockboy,

I don't know if this will make a difference, but it has helped me in other DoCmd object statements. Try taking out your quotes from the specification argument, but leave the comma:

DoCmd.TransferText acExportDelim, , "qryExportHeader", "c:\invoiceheader.txt", True

 

[stockboy]

Cool. Can I then delete the Query after I use it?

 

[stockboy]

Cosmo--

The problem is passing a parameter or critia item to the query. Any ideas?

 

[CosmoKramer]

Are you running this TransferText command through a command button on a form?? If so, couldn't you add a text box, list box, etc. that the user could enter their criteria, and have the query reference the form control??

 

[Shortie]

It might help if you could post the SQL statement. Then I'll see what I can do for you.

 

[LittleSmudge]

As an easier alternative to my previous idea:-

Put a user input parameter ( or a reference to a user updated control on the form ) into the stored query itself.

Then when the TransferText looks for the query it will pick up the correct data.


G LS

 

[FMAQ]

THERE ARE SUGESTIONS TO NOT USE FRECUENTLY SENDKEYS, BUT I HAVE USED IT IN SOME SITUATION LIKE YOURS, REQUEST THE DATA YOU NEED TO PASS AS PARAMETERS TO YOUR QURY IN A FORM INCLUDING IN IT THE TEXTBOXES OR CONTROLS YOU NEED THEN USE SENDKEYS TO SEND THE VALUES, NUMBERS AND DATES MUST BE FORMATTED CONVENIENTLY, APPEND AN CHR(13) BEFORE EACH VALUE YOU SEND, THEN EXECUTE TRANSFER TEXT, LOOK FOR NEEDED INFORMATION ABOUT SENDKEYS IN HELP. OK......