Loop through Table records

  • Thread starter Thread starter dhoover via AccessMonster.com
  • Start date Start date
D

dhoover via AccessMonster.com

I'm using the code below to loop through records in a table, open a report
for the record selected, export the report and then loop back through for
each record in the table who has a statusid of 1. The problem is when the
code loops back thorough, it does not go to a new record, it just repeats the
process for the same record. What am I doing wrong?


On Error GoTo Err_cmdCustInfo_Click

Dim strCISFolderPath As String
Dim strFolder5 As String
Dim strFolderPath5 As String
strFolder5 = Dir(strFolderPath5, vbDirectory)

If CISFolderPath <> "" Then
strFolderPath5 = CISFolderPath
strFolder5 = Dir(strFolderPath5, vbDirectory)
End If

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from [customer info]where statusid=1;")
With rst

If Not (.EOF And .BOF) Then

.MoveFirst
Do Until .EOF

DoCmd.OpenReport "Customer Instructions", acViewPreview, , "
[CustomerID]=" & [CustomerID]
DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF,
strFolderPath5 & "\CIS.rtf", False
DoCmd.Close acOutputReport, "customer Instructions", False

.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
 
Hi

The last parameter of your OpenReport line isn't using the CustomerID from
the recordset.

Try changing the parameter to...

"[CustomerID] = " & .Fields![CustomerID]


Also, I see you are using the same filename for every row. This means the
first row's report will be overwritten by the second row's report and so on...

Do you really want one report per row and, if so, do you really want to use
the same filename?

hth

Andy Hull
 
I've changed the code a bit, but i'm still having problems. the customer
that shows up for strFolderPath is not the report that shows during the
preview, I have something mixed up somewhere. To awnser your question before,
I do want to use the same file name becuase what this is doing is exporting
the file to each customer's folder, so using the same file name is ok.





Dim strfolderpath As String
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from [customer info]INNER JOIN Region ON
[Customer Info].RegionID = Region.RegionID where statusid=1;")
Set rst2 = db.OpenRecordset("Select * from [reportinginfo];")

strfolderpath = "G:\ABL Memos\" & rst!Region & "\" & rst2!SubfolderName &
"\CIS\"

With rst

If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
Do Until .EOF


DoCmd.OpenReport "Customer Instructions", acViewPreview, , "
[CustomerID] = " & .Fields![CustomerID]

DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF,
strfolderpath & "\CIS.rtf", False
DoCmd.Close acOutputReport, "customer Instructions", False

.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing











Andy said:
Hi

The last parameter of your OpenReport line isn't using the CustomerID from
the recordset.

Try changing the parameter to...

"[CustomerID] = " & .Fields![CustomerID]

Also, I see you are using the same filename for every row. This means the
first row's report will be overwritten by the second row's report and so on...

Do you really want one report per row and, if so, do you really want to use
the same filename?

hth

Andy Hull
I'm using the code below to loop through records in a table, open a report
for the record selected, export the report and then loop back through for
Click to expand...
[quoted text clipped - 39 lines]
Set rst = Nothing
Set db = Nothing
Click to expand...
Click to expand...
 
Hi again

You need to set the folder path each time you loop to a new customer.
You should be able to use dlookup instead of a second recordset.
I've amended the code slightly below...

NOTE: I have assumed that [reportinginfo] contains the CustomerID column.
This is needed so we can look up the relevant CustomerID to get their
SubFolderName.


Dim strfolderpath As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from [customer info] INNER JOIN Region
ON [Customer Info].RegionID = Region.RegionID where statusid=1;")

With rst

If Not (.EOF And .BOF) Then
..MoveLast
..MoveFirst
Do

'next line opens report for current customer
DoCmd.OpenReport "Customer Instructions", acViewPreview, , "[CustomerID] = "
& .Fields![CustomerID]

'next line sets path for current customer
strfolderpath = "G:\ABL Memos\" & .Fields!Region & "\" &
dlookup("SubfolderName", "reportinginfo","CustomerID = " &
..Fields!CustomerID) & "\CIS\"

DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF,
strfolderpath & "\CIS.rtf", False
DoCmd.Close acOutputReport, "customer Instructions", False

..MoveNext
Loop until .EOF
End If
..Close
End With
Set rst = Nothing
Set db = Nothing

''''''''''''''''''''''''''''''''''''''''''''''''

hth

Andy Hull


dhoover via AccessMonster.com said:
I've changed the code a bit, but i'm still having problems. the customer
that shows up for strFolderPath is not the report that shows during the
preview, I have something mixed up somewhere. To awnser your question before,
I do want to use the same file name becuase what this is doing is exporting
the file to each customer's folder, so using the same file name is ok.





Dim strfolderpath As String
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from [customer info]INNER JOIN Region ON
[Customer Info].RegionID = Region.RegionID where statusid=1;")
Set rst2 = db.OpenRecordset("Select * from [reportinginfo];")

strfolderpath = "G:\ABL Memos\" & rst!Region & "\" & rst2!SubfolderName &
"\CIS\"

With rst

If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
Do Until .EOF


DoCmd.OpenReport "Customer Instructions", acViewPreview, , "
[CustomerID] = " & .Fields![CustomerID]

DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF,
strfolderpath & "\CIS.rtf", False
DoCmd.Close acOutputReport, "customer Instructions", False

.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing











Andy said:
Hi

The last parameter of your OpenReport line isn't using the CustomerID from
the recordset.

Try changing the parameter to...

"[CustomerID] = " & .Fields![CustomerID]

Also, I see you are using the same filename for every row. This means the
first row's report will be overwritten by the second row's report and so on...

Do you really want one report per row and, if so, do you really want to use
the same filename?

hth

Andy Hull
I'm using the code below to loop through records in a table, open a report
for the record selected, export the report and then loop back through for
Click to expand...
[quoted text clipped - 39 lines]
Set rst = Nothing
Set db = Nothing
Click to expand...
Click to expand...
Click to expand...
 
works perfect, thank you!!!!




Andy said:
Hi again

You need to set the folder path each time you loop to a new customer.
You should be able to use dlookup instead of a second recordset.
I've amended the code slightly below...

NOTE: I have assumed that [reportinginfo] contains the CustomerID column.
This is needed so we can look up the relevant CustomerID to get their
SubFolderName.

Dim strfolderpath As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from [customer info] INNER JOIN Region
ON [Customer Info].RegionID = Region.RegionID where statusid=1;")

With rst

If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
Do

'next line opens report for current customer
DoCmd.OpenReport "Customer Instructions", acViewPreview, , "[CustomerID] = "
& .Fields![CustomerID]

'next line sets path for current customer
strfolderpath = "G:\ABL Memos\" & .Fields!Region & "\" &
dlookup("SubfolderName", "reportinginfo","CustomerID = " &
.Fields!CustomerID) & "\CIS\"

DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF,
strfolderpath & "\CIS.rtf", False
DoCmd.Close acOutputReport, "customer Instructions", False

.MoveNext
Loop until .EOF
End If
.Close
End With
Set rst = Nothing
Set db = Nothing

''''''''''''''''''''''''''''''''''''''''''''''''

hth

Andy Hull
I've changed the code a bit, but i'm still having problems. the customer
that shows up for strFolderPath is not the report that shows during the
Click to expand...
[quoted text clipped - 63 lines]
Click to expand...
 
spoke too soon,

I forgot that I need it to pickup regionpath rather than region from the
region table in this line of code:

strfolderpath = "G:\ABL Memos\" & .Fields!Regionpath & "\" & DLookup
("SubfolderName", "reportinginfo", "CustomerID = " & .Fields!CustomerID) & "\
CIS\"


but it's cannot find RegionPath

works perfect, thank you!!!!
Hi again
Click to expand...
[quoted text clipped - 54 lines]
Click to expand...
 
i got it, the code is fine, the field was missing from the table. :)
spoke too soon,

I forgot that I need it to pickup regionpath rather than region from the
region table in this line of code:

strfolderpath = "G:\ABL Memos\" & .Fields!Regionpath & "\" & DLookup
("SubfolderName", "reportinginfo", "CustomerID = " & .Fields!CustomerID) & "\
CIS\"

but it's cannot find RegionPath
works perfect, thank you!!!!
Click to expand...
[quoted text clipped - 3 lines]
Click to expand...
 
I'm using a similar piece of code in a similar database, but I'm getting the
error "too few parameters"
at Set rst = db.OpenRecordset("Select * from qryClientInfo where
status=Active;")






Dim strPath As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from qryClientInfo where status=Active;")


With rst

If Not (.EOF And .BOF) Then
..MoveLast
..MoveFirst
Do

DoCmd.OpenReport "Customer Instructions", acViewPreview, , "[CustomerID] = "
& .Fields![CustomerID]

strPath = "G:\CIS\" & [Client Name] & " CIS"


DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF, strPath
& ".rtf", False
DoCmd.Close acOutputReport, "customer Instructions", False

..MoveNext
Loop Until .EOF
End If
..Close
End With
Set rst = Nothing
Set db = Nothing







i got it, the code is fine, the field was missing from the table. :)
spoke too soon,
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Back
Top