Automatically Name Reports

[PRIreland]

I have a couple thousand reports to create as part of a data migration project. The information is in a table and I'm using reports to put the data in a report format to be OutputTo in RTF format so I can use them as attachments, etc. I can't sit and type all of these names and am wondering if there is a way to convince Access to name them for me. The most ideal would be for Access to take the name from a table that is keyed to one of the field in the report.

Example, if the key field in the report is [Group_ID] then the file would be saved as \\my documents\....\[group_ID].rtf.

Any ideas are appreciated.

Thank you

Regards,

Patrick

 

[VicRauch]

What are the fields in the table you are dealing with? When are these reports created? Are they to be created on the fly? How are you planning to write the code to export these reports to .RTF format?

Some more detail like this would help us help you.

 

[PRIreland]

I'm using a query to filter the data I want which is then used in a Report. The reports will created one time, I was planning on using OpenReport and or OutputTO from the access macros. As far as the code, I don't know, I don't know what is required.

The field names are Group_ID and Note_Text

 

[Remou]

It should be quite easy to get Access to name the reports for you, but looking at your posts I am still not sure I get where 'keyed on [group_ID]' is coming from. If the primary key is group id, do you wish the report to be called, literally, groupid or do you wish the report to be named for the first or last value of group_id, as is implied by enclosing it in brackets?
You say that the 'information is coming from a table' but I am not sure if you mean that the information for all the reports that need to be built is in a table, or that each report is based on a table. It seems to me that if you are migrating, you will need to run the whole thing over and over again to check everything before the final run, so surely the best bet is to build a table including the names of the tables to be used, the reports to be created, the sql strings etc etc. Otherwise, you may have to write huge amounts of error checking code.

 

[PRIreland]

I'm sorry I'm not clear....I'll try some more.
[group_id] is a field, it is located in the report header so when the report runs, I can get multiple lines of text for each [group_id]. The information for all the reports is in one table but, I am using a set of macro driven queries so that for each group_id that I want a report for, I'd end up with a table that only has the info for that one group_id. Then, I run the report, OutputTo the report and then rerun the macro driven queries to put fresh pieces of data into the table and repeat the process over and over again.
If I leave the filename field of the OutputTo function blank, it prompts me for a filename. If I were to do 3000 of these by hand this way, all I would enter would be the group_id (because it becomes the filename). Because the group ID changes each time the report prints, I'd end up with unique filenames that match the contents of the document that just got saved.
So, if there was a way to assign a document name when the report is output to a file (RTF) that matches the group ID that is keyed to that report via the report header and supporting table, then things would run flawlessly, I think.
I only have to do this process a couple of times; it does not have to be pretty. I'm almost there, I just can't figure out a way to convince Access to name the file automatically with the group_id as the name.

Thank you

PS: The Macro looks like this:
1)Qry to create table with Group_id = "Control", control is a separate number kept and updated in a dumb little table see below
2) Open Report in Preview (I do this so I can see the group_id that is being printed so I can name the file the same name)
3) OutputTo: Output the report in RTF but because the filename is blank, it waits for me at the prompt, so I enter the name that I obtained from looking at the report from step 2
4) Update the control variable by incrementing it by 1 number and then repeat the cycle.

 

[Remou]

Here is a very rough sketch. Does it seem to say what you want to do? A bit can be added to go through IDs (eg For intID = 1 to 100), if it is on the right lines.

Sub OutputReports()
Dim intID As Integer
Dim qdf As QueryDef
Dim OutputName As String

'This is now the 'control'
intID = 1

'Query that the report is based on
Set qdf = CurrentDb.QueryDefs("qryReports")
'Change the SQL
qdf.SQL = "Select * From tblTable Where Group_ID=" & intID

'This is now the 'control'
DoCmd.OutputTo acOutputReport, "rptReport", acFormatXLS, OutputName

End Sub

 

[VicRauch]

Well, you said it did not have to be pretty, so here is an ugly, but quick fix.

Skip your step 2.
just before you output the report:

dim RetVal as Variant
RetVal = DLookup("Group_id", "your-table-name")

Then where the OutputName goes, you can use:
RetVal & ".rtf"

Vic

 

[PRIreland]

Thanks both of you. They both look like what I'm trying to accomplish. On the first one, I'd need to run a counter, right?

I tried to implement the second idea by creating a module. The contents of the module (named module1) look like the following and it put Main in the upper RH part of the Visual Basic Screen, is that the procedure name?:

Sub Main(Retval)
Dim Retval As Variant
Retval = DLookup("MFG_GROUP", "T2")
End Sub

MFG_GROUP is the field name I want and T2 is my table name.

Then in the macro I selected OpenModule and entered Module1 as the Module Name and Main as the procedure.

Then the OutputTo, I put Retval & ".rtf" in the Output File field and I received an error. I put Retval in the Output File field and the macro generated a file named retval.

So, clearly I'm missing something. I know I'm really close on this.

Thank you.

PS: I'm a beginers beginer when it comes to VB.

 

[Remou]

Yes, you need a counter but it can start and end at any number. It seems to me that your 'little table' is a counter that is incremented by one. I notice I missed the output file name in my last post (oops). The example outputs XLS, but this is easy to change,

Sub OutputReports()
Dim intID As Integer
Dim qdf As QueryDef
Dim OutputName As String


'Change 1 and 100 to the right numbers
For intID = 1 To 100

  'Query that the report is based on
  Set qdf = CurrentDb.QueryDefs("qryReports")

  'Change the SQL for the query
  qdf.SQL = "Select * From tblTable Where Group_ID=" & intID

  'Output file name
  OutputName = "rpt" & intID & ".xls"

  'Output the report
  DoCmd.OutputTo acOutputReport, "rptReport", acFormatXLS, OutputName
Next

End Sub

 

[VicRauch]

Are you using an Access Report to output this RTF formatted file? Or are you just using the table "T2"? If you are using a Report, what is the report's name?

thanks,
Vic

 

[PRIreland]

The report's name is test. I'm in a macro then I use the OutputTo action. The report gets it information from table T2. The fields I fill in are:


Object Type: Report
Object Name: Test (test is the name of the report)
Output Format: Rich text Format
Output File: Retval & ".rtf"
Autostart: No
Template File:
Encoding:

 

[VicRauch]

I'm writting a Function for you that you will be able to call from your Macro. The function will take care of doing the RTF output. When I have the Report name, I can finish the function for you.
Vic

 

[VicRauch]

It would be good to know which folder you want these reports output to so I can put that into the Function too.

The post you just made of the OutputTo action, do you have any other actions in your macro? What I'm trying to get is all the steps you already have coded in the macro, so I can write the VBA for you. I believe it would take much longer if we just keep swapping little pieces back and forth.

Function OutputRTF(TableName)
'
Dim RetVal as Variant
Dim ReportPath as string

  ReportPath = "C:\Reports\"
  RetVal = DLookup("MFG_GROUP", "T2")
  DoCmd.OutputTo acOutputReport, "Test", acFormatRTF, ReportPath & RetVal

End Function

 

[PRIreland]

The path is: C:\Documents and Settings\pairus\My Documents\Rockwell Project\MFG_Notes\

Macro is:
1)SetWarnings
2)OpenQuery: Query Name: Update T Counter; View: Datasheet; Data Mode: Edit
3) OpenQuery: Query Name: Update T Counter 1; View: Datasheet; Data Mode: Edit
4) OpenQuery: Query Name: Query 2; View: Datasheet; Data Mode: Edit
5) OpenModule: Module Name: Module1 ProcedureName: Output Rtf

That's the whole macro, not much to it.

With your help, I believe I'll end up with a macro that basically has one action? That being to Open a Module?

Thank you very much for your help.

Patrick

 

[PRIreland]

I've got to go off-line for a few days. I'll be checking back in on Monday.

Thank you.

patrick

 

[VicRauch]

Patrick,
Need to know what the queries actually do. If you could open the queries in design view, go to the SQL view, copy all the code, and paste it into a posting for each of the three queries, that would allow me to finish this function. Also, explain what you do when the first two queries open. I see that they both open in Edit view, which tells me you must be doing something in these queries that needs to be done within the function.

Vic

 

[PRIreland]

Vic,
I don't do any editing with the queries. I just have them in edit mode becuase that is the default in Access and I don't mess with it.

The first two just increment a counter. It is very crude, but, as I said, I'm not really a VB programmer. My last programming events were using Quick Basic lots of years ago.

The third query has joins in it that pulls information from three tables, one of which is my counter. The counter makes the other two tables give-up data on a 1:1 relationship. The counter needs to run to 3335.

There are gaps in the numbering of my MFG_Groups. So, I'll need some way of not printing a report with no data. If this is too complicated, no big deal, I have another crude way of leaving the blank reports behind.

By the numbers, the queries are:

2)UPDATE TCounter SET TCounter.TCounter = [TCounter1]+1
WITH OWNERACCESS OPTION;


3)UPDATE TCounter SET TCounter.TCounter1 = [TCounter]
WITH OWNERACCESS OPTION;


4)SELECT M_MFG_GROUP_NOTES.MFG_GROUP, M_MFG_GROUP_NOTES.MFG_NOTE_ID, R_MFG_GROUP.MFG_NAME, R_MFG_GROUP.MFG_DESP, M_MFG_GROUP_NOTES.NOTE_TEXT, M_MFG_GROUP_NOTES.ADDED_BY, M_MFG_GROUP_NOTES.MODIFIED_BY, IIf(Mid(M_MFG_GROUP_NOTEs.last_modified,9,1) Like " ",Left(M_MFG_GROUP_NOTEs.last_modified,8),IIf(Mid(M_MFG_GROUP_NOTEs.last_modified,10,1) Like " ",Left(M_MFG_GROUP_NOTEs.last_modified,9),IIf(Mid(M_MFG_GROUP_NOTEs.last_modified,11,1) Like " ",Left(M_MFG_GROUP_NOTEs.last_modified,10),"ERROR"))) AS Moddate, M_MFG_GROUP_NOTES.LAST_MODIFIED INTO T2
FROM TCounter INNER JOIN (M_MFG_GROUP_NOTES INNER JOIN R_MFG_GROUP ON M_MFG_GROUP_NOTES.MFG_GROUP = R_MFG_GROUP.MFG_GROUP) ON TCounter.TCounter = R_MFG_GROUP.MFG_GROUP
ORDER BY M_MFG_GROUP_NOTES.MFG_GROUP, M_MFG_GROUP_NOTES.LAST_MODIFIED;

In that center section, I'm picking the mm/dd/yy out of a variable length field that has time stuck on it, an example of what is in that field is:
8/16/1997 2:03:21 PM
1/25/2006 8:45:27 AM
so, the query looks to find that first gap and then, based on that, takes the left hand set of characters.

I hope this helps.

Patrick

 

[PRIreland]

Hi Vic, Hi Remou,

Hope your weekend was a good one. I've been away. Am I SOL?

Patrick

 

[Remou]

Ok. I have thought about this. If you put this line:
[tt]="C:\SomeDirectory\" & DMax("ID","tblControl") & ".rtf"[/tt]
As the name of the output file in the output to part of your macro, it will output files named, say, 1.rtf, 2.rtf ...

 

[PRIreland]

THANK YOU!

That worked magnificently! This is a very simple solution that, while crude, works very very well

Thanks to you and Vic for all your help.

Nice!!!!!!

Patrick