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
On Format in Report
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: On Format in Report
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: On Format in Report
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
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
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: On Format in Report
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
Leesha
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: On Format in Report
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
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
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: On Format in Report
I initially got an error message. After removing an event procedure that wasn't used, it appears to work correctly:
Database attached.
Database attached.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: On Format in Report
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
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.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: On Format in Report
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:
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: On Format in Report
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
Have a great day!
Leesha