Customized On Close Event Procedure on Access Report 1

[ProgEvalJAA]

I want to add an Event Procedure to my Access Report to ask my users if they want to print three copies of my Deficiency Report before closing it. I want to attach it to On Close.

If they say yes to printing, I want the procedure to print three copies with three different labels:

1 - Internal routing copy
2 - External copy
3 - Personal file copy

If they say no, I want the report to close as usual.

Any thoughts on the code I need to do this?

 

[ProgEvalJAA]

I am close to solving this, but I'm hopeful someone else can provide just a bit more guidance relative to the code itself as outlined later in this post.

I created a Print Report Dialog Box Form.

It has three parts to it:

1)Print Choices - This provides users two options in the form of an option group: (1) to choose to print one copy (2) or to choose to print three copies.

2)Print Destination- This gives users the choice between printing or previewing the report. Again, I've used an option group here.

3)Command buttons - I have two command buttons here, one for the actual printing and one for the cancel event.

I'm having trouble with the Print event procedures. I'm going to attach the Event Procedure to the Print command button and attach it to the OnClick property. I want to be able to print one copy or three copies, depending on the user's choice. If the user selects the choice to print three copies, I'd like to be able to change the labels on all three to read "File Copy", "External Copy", etc.

On another tech forum, someone posted the following entry:

[purple]clearly you can't directly print 3 different reports with a single click unless you use 3 part stationery with different texts pre-printed
----------
so to print 3 slightly different reports, you need to loop round a print command 3 times, and depending on what the print number is, set a watermark or text box/label on the report to carry the variable text.

so on your button do something like

for rcount = 1 to 3
docmd.openreport "reportname"
next rcount

and ten in the openevent for "reportname" have

select case rcount:
case 1: mytext = "copy"
case 2: mytext = "Audit"
end select" [/purple]

Can someone review this and point me in the right direction? I'm extremely new to Visual Basic and have struggled for two days to find the right code. I'm thinking this might be close to what I need, but I'm not sure.

 

[dhookom]

Depending on your needs, you could create a table
[tt][blue]
tblCopies
--------------------------
CopyNum values 1, 2, 3,...
CopyName values Internal, External, Personal
[/blue][/tt]
Add this table to your report's record source and don't join it to anything. If you don't add any criteria under the CopyNum field, you will return 3 copies of each record.

Set the Primary Sorting and Grouping level to CopyNum and make sure you add a page break.

Add a bound text box to display the CopyName somewhere in your CopyNum group header or footer.

Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

Thank you. It works like a charm. However, the method has has one tiny drawback.

If my users want to print just the file copy, is there an easy way to automatically do that somehow outside the queries and tables? They don't have access to those other than to tell them to print only the record number for the selected copy in question?

Thanks again for the help.

 

[dhookom]

You can place a criteria in the report's record source query to open the report with a where condition.
Perhaps min and max copy number text boxes on a form and code to open the report like:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.txtMinCopy) Then
   strWHere = strWhere & " And CopyNum >=" & Me.txtMinCopy
End If

If Not IsNull(Me.txtMaxCopy) Then
   strWHere = strWhere & " And CopyNum <=" & Me.txtMaxCopy
End If
 
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

I appreciate all the help you have given me so far. I have one last question relative to this project.

I used [blue]faq703-2307[/blue] to establish page numbering for my reports and it's real close to what I need.

For each staff member I have, I get the necesasry three copies, but the first copy of the first report says 1 of 3. Is there a simple way to modify the code slightly to change it to print 1 of 1 for the File Copy, 1 of 1 for the External Copy, etc.? Or, is there a simple way to modify [blue]tblCopies[/blue] to do the task?

 

[dhookom]

Can you provide a sample of what is getting printed now and what you would like to see printed? I think you just need to add a "/3" or subtract something somewhere.

Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

Actually, I figured it out with a little help from my supervisor. He suggested adding another grouping on CopyName, and it worked.

Thank you again for the assistance with this project.