Loop Through a Microsoft Access Table

[spenn]

I am trying to loop through a table and for each record create a snapshot
report. I need to use the name out of the table to save it under that name. I
have the report set up but when you run the report the report is saved as one
big report. I need a report for each. Can Somebody help me?

 

[aaron.kempf]

how many tables you got

what exactly are you trying to do?

 

[spenn]

I have One Table that is pulling all the data. I am trying to make a snapshot
report of each record in the table. Then I am going to use the snapshots for
a website

 

[spenn]

I will send you the report if you need to look at it. The information is
about datacards.
my email
(e-mail address removed)

 

[aaron.kempf]

gosh i just dont know if you need data warehousign advice-- or if you
need help with Access snapshots

tell me more about what you had in mind

like exporting a report to a SNP format through the command line?

you can make a macro
(docmd. if in vba)
OutputTo outputReport, rptName, acSnp; etc

let me know what you need

 

[spenn]

can I write a module in access to loop through the table and run the report
for each?

 

[spenn]

I know how to export the SNP. I think that I need a module to run the report
for each record and take the title name out of the table and make that the
file name. I need a SNP report for each record that is in the database so
that I can post it on the internet.

Do u think I need to use VB.NEt or can I use a module in access?
And How?

 

[Dirk Goldgar]

I am trying to loop through a table and for each record create a
snapshot report. I need to use the name out of the table to save it
under that name. I have the report set up but when you run the report
the report is saved as one big report. I need a report for each. Can
Somebody help me?

You can use code something like this "air code":

'----- start of example code -----
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("YourTable")

With rs
Do Until .EOF

DoCmd.OpenReport "YourReport", acViewPreview, , _
"CustomerID=" & !CustomerID

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & !CustomerName & ".snp"

DoCmd.Close acReport, "YourReport", acSaveNo

.MoveNext
Loop
.Close
End With
'----- end of example code -----

The code assumes that there is a field named "CustomerID" that is the
(numeric) primary key of table "YourTable", and there's a field named
"CustomerName" that is what you want to use as the name of the output
snapshot file.

 

[spenn]

Do i put that code in a module in access?

You can use code something like this "air code":

'----- start of example code -----
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("YourTable")

With rs
Do Until .EOF

DoCmd.OpenReport "YourReport", acViewPreview, , _
"CustomerID=" & !CustomerID

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & !CustomerName & ".snp"

DoCmd.Close acReport, "YourReport", acSaveNo

.MoveNext
Loop
.Close
End With
'----- end of example code -----

The code assumes that there is a field named "CustomerID" that is the
(numeric) primary key of table "YourTable", and there's a field named
"CustomerName" that is what you want to use as the name of the output
snapshot file.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[spenn]

i tried that code it says syntax error. here is the code can u tell me
whats wrong?
thanks
DATACARDS SORT FORM is the table
I am getting an error on the openReport
The field that i need is Datacard Title.
I am getting error on the output to I need the datacard title to be the save
name.
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARDS SORT FORM")

With rs
Do Until .EOF

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"Datacard Title=" & !Datacard Title

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & !Datacard Title & ".snp"

DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With

 

[Dirk Goldgar]

Do i put that code in a module in access?

It's not complete; it needs the rest of the procedure -- at least a Sub
statement and End Sub statement -- to go around it. Would you be
requesting this function from a command button on a form? In that case,
the code would go into the event procedure for that command button's
Click event.

 

[Dirk Goldgar]

i tried that code it says syntax error. here is the code can u tell
me whats wrong?
thanks
DATACARDS SORT FORM is the table
I am getting an error on the openReport
The field that i need is Datacard Title.
I am getting error on the output to I need the datacard title to be
the save name.
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARDS SORT FORM")

With rs
Do Until .EOF

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"Datacard Title=" & !Datacard Title

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & !Datacard Title & ".snp"

DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With

I see two problems. First, since the field name "Datacard Title"
contains a space, it must be enclosed in square brackets whereever you
refer to it. Second, I'm assuming that's a text field, and so when you
write the criteria for the report you have to get quotes into the
criteria string around the field value. Try this revision of the
relevant lines:

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"Datacard Title=" & _
Chr(34) & ![Datacard Title] & Chr(34)

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & ![Datacard Title] & ".snp"
:

 

[spenn]

I put the code in and I am getting a error message:
Runtime error '3075'
Syntax error (missing operator) in query expression'(Datacard
Title=Lifestyle's new American Family)'

i am getting it on this line of code
DoCmd.OpenReport "Datacards Report", acViewPreview, , "Datacard Title=" &
![Datacard Title]

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & "Datacard Title" & ".snp"

 

[Dirk Goldgar]

I put the code in and I am getting a error message:
Runtime error '3075'
Syntax error (missing operator) in query expression'(Datacard
Title=Lifestyle's new American Family)'

i am getting it on this line of code
DoCmd.OpenReport "Datacards Report", acViewPreview, , "Datacard
Title=" & ![Datacard Title]

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & "Datacard Title" & ".snp"

You didn't make the full correction to your code that I posted in my
last message, and now I see that I made a mistake anyway. Try this:

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)

 

[spenn]

what does this do?
"Datacard Title=" & _
Chr(34) & ![Datacard Title] & Chr(34)
why do u need "Datacard Title = " ?
It is still throwing the same error.

i tried that code it says syntax error. here is the code can u tell
me whats wrong?
thanks
DATACARDS SORT FORM is the table
I am getting an error on the openReport
The field that i need is Datacard Title.
I am getting error on the output to I need the datacard title to be
the save name.
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARDS SORT FORM")

With rs
Do Until .EOF

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"Datacard Title=" & !Datacard Title

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & !Datacard Title & ".snp"

DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With

I see two problems. First, since the field name "Datacard Title"
contains a space, it must be enclosed in square brackets whereever you
refer to it. Second, I'm assuming that's a text field, and so when you
write the criteria for the report you have to get quotes into the
criteria string around the field value. Try this revision of the
relevant lines:

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"Datacard Title=" & _
Chr(34) & ![Datacard Title] & Chr(34)

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & ![Datacard Title] & ".snp"
:
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

what does this do?
"Datacard Title=" & _
Chr(34) & ![Datacard Title] & Chr(34)
why do u need "Datacard Title = " ?
It is still throwing the same error.

As you'll see from my last message, there's an error in that code. It
should be:

"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)

That is the WhereCondition argument for the OpenReport method -- it
specifies a filter to apply to the data when opening the report. In
this case, we want the report to include only those records where the
[Datacard Title] field matches the one we're getting from the recordset.
The Chr(34) function calls embed double-quotes (") around the value
we're providing from the recordset, so that the resulting filter string
is correctly formatted.

 

[spenn]

Thanks for all the help but I have another problem

here is the code:

Public Sub outputreport()
'DoCmd.OutputTo acOutputReport, "Dataards Report", acFormatSNP,
CurrentProject.Path

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARD FORM SORT")

With rs
Do Until .EOF
DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)


'DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"Datacard Title=" & ![Datacard Title]

DoCmd.OutputTo acOutputReport, "Datacards Report", acFormatSNP, _
"C:\Documents and Settings\Owner\Desktop\New Folder"
& ![Datacard Title] & ".snp"



DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With


End Sub



the error says: The OutputTo action was canceled.
the screen comes up and says out puting then the error message comes up.
can u help me
thanks

I put the code in and I am getting a error message:
Runtime error '3075'
Syntax error (missing operator) in query expression'(Datacard
Title=Lifestyle's new American Family)'

i am getting it on this line of code
DoCmd.OpenReport "Datacards Report", acViewPreview, , "Datacard
Title=" & ![Datacard Title]

DoCmd.OutputTo acOutputReport, , acFormatSNP, _
"C:\Temp\" & "Datacard Title" & ".snp"

You didn't make the full correction to your code that I posted in my
last message, and now I see that I made a mistake anyway. Try this:

DoCmd.OpenReport "Datacard Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for all the help but I have another problem

here is the code:

Public Sub outputreport()
'DoCmd.OutputTo acOutputReport, "Dataards Report", acFormatSNP,
CurrentProject.Path

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARD FORM SORT")

With rs
Do Until .EOF
DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)


'DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"Datacard Title=" & ![Datacard Title]

DoCmd.OutputTo acOutputReport, "Datacards Report",
acFormatSNP, _ "C:\Documents and
Settings\Owner\Desktop\New Folder" & ![Datacard Title] & ".snp"



DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With


End Sub



the error says: The OutputTo action was canceled.
the screen comes up and says out puting then the error message comes
up. can u help me
thanks

A simplified version of that code works for me. Two things occur to me.
First, I think you need a back-slash on the end of the folder path when
you build your output file name, like this:

"C:\Documents and Settings\Owner\Desktop\New Folder\"
& ![Datacard Title] & ".snp"

Second, have you inspected the values of [Datacard Title] to make sure
that they are all valid for file names? There are characters that a
Windows file name cannot contain.

 

[spenn]

I fixed the the \ after the path, and the Datacard Title only contains
spaces. I took it the Datacard title out and put a fixed file name. What else
can be the problem?
Thanks

Thanks for all the help but I have another problem

here is the code:

Public Sub outputreport()
'DoCmd.OutputTo acOutputReport, "Dataards Report", acFormatSNP,
CurrentProject.Path

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARD FORM SORT")

With rs
Do Until .EOF
DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)


'DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"Datacard Title=" & ![Datacard Title]

DoCmd.OutputTo acOutputReport, "Datacards Report",
acFormatSNP, _ "C:\Documents and
Settings\Owner\Desktop\New Folder" & ![Datacard Title] & ".snp"



DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With


End Sub



the error says: The OutputTo action was canceled.
the screen comes up and says out puting then the error message comes
up. can u help me
thanks

A simplified version of that code works for me. Two things occur to me.
First, I think you need a back-slash on the end of the folder path when
you build your output file name, like this:

"C:\Documents and Settings\Owner\Desktop\New Folder\"
& ![Datacard Title] & ".snp"

Second, have you inspected the values of [Datacard Title] to make sure
that they are all valid for file names? There are characters that a
Windows file name cannot contain.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[spenn]

The Problem it is throwing is error 2501 Invalid page.
Run-time error 2501:
The OutputTo action was canceled.
here is the code:

Public Sub outputreport()


Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARD FORM SORT")

With rs
Do Until .EOF
DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)



DoCmd.OutputTo acOutputReport, "Datacards Report", acFormatSNP, _
"C:\Documents and Settings\Owner\Desktop\New
Folder\" & ![Datacard Title] & ".snp"



DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With


End Sub
thanks a lot
steven

I fixed the the \ after the path, and the Datacard Title only contains
spaces. I took it the Datacard title out and put a fixed file name. What else
can be the problem?
Thanks

Thanks for all the help but I have another problem

here is the code:

Public Sub outputreport()
'DoCmd.OutputTo acOutputReport, "Dataards Report", acFormatSNP,
CurrentProject.Path

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("DATACARD FORM SORT")

With rs
Do Until .EOF
DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"[Datacard Title]=" & _
Chr(34) & ![Datacard Title] & Chr(34)


'DoCmd.OpenReport "Datacards Report", acViewPreview, , _
"Datacard Title=" & ![Datacard Title]

DoCmd.OutputTo acOutputReport, "Datacards Report",
acFormatSNP, _ "C:\Documents and
Settings\Owner\Desktop\New Folder" & ![Datacard Title] & ".snp"



DoCmd.Close acReport, "Datacard Report", acSaveNo

.MoveNext
Loop
.Close
End With


End Sub



the error says: The OutputTo action was canceled.
the screen comes up and says out puting then the error message comes
up. can u help me
thanks

A simplified version of that code works for me. Two things occur to me.
First, I think you need a back-slash on the end of the folder path when
you build your output file name, like this:

"C:\Documents and Settings\Owner\Desktop\New Folder\"
& ![Datacard Title] & ".snp"

Second, have you inspected the values of [Datacard Title] to make sure
that they are all valid for file names? There are characters that a
Windows file name cannot contain.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)