Setting visibility on Form Fields

[Eljefegeneo]

I have a report that prints my monthly invoices with the following code:

  Dim iCount As Integer
  Dim rpt As String
  Dim cond As String
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
   
  rpt = "rptInvoiceToBeMailed"
   
   
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("qryInvoicePrintClientCopy")
   
        With rs
                rs.MoveFirst
                
                Do While Not .EOF
                      cond = "[RecordID] = " & rs!RecordID
                      'Cannot open in acViewNormal as page length is too long.
                      DoCmd.OpenReport rpt, acViewPreview, , cond
                      PrintNew   'my special module to print
                      DoCmd.Close acReport, rpt                                       
                      .MoveNext
                Loop
        End With
     
        rs.Close

This works fine, of course. However, there are certain invoices that inform the client that the amount due will be debited from their bank on a certain date, known as an ACH transfer. Thus the bottom half of the invoice is not necessary, where a regular invoice says: "Please cut and return this portion with your payment", etc., the lower third of the invoice.

So I made a sub called VisibilityText were if the ACH check box was true certain text would be false. Otherwise, if the payment is to be mailed, the bottom third of the report is to be visible.

That being said, I put the VisibilityText in the OnLoad Event. Is this the correct place or should it be in the OnCurrent event or another event? I have tested it on individual reports and a sample of two or three reports, but not wanting to print out the hundreds of invoices to test it, I just want to be sure I have put it in the correct event.

Thanks. And Happy Turkey Day!

 

[isladogs]

From your description, I would put it in the on format event of whichever report section it is in.

Why do you need to loop through a recordset for this as it will make it slower to run than necessary. Just filter the record source of the report to the records required e.g. With that query(?) and ensure each record prints on a new page. Job done .. and faster.

 

[Eljefegeneo]

There is no section on the report other than the main one and the header. So should it be OnLoad or OnCurrent, these seem to be the only two options for me?


thanks for the other advice, I'll take a look at that later.

 

[isladogs]

At the very least you have a detail section and probably PageHeader & PageFooter. Your control will be located in one of those. Use the Detail_Format event or whichever section is appropriate

 

[June7]

Must be in Format or Print event of section the control is in.

Alternatively, have a textbox with expression that conditionally displays the message.

 

[Eljefegeneo]

OK, put in the OnPrint event an works great. Thank you both.