Can Anyone please help with this?? (1 Viewer)

nj2la96 · Feb 7, 2016

I am creating a report that has command buttons (i.e Save to PDF, Print, Email etc) on the report header section, but these buttons can only be viewed when the report is shown on Report View and not Print Preview.

However, on the same report, I have a couple of fields in the detail section that I want to hide, namely the quantity and unit price fields which should be hidden if the quantity is equal to 1.

Here is my problem. If I make the default be Report view, I see my buttons on the top but it does not hide my quantity and unit price fields. And, if I change the view to Print Preview, I can NOT see the buttons on the top but my quantity and unit price fields are now hidden.

Does anyone please have an idea how I can have both the buttons on top of my report as well as hide the fields in the detail section??

sneuberg · Feb 7, 2016

How are the quantity and unit price being hidden. I'm guessing that in some event you test to see if the quantity is 1 and then hide the fields. What event?

Could you upload your database or at least the part with the report and supporting objects?

nj2la96 · Feb 7, 2016

Thanks for the reply.

On the OnFormat event of the detail section, I have added the following code:

If Me!Quantity = 1 Then
Me!Quantity.Visible = False
Me!UnitPrice.Visible = False
End If

I have attached a portion of the db, as you can see from the report, the 2nd line has quantity of 1 and its quantity and unit price should be hidden. And it does work if you change the view to Print Preview.

I hope it helps.

sneuberg · Feb 7, 2016

The attached database is your database with the quantity and unit price hidden in report view for quantity equal to one. Well kind of hidden. What I did is apply conditional formatting to the fields for the expression [Quantity] = 1, setting the background color and forecolor to the same color. I tried to get it close to the existing background but you can still see a faint outline of the textboxes. I'm sure if I spent enough time on it I could hide them completely.

I suspect there's a better way of doing this. I'll keep thinking about this and maybe some other forum member has a solution. If not maybe you can get by with this.

bob fitz · Feb 7, 2016

Another way to achieve the desired result would be to use a calculated field in a query and use this query as the record source for the report:

Code:

SELECT qryInvoice.ClientID, IIf([quantity]=1,Null,[quantity]) AS Qty, qryInvoice.ClientName, qryInvoice.Address1, qryInvoice.Address2, qryInvoice.Address3, qryInvoice.City, qryInvoice.[Post Code], qryInvoice.Attention, qryInvoice.Type, qryInvoice.InvoiceNumber, qryInvoice.InvoiceDate, qryInvoice.BillingPeriod, qryInvoice.ReportOrder, qryInvoice.TransactionDetailID, qryInvoice.TransactionType, qryInvoice.ShortDescription, qryInvoice.UnitPrice, qryInvoice.InvoiceTotal, qryInvoice.CummulativeBalance, qryInvoice.BeginningBalance FROM qryInvoice;

On the report you would need to then change the control source of the control called "Quanity" to "Qty".
You would also need to change the control source of the control called "text117" to: =Sum([qty]*[UnitPrice])

nj2la96 · Feb 7, 2016

sneuberg,

Thank you. I tried your idea but the invoice still shows, ever so slightly, the fields we don't want.

Bob,

I haven't tried your idea yet but the last suggestion of changing the formula in text117 seems like it would be a problem if the quantity is 1, wouldn't it?? Because from your suggestion, if the quantity is 1, then Qty would be null

bob fitz · Feb 7, 2016

Perhaps this can be over come by using the Nz() function in the expression for text117. Maybe something like: =Sum(Nz([qty],0)*[UnitPrice])

nj2la96 · Feb 7, 2016

Hmm....seems a bit messy. I can't believe something so simple could cause so much difficulty. I'll try and see if I can get other ideas. Thanks.

sneuberg · Feb 7, 2016

If your only problem with the conditional formatting solution is that it wasn't perfectly hidden, then the attached database will show you that this can be done. The trick to hiding it was determining what the background color was which is list in the properties as Background 1, Darker 5%. I found that if you click on the three dots ... and More Colors it tells you the current RGB values. All three are 242 for Background 1, Darker 5%. So in conditional formatting you can choose More Colors and set the RGB value to 242 to get a perfect match.

Personally I think bob fitz's solution isn't excessively messy considering the bizarre requirement and is the better solution. Someday this feature may be considered a bug

and finding the cause with his solution would be easier than it being hidden away in conditional formatting.

nj2la96 · Feb 8, 2016

Thank you for your response. Your sample database looks a lot better. Thank you, I will try this out now. Thanks for all your help.

The_Doc_Man · Feb 8, 2016

Something jumped out at me. The buttons showing or not showing in Print Preview is nothing, but the fields disappearing in one view but not the other is indicative of a little-remembered problem.

When you do code that involves OnFormat, that ONLY fires in Report View. When you do code that involves OnPrint, that ONLY fires in Print-based modes (Print Preview, Print View). In essence, if you have a section (whether Detail, Group Header, or Group Footer) that needs to do some testing of a field and do something different for some values of that field, you either need to duplicate the code or you need to write a subroutine to do the conditional stuff and call it from both a section_OnFormat and a section_OnPrint event routine.

sneuberg · Feb 8, 2016

The_Doc_Man said:
Something jumped out at me. The buttons showing or not showing in Print Preview is nothing, but the fields disappearing in one view but not the other is indicative of a little-remembered problem.

When you do code that involves OnFormat, that ONLY fires in Report View. When you do code that involves OnPrint, that ONLY fires in Print-based modes (Print Preview, Print View). In essence, if you have a section (whether Detail, Group Header, or Group Footer) that needs to do some testing of a field and do something different for some values of that field, you either need to duplicate the code or you need to write a subroutine to do the conditional stuff and call it from both a section_OnFormat and a section_OnPrint event routine.

I test this with the event code as follows:

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If Me!Quantity = 1 Then
    Me!Quantity.Visible = False
    Me!UnitPrice.Visible = False
  End If
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me!Quantity = 1 Then
    Me!Quantity.Visible = False
    Me!UnitPrice.Visible = False
  End If

End Sub

and it doesn't have the desired effect. The controls are still visible in report view. The Detail_Format doesn't seem to fire in report view. With the code like (msgboxes):

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    MsgBox "Detail_Format"
  If Me!Quantity = 1 Then
    Me!Quantity.Visible = False
    Me!UnitPrice.Visible = False
  End If
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
MsgBox "Detail_Print"
If Me!Quantity = 1 Then
    Me!Quantity.Visible = False
    Me!UnitPrice.Visible = False
  End If

End Sub

I can see that both of these fire twice when going from Report View to Print Preview.

nj2la96 · Feb 8, 2016

I concur with sneuberg.

The_Doc_Man · Feb 9, 2016

Search this forum or the Great Google Brain for the _Format and _Print events. You will find behavior such as I described. If Ac2013 has changed behavior, so be it, but it used to be as I said, and the events are still there in my work copy of Access 2013. I learned this little tidbit from this very forum.

If the events are not different, then ask yourself why a section has TWO events that are the same exact function: section_OnPrint and section_OnFormat. Look them up if you doubt me. And yes, the events CAN fire twice for the same section on the same report. When you debug that, check the 2nd event parameter, which is the count of times that given section has fired that event. The numbers should change.

As to controls not displaying in certain cases, please select one of the offending controls, then call up the property sheet, look at "Format" and (near the bottom) find the "DisplayWhen" property. That property toggles as a 3-way: Always, Print Only, Screen Only. That setting might cause the behavior regarding controls that are not visible when you expected them to be.

nj2la96 · Feb 10, 2016

Sorry Doc, we are not doubting you. We were just saying that it didn't work as you described. Granted, I am using Access 2016 so maybe things may have changed since, but I have tried putting the same code in the OnPrint and OnFormat and alternating the display when parameter and none of those work in Access 2016.

The_Doc_Man · Feb 10, 2016

My second comment about the "Display When" property should help for at least some of what you are doing.

And to be honest, the _Print/_Format behavior I described was a real pain-in-the-...toches when it was current behavior for earlier Access versions. If Ac2016 finally got rid of the dichotomous behavior, I wouldn't shed a single tear, trust me.

Can Anyone please help with this?? (1 Viewer)

nj2la96

Registered User.

sneuberg

AWF VIP

nj2la96

Registered User.

Attachments

sneuberg

AWF VIP

Attachments

bob fitz

AWF VIP

nj2la96

Registered User.

bob fitz

AWF VIP

nj2la96

Registered User.

sneuberg

AWF VIP

Attachments

nj2la96

Registered User.

The_Doc_Man

Immoderate Moderator

sneuberg

AWF VIP

nj2la96

Registered User.

The_Doc_Man

Immoderate Moderator

nj2la96

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread