Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

MS Access: Print a report with number of copy printed on the report

This database will be used in MS Access 2000.  One task for the database is to fill in the form QM_Nonconforming_f then print 1 to 50 labels from the report QM_Nonconforming_r depending on the number entered in the TotalLoadsTagged field.   I have tried to follow instructions for macros and modules and I cannot get it to work properly.  I got it so mixed up that I started over with a fresh copy of the db.

I have a simpified test copy at www.cjkurtz.com/test/qmis.htm.

Here is what I want to do.

1. Fill in the form QM_Nonconforming_f
2. Click a button that will
   a. save the record
   b. print the record as QM_Nonconforming_r (without previewing the report)
   c. and when printing the form it should count and print the copy number on the record
       for example: it prints Load (NUMBER) of 10 Loads.  10 is read from the form field:
             TotalTaggedLoads      
       and 10 reports are printed with 1of 10, 2 of 10, etc. printed on the reports.
   d.  I will also send one copy of an email, but I have figured that out
   e. display a new blank form

In my various attempts using macros with openreport and printout, I can't seem to select the correct record, or output the correct number of copies reading the quantity from TotalTaggedLoads field from the form.
At one point in a module I was able to printout the correct number of copies but it also printed out the form.
I have not been able to come close to having the correct number of copies print out.  I tried using the page function, but that just gave me 1of 10 on all the copies.
   
This shouldn't be that hard but I have spent hours on this and I am finally asking for help.  Thanks.

0
cjkurtz
Asked:
cjkurtz
  • 6
  • 2
1 Solution
 
BadotzCommented:
Perhaps a For...Next loop from 1 to TotalTaggedLoads in the code for the form? And a variable you can set to "1 of n", "2 of n", etc. within the report?

0
 
BadotzCommented:
If you do not have a module in your database, create one. I call mine "mod_Main" in this example.

Add this to the new module:

Public nof As Integer ' n of
Public nof_max As Integer ' count

Public Sub Print_Report(rptName, counter)
   
    nof_max = counter
    For nof = 1 To nof_max
       
        DoCmd.OpenReport rptName, acViewNormal
   
    Next

End Sub

Open the report you wish to count in design view and add a textbox where you want the message to display. Give the textbox a name you can remember. I use the name 'txtNOF' in my example.

While the report is open in design view, add code to the report header On Print event (code builder):

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
   
    Me.txtNOF = mod_Main.nof & " of " & mod_Main.nof_max

End Sub

Finally, in your input report, add this:

Private Sub cmdPrint_Click()
   
    mod_Main.Print_Report Me.TotalTaggedLoads

End Sub

Save all open elements and see if this does the trick. If not, let me know.
0
 
BadotzCommented:
Sorry, this:

Finally, in your input report, add this:

should be:

Finally, in your input FORM, add this:
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cjkurtzAuthor Commented:
Badotz, Thanks, for your help so far.

I am getting a compile error when I click the Print btton on the form  to run cmd_Print.

I've uploaded the db to qmis 2.mdb on www.cjkurtz.com/test/qmis.htm.

This is what I did:

Created Mod_Main and added this code. I changed rptName to QM_Nonconforming_r.

Public nof As Integer ' n of
Public nof_max As Integer ' count

Public Sub Print_Report(QM_Nonconforming_r, counter)
   
    nof_max = counter
    For nof = 1 To nof_max
       
        DoCmd.OpenReport QM_Nonconforming_r, acViewNormal
   
    Next

End Sub

In the report I added a textbox 'txtNOF' .

I added a report header and added code to the On Print event:

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
   
    Me.txtNOF = mod_Main.nof & " of " & mod_Main.nof_max

End Sub

I made a button on the form to run the event procedure cmd_Print: This is where I get the complie error: Aurgument not optional

Private Sub cmdPrint_Click()
   
    Mod_Main.Print_Report Me.[TotalLoadsTagged]

End Sub

Do I need code to save the form before printing? and to open a new form after printing?

Thanks
0
 
BadotzCommented:
My bad - change this:

Public Sub Print_Report(QM_Nonconforming_r, counter)

to this:

Public Sub Print_Report(counter)

and this:

DoCmd.OpenReport QM_Nonconforming_r, acViewNormal

to this:

DoCmd.OpenReport "QM_Nonconforming_r", acViewNormal
0
 
cjkurtzAuthor Commented:
Badotz,

It is getting closer.  It was not printing out the current record in the Form, it was printing a blank if there were no previously saved records.  I had to save the currect record in the form, so I added that code.   I also added code to open a new form after printing.

However, when there is more than one record,  it prints all the records not just the record for the currently open form.  

Also, on the printed record I don't get the Number of the COPY being printed.  How do I get that COUNTER number to the report text box txtNOF ?

I really appreciate this help.  Great learning experience, thanks.
0
 
BadotzCommented:
>>It was not printing out the current record in the Form

Not knowing what the report does, I did not know this was a requirement

>>However, when there is more than one record,  it prints all the records

See above comment

>>Also, on the printed record I don't get the Number of the COPY being printed

Not sure why, if you used the same code as that which I provided.

    nof_max = counter
    For nof = 1 To nof_max
       
        DoCmd.OpenReport rptName, acViewNormal
   
    Next

>nof< and >nof_max< are global vars declared in >mod_Main<

The loop sets >nof_max< to the value of the >counter< parameter from your input form, which should be the value of >Me.TotalTaggedLoads<

Finally, the report runs and for each report printed, it stuffs the value into the textbox

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
   
    Me.txtNOF = mod_Main.nof & " of " & mod_Main.nof_max

End Sub

which outputs >1 of 10< on the first report, >2 of 10< on the second report, etc.
0
 
BadotzCommented:
No worries - glad to help.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now