Loop Through Files

S

Stephen Lynch

First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing datesonly. For
example, in the table files names are as such:

FileName Path Status
071208.rps C:\test1 Posted
071208.rps C:\test2 Pending
071208.rps C:\test345 Pending
071208.rps C:\test432 Pending
071308.rps C:\test1 Pending
071308.rps C:\test2 Pending
071308.rps C:\test345 Pending
071308.rps C:\test432 Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208, have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try at it
for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;



Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

Dim stFileName As String

Dim stFilePath As String

Dim stFileID As String



'Declare files names as variables

stFileName = Me![lstboxPost].Value

stFilePath = Me![lstboxPost].Column(1)

stFileId = Me![lstboxPost].Column(4)



'Loop through each filename that starts with date



Do Until EOF(stFileName)

'Do something like run sql code to update file'

Loop



Anyway, thanks in advanve.

Steve
 
P

pietlinden

First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing datesonly. For
example, in the table files names are as such:

FileName        Path                Status
071208.rps    C:\test1            Posted
071208.rps    C:\test2            Pending
071208.rps    C:\test345        Pending
071208.rps    C:\test432        Pending
071308.rps    C:\test1            Pending
071308.rps    C:\test2            Pending
071308.rps    C:\test345        Pending
071308.rps    C:\test432        Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208, have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try at it
for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;

Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

    Dim stFileName As String

    Dim stFilePath As String

    Dim stFileID As String

    'Declare files names as variables

        stFileName = Me![lstboxPost].Value

        stFilePath = Me![lstboxPost].Column(1)

        stFileId = Me![lstboxPost].Column(4)

'Loop through each filename that starts with date

Do Until EOF(stFileName)

 'Do something like run sql code to update file'

Loop

Anyway, thanks in advanve.

Steve
Click to expand...

You just want to update the STATUS of the item and nothing more? (Not
import or anything like that).

Then this is simple.
Use the code at http://www.mvps.org/access/forms/frm0007.htm

You could build the SQL filter in your code and then just run an
update query instead of a select query. Just use something like

dim strSQL = "UPDATE MyTable SET MyField1='Something',
MyField2=SomeNumericValue WHERE MyTable.Status='Pending';

'you could loop through your listbox here and append filter
criteria...
strSql = "UPDATE tblMember SET BatchID = " & lngBatchID & " WHERE
BatchID Is Null;"
db.Execute strSql, dbFailOnError
lngKt = db.RecordsAffected
 
S

Stephen Lynch

Thanks for the help, but I actually need to granb the file path and name and
run a few procedures using the file names.

So if I read the page that you redirected me to, basically it is saying add,
let's say another list box that's hidden and populate the name of the files
and paths based on the double click event of the non hidden listbox and then
loop through each of thouse files that are in the hidden text box.



First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing datesonly.
For
example, in the table files names are as such:

FileName Path Status
071208.rps C:\test1 Posted
071208.rps C:\test2 Pending
071208.rps C:\test345 Pending
071208.rps C:\test432 Pending
071308.rps C:\test1 Pending
071308.rps C:\test2 Pending
071308.rps C:\test345 Pending
071308.rps C:\test432 Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208, have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try at
it
for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;

Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

Dim stFileName As String

Dim stFilePath As String

Dim stFileID As String

'Declare files names as variables

stFileName = Me![lstboxPost].Value

stFilePath = Me![lstboxPost].Column(1)

stFileId = Me![lstboxPost].Column(4)

'Loop through each filename that starts with date

Do Until EOF(stFileName)

'Do something like run sql code to update file'

Loop

Anyway, thanks in advanve.

Steve
Click to expand...

You just want to update the STATUS of the item and nothing more? (Not
import or anything like that).

Then this is simple.
Use the code at http://www.mvps.org/access/forms/frm0007.htm

You could build the SQL filter in your code and then just run an
update query instead of a select query. Just use something like

dim strSQL = "UPDATE MyTable SET MyField1='Something',
MyField2=SomeNumericValue WHERE MyTable.Status='Pending';

'you could loop through your listbox here and append filter
criteria...
strSql = "UPDATE tblMember SET BatchID = " & lngBatchID & " WHERE
BatchID Is Null;"
db.Execute strSql, dbFailOnError
lngKt = db.RecordsAffected
 
S

Stephen Lynch

Or can I loop through a recordset. I grab the date from the listbox, and
then run a query based on this date. Then loop though each iten in the
query and pass the query result out to the function.


Open recordset (record where Filedate = lstbox.value)
Get first record
Save field filepath in memory as fpath
Save field filename as fname in memory
Call Function (fpath, fname)

Loop


First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing datesonly.
For
example, in the table files names are as such:

FileName Path Status
071208.rps C:\test1 Posted
071208.rps C:\test2 Pending
071208.rps C:\test345 Pending
071208.rps C:\test432 Pending
071308.rps C:\test1 Pending
071308.rps C:\test2 Pending
071308.rps C:\test345 Pending
071308.rps C:\test432 Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208, have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try at
it
for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;

Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

Dim stFileName As String

Dim stFilePath As String

Dim stFileID As String

'Declare files names as variables

stFileName = Me![lstboxPost].Value

stFilePath = Me![lstboxPost].Column(1)

stFileId = Me![lstboxPost].Column(4)

'Loop through each filename that starts with date

Do Until EOF(stFileName)

'Do something like run sql code to update file'

Loop

Anyway, thanks in advanve.

Steve
Click to expand...

You just want to update the STATUS of the item and nothing more? (Not
import or anything like that).

Then this is simple.
Use the code at http://www.mvps.org/access/forms/frm0007.htm

You could build the SQL filter in your code and then just run an
update query instead of a select query. Just use something like

dim strSQL = "UPDATE MyTable SET MyField1='Something',
MyField2=SomeNumericValue WHERE MyTable.Status='Pending';

'you could loop through your listbox here and append filter
criteria...
strSql = "UPDATE tblMember SET BatchID = " & lngBatchID & " WHERE
BatchID Is Null;"
db.Execute strSql, dbFailOnError
lngKt = db.RecordsAffected
 
S

Stephen Lynch

Ok, Now I tried this. It works on the first file but seems to hang forever,
any deas?
Private Sub List119_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset
Dim strSql As String

Dim stFileName As String
Dim stFilePath As String
Dim stFileDate As String
Dim stFileInput As String
Dim stFileOutput As String

strSql = "SELECT tblImportedFileNames.FName, tblImportedFileNames.FPath,
tblImportedFileNames.Status, Mid([FName],3,6) AS Expr1 FROM
tblImportedFileNames WHERE (((tblImportedFileNames.FName) Like '*.rps') AND
((tblImportedFileNames.Status)='Pending') AND ((Mid([FName],3,6))=70908));"

'Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

stFileName = rs.Fields("FName")
stFilePath = rs.Fields("FPath")
stFileDate = rs.Fields("Expr1")
stFileInput = rs.Fields("FPath") & rs.Fields("FName")
stFileOutput = rs.Fields("FPath") & "test.txt"

Call AddDatetoRPS(stFileInput, stFileOutput, stFileDate, 8004612)

rs.MoveNext
Do While Not rs.EOF
Loop

End Sub

Stephen Lynch said:
First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing datesonly.
For example, in the table files names are as such:

FileName Path Status
071208.rps C:\test1 Posted
071208.rps C:\test2 Pending
071208.rps C:\test345 Pending
071208.rps C:\test432 Pending
071308.rps C:\test1 Pending
071308.rps C:\test2 Pending
071308.rps C:\test345 Pending
071308.rps C:\test432 Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208, have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try at
it for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;



Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

Dim stFileName As String

Dim stFilePath As String

Dim stFileID As String



'Declare files names as variables

stFileName = Me![lstboxPost].Value

stFilePath = Me![lstboxPost].Column(1)

stFileId = Me![lstboxPost].Column(4)



'Loop through each filename that starts with date



Do Until EOF(stFileName)

'Do something like run sql code to update file'

Loop



Anyway, thanks in advanve.

Steve
Click to expand...
 
S

Steve Sanford

Hi Steve,

It seems to hang because you don't have anything in the "Do Loop".

Also, if the file name is "071208.rps", Mid([FName],3,6) will not return in
"071208", it returns "1208.r".

I modified your code a little. Try this:


The first two lines of the code page should be:

Option Compare Database
Option Explicit

' --------- code beg -----------------
Private Sub List119_DblClick(Cancel As Integer)
On Error GoTo ErrorHandler

Dim rs As DAO.Recordset
Dim strSql As String

Dim stFileName As String
Dim stFilePath As String
Dim stFileDate As String
Dim stFileInput As String
Dim stFileOutput As String

strSql = "SELECT FName, FPath, Status, Mid([FName],3,6) AS Expr1 "
strSql = strSql & " From tblImportedFileNames"
strSql = strSql & " WHERE FName Like '*.rps' AND "
strSql = strSql & " Status = 'Pending' AND "
strSql = strSql & " Mid([FName],3,6))= '" & Me.List119 & "';"

'Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

Do While Not rs.EOF
stFileName = rs.Fields("FName")
stFilePath = rs.Fields("FPath")
stFileDate = rs.Fields("Expr1")

'you might need to change these to
' stFileInput = rs.Fields("FPath") & "\" & rs.Fields("FName")
' stFileOutput = rs.Fields("FPath") & "\" & "test.txt"

stFileInput = rs.Fields("FPath") & rs.Fields("FName")
stFileOutput = rs.Fields("FPath") & "test.txt"

Call AddDatetoRPS(stFileInput, stFileOutput, stFileDate, 8004612)

rs.MoveNext
Loop

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub


ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here

End Sub
' --------- code end -----------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Stephen Lynch said:
Ok, Now I tried this. It works on the first file but seems to hang forever,
any deas?
Private Sub List119_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset
Dim strSql As String

Dim stFileName As String
Dim stFilePath As String
Dim stFileDate As String
Dim stFileInput As String
Dim stFileOutput As String

strSql = "SELECT tblImportedFileNames.FName, tblImportedFileNames.FPath,
tblImportedFileNames.Status, Mid([FName],3,6) AS Expr1 FROM
tblImportedFileNames WHERE (((tblImportedFileNames.FName) Like '*.rps') AND
((tblImportedFileNames.Status)='Pending') AND ((Mid([FName],3,6))=70908));"

'Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

stFileName = rs.Fields("FName")
stFilePath = rs.Fields("FPath")
stFileDate = rs.Fields("Expr1")
stFileInput = rs.Fields("FPath") & rs.Fields("FName")
stFileOutput = rs.Fields("FPath") & "test.txt"

Call AddDatetoRPS(stFileInput, stFileOutput, stFileDate, 8004612)

rs.MoveNext
Do While Not rs.EOF
Loop

End Sub

Stephen Lynch said:
First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing datesonly.
For example, in the table files names are as such:

FileName Path Status
071208.rps C:\test1 Posted
071208.rps C:\test2 Pending
071208.rps C:\test345 Pending
071208.rps C:\test432 Pending
071308.rps C:\test1 Pending
071308.rps C:\test2 Pending
071308.rps C:\test345 Pending
071308.rps C:\test432 Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208, have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try at
it for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;



Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

Dim stFileName As String

Dim stFilePath As String

Dim stFileID As String



'Declare files names as variables

stFileName = Me![lstboxPost].Value

stFilePath = Me![lstboxPost].Column(1)

stFileId = Me![lstboxPost].Column(4)



'Loop through each filename that starts with date



Do Until EOF(stFileName)

'Do something like run sql code to update file'

Loop



Anyway, thanks in advanve.

Steve
Click to expand...
Click to expand...
 
S

Stephen Lynch

Steve;

Genius, my man, just genius. Sorry for the late delay. Thanks for all of
the help, I would have struggled for many, many hours on this.


Steve Sanford said:
Hi Steve,

It seems to hang because you don't have anything in the "Do Loop".

Also, if the file name is "071208.rps", Mid([FName],3,6) will not return
in
"071208", it returns "1208.r".

I modified your code a little. Try this:


The first two lines of the code page should be:

Option Compare Database
Option Explicit

' --------- code beg -----------------
Private Sub List119_DblClick(Cancel As Integer)
On Error GoTo ErrorHandler

Dim rs As DAO.Recordset
Dim strSql As String

Dim stFileName As String
Dim stFilePath As String
Dim stFileDate As String
Dim stFileInput As String
Dim stFileOutput As String

strSql = "SELECT FName, FPath, Status, Mid([FName],3,6) AS Expr1 "
strSql = strSql & " From tblImportedFileNames"
strSql = strSql & " WHERE FName Like '*.rps' AND "
strSql = strSql & " Status = 'Pending' AND "
strSql = strSql & " Mid([FName],3,6))= '" & Me.List119 & "';"

'Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

Do While Not rs.EOF
stFileName = rs.Fields("FName")
stFilePath = rs.Fields("FPath")
stFileDate = rs.Fields("Expr1")

'you might need to change these to
' stFileInput = rs.Fields("FPath") & "\" & rs.Fields("FName")
' stFileOutput = rs.Fields("FPath") & "\" & "test.txt"

stFileInput = rs.Fields("FPath") & rs.Fields("FName")
stFileOutput = rs.Fields("FPath") & "test.txt"

Call AddDatetoRPS(stFileInput, stFileOutput, stFileDate, 8004612)

rs.MoveNext
Loop

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub


ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here

End Sub
' --------- code end -----------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Stephen Lynch said:
Ok, Now I tried this. It works on the first file but seems to hang
forever,
any deas?
Private Sub List119_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset
Dim strSql As String

Dim stFileName As String
Dim stFilePath As String
Dim stFileDate As String
Dim stFileInput As String
Dim stFileOutput As String

strSql = "SELECT tblImportedFileNames.FName, tblImportedFileNames.FPath,
tblImportedFileNames.Status, Mid([FName],3,6) AS Expr1 FROM
tblImportedFileNames WHERE (((tblImportedFileNames.FName) Like '*.rps')
AND
((tblImportedFileNames.Status)='Pending') AND
((Mid([FName],3,6))=70908));"

'Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

stFileName = rs.Fields("FName")
stFilePath = rs.Fields("FPath")
stFileDate = rs.Fields("Expr1")
stFileInput = rs.Fields("FPath") & rs.Fields("FName")
stFileOutput = rs.Fields("FPath") & "test.txt"

Call AddDatetoRPS(stFileInput, stFileOutput, stFileDate, 8004612)

rs.MoveNext
Do While Not rs.EOF
Loop

End Sub

Stephen Lynch said:
First thanks for all the help recieved in the past. I have a recordset
linked to a listbox. On the listbox I have the output showing
datesonly.
For example, in the table files names are as such:

FileName Path Status
071208.rps C:\test1 Posted
071208.rps C:\test2 Pending
071208.rps C:\test345 Pending
071208.rps C:\test432 Pending
071308.rps C:\test1 Pending
071308.rps C:\test2 Pending
071308.rps C:\test345 Pending
071308.rps C:\test432 Pending

I grouped my List box to show:

071208
071308

What I am trying to do is when I double click the entry like 071208,
have
the code loop though each file that starts with the 071208 and has an
extension of rps and staus is Pending .

I have written a few loops before but I am stuck. Here is my lame try
at
it for the last hour before asking for help.

My SQL Statement on my Listbox

SELECT Mid([tblImportedFileNames]![FName],3,6) AS FileDate,
tblImportedFileNames.Status

FROM tblImportedFileNames

WHERE (((Right([tblImportedFileNames]![FName],3))="rps"))

GROUP BY Mid([tblImportedFileNames]![FName],3,6),
tblImportedFileNames.Status

HAVING (((tblImportedFileNames.Status)="Pending"))

ORDER BY Mid([tblImportedFileNames]![FName],3,6) DESC;



Code

Private Sub lstboxPost_DblClick(Cancel As Integer)

Dim stFileName As String

Dim stFilePath As String

Dim stFileID As String



'Declare files names as variables

stFileName = Me![lstboxPost].Value

stFilePath = Me![lstboxPost].Column(1)

stFileId = Me![lstboxPost].Column(4)



'Loop through each filename that starts with date



Do Until EOF(stFileName)

'Do something like run sql code to update file'

Loop



Anyway, thanks in advanve.

Steve
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop through workbooks 2
VBA Loop through Specific Subfolders to find files (drill down 4 levels) 3
for next loop problem 3
Loop through query records 3
loop through (should be easy) 0

Top