On Format in Report

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

On Format in Report

Post by Leesha »

Hi,
I'm using the following code in the on format event of a report. The code works fine provided all rows in the query do not have information in the KeyConstituents field. My issue stems from the fact that some records have data in this field and others do not. The field is not visible for all rows if any of the KeyConstituent fields are blank. Is it possible to have only the KeyConstituent fields blank for the records with no info and the records with info would show the data. There is other info in each record that is used in each report. As a result each record needs to populate. I'm just trying to eliminate the fields with no data.

Thanks!
Leesha


If IsNull(Me.KeyConstituents) Then
Me.KeyConstituents.Visible = False
End If

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: On Format in Report

Post by HansV »

You must place this code in the On Format event of the section of the report that contains the KeyConstituents control - presumably the Detail section.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: On Format in Report

Post by Leesha »

Hi Hans,
That is where I have it. However it does not seem able to differentiate between the records that have data in that field and those that do not.
Leesha

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: On Format in Report

Post by HansV »

Hmmm... could you attach a copy of the database?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: On Format in Report

Post by Leesha »

Arrrrh! I just did a stripped down version of the DB and created a new report as it was just easier and the code worked. So there is something wrong with the original report. Now to just figure it out :) At least I know it's possible!
Leesha

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: On Format in Report

Post by HansV »

Good luck! :crossfingers:
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: On Format in Report

Post by Leesha »

Hi Hans,
I concede defeat. Now my issue is that the zip file is too big even though there is very little in it. I uploaded it to Drop Box. Here is the link https://www.dropbox.com/s/sxnl0968blaoq ... t.zip?dl=0
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: On Format in Report

Post by HansV »

I initially got an error message. After removing an event procedure that wasn't used, it appears to work correctly:

S1354.png

Database attached.

FormatReport.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: On Format in Report

Post by Leesha »

Hi Hans,
I downloaded the database and ran the report and its doing the same thing it did to me before. I've attached a copy of the report. Key Constituents doesn't show at all??? I didn't make any changes.
Leesha
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: On Format in Report

Post by HansV »

Aargh - I should have seen that. The code only sets Visible to False, never to True. I didn't notice because the report opens in Report View by default; the code works in Print Preview only.
Try this version:

Code: Select all

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.KeyConstituents.Visible = Not IsNull(Me.KeyConstituents)
    Me.Ingredients.Visible = Not IsNull(Me.Ingredients)
End Sub
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: On Format in Report

Post by Leesha »

Thanks Hans! That did the trick! It's also an approach I've not used before. LOVE to learn new methods!
Have a great day!
Leesha