Use result of a Query in Code.

[DerickD]

Hey all,

Here is the situation...I have a drop down box where you select a project name. Then there is a button that you press that will import files from diffrent folders depending on the project you select in the Project drop down box.

The Project and the Path of the files are both found in one table.."Project_Path"..

This is what I have come up with but I knew from the start that it could not be as straight forward as this :

Private Sub ImportClick()

Dim myPath
Dim myFiles
Dim myImportSpec

Set myPath = "SELECT Project_Path.Path FROM Project_Path WHERE (((Project_Path.Name)=" & Me![ProjectSelect] & "));"
Set myImportSpec = "SELECT Project_Path.Spec FROM Project_Path WHERE (((Project_Path.Name)=" & Me![ProjectSelect] & "));"

myFiles = Dir(myPath & "/*.*")
Do While myFiles = ""
DoCmd.TransferText acImportDelim, myImportSpec, "Temp Import", myFiles

' ** Some appending and sorting going on here **

Loop

MsgBox ("Done!!")
End Sub

What is the correct way of doing this??

Help is needed..as if I can get to know this then alot can be done...


Regards to all,
DerickD

 

[ploeryan]

Hello

It looks like it should work fine.

you could try having the combobox rowsource to the three columns from the table - something like

SELECT DISTINCTROW [Project_Path].[Name], [Project_Path].[Path], [Project_Path].[Path_Spec] FROM [Table1];

With the column width somthing like 10cm,0cm,0cm so the second and third columns are not shown.

Then you can adjust your code to not reference the SQL queries - something like:

set mypath=me.comboboxname.column(1)
set myimportspec=me.comboboxname.column(2)

myfiles=dir(mypath,vbdirectory)
Do While myfiles<> &quot;&quot;
DoCmd.TransferText acImportDelim, myImportSpec, &quot;Temp Import&quot;, myFiles

' ** Some appending and sorting going on here **

myfiles=dir ' get next entry

Loop

Good Luck! [Afro2]

 

[DerickD]

Thanks for the reply..

That is an option that I can use well, and I can not see any problems with it.

But I would realy like to know how this is done 'correctly' (if there is a correct way). I have a feeling that it has something to do with record sets.

For me I would like to know how it is done similar to the way you can get a list of file names from a folder :

Dim myFileName
myFileName = Dir (c:\*.*)
Do while myFileName = ""
MsgBox(myFileName)
Loop
[\code]

Do you see what I mean? You get a list of results pumped out with every loop.
Thanks..