Print different reports based on field value?

[kv29]

Hi everyone! I really hope my problem will be a piece of cake for an expert coder :)

I have a table with 100 records, and I need to print them all between 10 different reports based on each record field value, all with a single click.

As you guessed, I dont know much about vb, so I would really appreciate some code to do what is required.

thanks!

[Stewart Ross]

Hi. I am sure that there would be many contributors who could assist you once you tell us what it is you need to do. As it is we would just be guessing.

Do the ten different reports have the same recordsource? What is the relationship between the reports and the table with 100 records? On what basis do you select the reports to be printed? Have you a form with a selection combo or listbox for users to select particular reports?

These are just starter questions. To provide any form of solution we would need to know form names, control names, field names and relationships for relevant fields.

Please understand, what you ask is non-trivial and cannot be answered by guesswork...

-Stewart

Hi everyone! I really hope my problem will be a piece of cake for an expert coder :)

I have a table with 100 records, and I need to print them all between 10 different reports based on each record field value, all with a single click.

As you guessed, I dont know much about vb, so I would really appreciate some code to do what is required.

thanks!

[kv29]

Stewart, the table is quite simple, has 4 fields and the last one ("code") allows numbers from 1 to 10 (that represent those 10 different reports, also named "1" to "10"). no need for the user to fill the "code" field.

what I need is to print them all, each record on its own report, in a single print batch.

[Stewart Ross]

This is useful clarification - thank you. There are still details missing, however. What is the name of the table? What is the name of the field that makes each record in the 100 records unique?

From what you have told me so far, there are ten reports to be run. If I was being naive about it I would provide you with a simple loop in which a counter runs from 1 to 10, that opens each report in turn. However, such an approach has nothing whatsoever to do with the 100 records you mention:

Dim Counter as Integer
For Counter = 1 to 10
   DoCmd.OpenReport Str(Counter), acViewNormal
Next Counter

I know from experience that is not what you need - but what you have told me does not actually help me to provide you with a full solution. The norm is to process the recordset contents in a different kind of loop like the one below. To print each record separately as you are requesting you need to filter the report for each unique record, but you have not said what field that is. A placeholder is shown below for the table name and for that unique field.

[code=vb]Dim RS as DAO.RecordSet
Dim ReportNo as String
Dim ReportFilter as String
Set RS = CurrentDB.OpenRecordset("name of your 100 record table")
Do While Not RS.EOF
ReportNo = Str(RS!

)
  ReportFilter = "[unique record ID] = " & RS![unique record ID]
  DoCmd.OpenReport ReportNo, acViewNormal, , ReportFilter
  RS.Movenext
Loop
RS.Close

The skeleton above is incomplete because, as I have said, the missing details are not things I can guess at. The code above assumes that the unique ID is some form of number. If it is a string value change the filter line to

ReportFilter = "[unique record ID] = '" & RS![unique record ID] & "'"

Add the code above to the On Click event of a control you place on a form somewhere. You may also need to add a reference to the DAO Object library - from the VB editor choose Tools, References and tick the MS DAO 3.x object library if it is not already ticked.

I am sure that once you fill in the missing details that I cannot you will be able to resolve any remaining issues reasonably easily yourself.

-Stewart

[kv29]

Stewart, your code works like a charm!
Every report is to be sent to a printed form, so they are supposed to fill "empty fields". Do you suggest to put some kind of pause in between not to screw the printing order?

btw, I was thinking to change the "code" field (and so all the reports) from numbers to text, do I need to change something on your code?

thanks!!