Parameters

[min3010]

Please help, im new to this VBA stuff so am stuck.

Im trying to export the data from a query to a textfile using Docmd.TransferText. The difficult part is rather than specify the query name and file name in the code, I wish to do the following:

- Allow the user to enter the query name and file names in textboxes in a Access form and then pass these names as parameters to the code (the idea of the form is to enable the user to enter the query name and destination file and based on what name is entered, the data is transfered to a text file)

-The above is only executed dependant on what is selected from a combobox. For instance, if the user selects a certain item from the combobox, the two textboxes are enabled, the user enters the query name and destination file path, clicks run and the magic happens!

Here is what I have so far:

Private Sub txtRun_DblClick(Cancel As Integer)

If comboActions.ListIndex = 0 Then
DoCmd.RunMacro "1 USAGE - Import and update data"
ElseIf comboActions.ListIndex = 2 Then
DoCmd.RunMacro "3 SUBSCRIBERS - Import and update data"
ElseIf comboActions.ListIndex = 4 Then
DoCmd.RunMacro "5 SERVICE_CHARGE - Import and update data"
ElseIf comboActions.ListIndex = 5 Then
txtQueryName.Enabled = True
TestMethod (strQry, strFile)
End If
End Sub

Public Sub TestMethod(strQry, strFile As String)
strQry = txtQueryName.Text
strFile = txtFileName.Text
DoCmd.TransferText acExportDelim, "SERV_Export_Spec", strQry, strFile, No
End Sub

Its in a bit of a mess so can someone help me sort it out?

Much Appreciated!

 

[Premy]

How about this?

Private Sub txtRun_DblClick(Cancel As Integer)
Dim sQry as string
Dim sFile as string
sQry = Me.txtQueryName
sFile = Me.txtFileName

If comboActions.ListIndex = 0 Then
DoCmd.RunMacro "1 USAGE - Import and update data"
ElseIf comboActions.ListIndex = 2 Then
DoCmd.RunMacro "3 SUBSCRIBERS - Import and update data"
ElseIf comboActions.ListIndex = 4 Then
DoCmd.RunMacro "5 SERVICE_CHARGE - Import and update data"
ElseIf comboActions.ListIndex = 5 Then
txtQueryName.Enabled = True
TestMethod (strQry, strFile)
End If
End Sub

Public Sub TestMethod(strQry As String, strFile As String)
DoCmd.TransferText acExportDelim, "SERV_Export_Spec", strQry, strFile, No
End Sub

I put the red part to remind u that variable datatypes should be declared individualy. In (strQry, strFile As String) only strFile has a string datatype, strqry is a variant.

HTH
Premy

 

[Premy]

Sorry, TestMethod (strQry, strFile) should be TestMethod (sQry, sFile)

 

[min3010]

Thanks so much Premy, that worked a treat!

I just needed to add ammend TestMethod(SQry,SFile) to
Call TestMethod(SQry,SFile).

 

[boblarson]

Thanks so much Premy, that worked a treat!

I just needed to add ammend TestMethod(SQry,SFile) to
Call TestMethod(SQry,SFile).

Just an FYI about this:

If you want to use TestMethod without the keyword Call, you leave off the parentheses:

TestMethod SQry, SFile

If you use the keyword CALL then you must include the parentheses.

When using a function, if there is nothing to the left of it, like the word CALL or an = sign then use without the parens, and if there is use it with the parens.