On form run code for each record in table 1

[JamieArvato]

Hello, I'm trying to run code which imports x number of files using text boxes to hold folder/filename and then using import queries, etc.

Unfortunately though I have it set up as:

Folder1 Filename1
Folder2 Filename2
Folder3 Filename3
Folder4 Filename4

All as seperate text boxes, which are all seperate COLUMNS in a Filepath table (50 column table)

The code then goes something like

'1st File
x = "C:\" + Folder1 + Filename1
ImportSpreadsheet
Run queries

'2nd File
x = "C:\" + Folder2 + Filename2
ImportSpreadsheet
Run queries

etc

I'm sure you can see where I'm going with this, I have 25 files to import.

How can I have a single table with a Folder column and Filename column which is the ControlSource for a Form and then say for each record in table:

ImportSpreadsheet
Run queries

Thanks.

In Excel I could confidently use loops to go down a list of cells if an Import flag was 'Y' then it would import and then put the record count next to the Filename and a Success Y/N next to each one. The way I did this was by coding to one set of cells, say row 5 then looped to put the filename in this exact row and import, then move the next filename up and import, etc.

Here is one section of code:

fp = Me![Drive] + ("Me![CF" + x + "]") + "\Timesheet - " + Me![CTS1] + " - " + Me![WE]

If [Forms]![Frm_Import]![Check1] = True Then
Me![Text199].Value = ""
Me![Text231].Value = ""
Me.Repaint
DoCmd.TransferSpreadsheet acImport, cSpreadsheetTypeExcel9, "TblImport", fp, _
True, "Total_Week_Data"
Me![Text199].Value = "Imp:" + Str(DCount("*", "TblImport")) + ", "
Me.Repaint
ADRecsBeforeAppend = DCount("*", "Tbl_All_Data")
Import_Run_Queries
ADRecsAfterAppend = DCount("*", "Tbl_All_Data")
Me![Text199].Value = Me![Text199].Value + "Mov:" + Str(ADRecsAfterAppend - ADRecsBeforeAppend)
Me![Status1].BackColor = vbRed
TimeAfter = Now()
Me![Text231].Value = (TimeAfter - TimeBefore)
Me.Repaint

 

[randy700]

How about....

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName")
rs.MoveFirst
Do Until rs.EOF
strSQL = "c:\" & rs!FolderName & "\" & rs!FileName
Your code to import and run queries
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing


Randy