Printing different report for different records

[gemadan96]

I have a split form, that just displays the header and datasheet. It has a list of promotions. In it are two controls to that determines the diploma to be printed and if the diploma has been printed. I've added a button control to print the unprinted diplomas. Sure enough it prints just the unprinted diplomas however it doesn't necessarily print the correct diploma. Right now there are just two diplomas, but in the future there could be more so I don't want to have to modify the code each time a new diploma is added.

The code I have at the moment is:

Private Sub cmdPrintDiplomas_Click()


DoCmd.OpenReport Me.Diploma, acViewReport, , "[DiplomaPrinted] = " & 0, acWindowNormal


End Sub

I also would like it to change the DiplomaPrinted value after it has printed. I had added - Me.DiplomaPrinted = -1 - to the code but it gives me a runtime error -2147352567: You can't assign a value to this object. When I debug it highlight that line of code.

To sum up, my first objective is to print the unprinted diplomas with the correct diploma and my second is to change it's status to printed.



Any help is appreciated.

[June7]

What criteria determines which diploma should print?

Is the field named DiplomaPrinted? Try using the bang character to indicate the field: Me!DiplomaPrinted = True

[gemadan96]

Me.Diploma determines the report to print.

Tried the Me!DiplomaPrinted = True and got the same result.

[June7]

So Diploma field has name of the report to print which gives you the dynamic criteria for selecting the appropriate report and code does not have to be modified.

I don't know why the field won't update. The field is in the form RecordSource?

If you want to provide db for analysis, follow instructions at bottom of my post.

[gemadan96]

I've done some playing around and found that it is using the designated diploma from the first record for all the records in the group. I tried changing the where clause to "[DiplomaPrinted] = " & 0 & " And " & "[PID] = " & Me.PID. That just gave me the background of the report with no data. If I set the where clause as "[PID] = " & Me.PID then I just get the first record in the list. So some how I have to add something that will look at each record determine if it's been printed. If not then it print's it using the designated report. and then changes the DiplomaPrinted value to =1 or True.

[gemadan96]

MAManager_be.zip

Here's the database. The form I'm working with is PromotionList and the Diploma button in the upper right.

[June7]

Should not post db with real personal data (phone, email, address). Is this real data in this db?

Why use embedded macros for some events and VBA for others? Why not just VBA?

I tested your form. I click the Print box for any record and diploma opens for that student. Code ran without error.

The Diplomas and Print command buttons also work.

[gemadan96]

In the list the Print column does work as it should. It's the diploma button where the code is not giving me what I want. There are four records where the DiplomaPrinted column is unchecked. It does produce all those diplomas, however one of them should print a different report. One has a diploma with a border and the other has no border and some different graphics.

This was started with one of the Microsoft templates, which contains a mixture of VBA and macros. I'm slowly migrating the macros to VBA.

[June7]

An OpenReport method can open only one report. The only criteria for selection of records is the DiplomaPrinted parameter. If you need to batch print different diplomas for different students, options:

1. one diploma and manage dynamic display of images with Image controls - ControlSource property can reference an image stored in table or externally

2. loop through a recordset to print direct to printer the appropriate report for each record

3. user selects diploma type to be printed and include that as a parameter in the OpenReport command

4. one diploma graphic design and use expressions in textboxes to manage the content for different diploma titles

[gemadan96]

Having just one report (diploma) makes sense and is preferred. Any suggestions on how to accomplish that? I do have a table for the images? I could add a field to designate the diploma the graphic is for.

I am also open to direct print. I am using report view just for testing. In the end the plan is to direct print.

I am also looking at using some VBA to to set the visibility of the control (image and text) based on the diploma.

Private Sub Report_Current()
Me.upperleft.Visible = ????
End Sub

[June7]

Already offered suggestions on how to accomplish with one report. Use Image control and expressions in textbox for dynamic output. Since this would be a small set of images, storing them in a table should not burden the db too much. Might still want to keep the images small, less than 100KB ea, for faster rendering of report. A record for each diploma type and a field for each image. Then join that table to the Promotions table linking on common Diploma type field. Code to set visibility would be unnecessary and most likely not successful.

[gemadan96]

Here's what I've done. I've created a table for diplomas with a field DiplomaName. The value in that field is the name of the report (diploma). I then created a pop up form called Select Diploma that is linked to the diplomas table. It has a combo box where the user will select the type of diploma to print. I added a button control with the following code:

Private Sub PrintDiplomas_Click()
DoCmd.OpenReport Me.Diploma, acViewReport, , "DiplomaPrinted = " & 0 & " And Diploma = '" & Me.Diploma & "'", acWindowNormal
End Sub

This seems to require the least coding, but still gives the flexibility to add new diplomas in the future.

This works. It prints the selected diploma report only for the ones where the DiplomaPrinted value is 0 or False. Now I need to change the value of DiplomaPrinted to -1 (True)

I've tried Forms![Promotion List]![DiplomaPrinted] = True in several different variations. I don't get an error. nut it doesn't change the value either.

[June7]

You went with option 3? I would have gone with 1.

If it worked, that code would change the value for only one record, not for the entire batch you printed. Use an UPDATE sql action.

CurrentDb.Execute "UPDATE Promotions SET DiplomaPrinted=True WHERE DiplomaPrinted=False AND Diploma = '" & Me.Diploma & "'"


BTW, don't need to concatenate the Boolean constant:

DoCmd.OpenReport Me.Diploma, acViewReport, , "DiplomaPrinted = 0 And Diploma = '" & Me.Diploma & "'", acWindowNormal

[gemadan96]

I wanted to do option 1, but was having trouble figuring out how to do it. If you have further insight I will certainly consider it. My plan is to store all the images externally, even if it's just a few. This will allow for easier updates to the images.

The UPDATE action did it. I updated the Boolean constant.

[June7]

What exactly about Option 1 did you have difficulty with? The ControlSource property of Image control can be bound to an attachment field (store only 1 image in the field) or an expression with path string for an external file concatenated with a field reference, like:

="X:\path\" & [fieldname]