Report formatting question--Access 2007
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Report formatting question--Access 2007
Greetings! I submitted a question yesterday about a report I'm developing in an Access 2007 db. It looks at individual patients (children) and their vaccinations. I was able to successfully create the report and subreport that shows each patient, the list of antigens (vaccinations), the count of antigen shots for each antigen, and the date of each shot. However, when I run the report, I'd like to hide where it shows duplicates for the count. I'll attach two images--one a screen shot of what the report and subreport look like now, and one what I would like to see. If I go to the subreport and say "no duplicates" in the properties for Antigen, it works like a charm. If I say "no duplicates" for the CountofAntigen, however, it doesn't work so well. Please see the images. Any ideas?? Sure appreciate it.
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
Check what you see in Print Preview. Report View is not always accurate in Access 2007.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
You could use VBA code in the report's module:
where CountOfAntigen is the name of the text box containing the # of shots, and GroupHeader0 is the name of the group header for the Antigen field.
This only works in Print Preview for me, not in Report View.
Code: Select all
Private lngCount As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
lngCount = lngCount + 1
Me.CountOfAntigen.Visible = (lngCount = 1)
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
lngCount = 0
End Sub
This only works in Print Preview for me, not in Report View.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Report formatting question--Access 2007
Option Compare Database
Private lngCount As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
lngCount = lngCount + 1
Me.CountOfANTIGEN.Visible = (lngCount = 1)
End Sub
Private Sub GroupHeaderAntigen_Format(Cancel As Integer, FormatCount As Integer)
lngCount = 0
End Sub
So the name of the header is Antigen... but when I did this, and reran the report, and looked at it in print preview, I saw the following:
Private lngCount As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
lngCount = lngCount + 1
Me.CountOfANTIGEN.Visible = (lngCount = 1)
End Sub
Private Sub GroupHeaderAntigen_Format(Cancel As Integer, FormatCount As Integer)
lngCount = 0
End Sub
So the name of the header is Antigen... but when I did this, and reran the report, and looked at it in print preview, I saw the following:
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
Could you post a stripped down, compacted and zipped copy of the database?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Report formatting question--Access 2007
I wish I could but I really can't since it has patient data in it... sorry!
Did I misname the group heading in the code?
This is a screen shot of the subreport in design view...
Did I misname the group heading in the code?
This is a screen shot of the subreport in design view...
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
Can't you delete the real patient data (from a copy of the database, of course!) and enter a few dummy records?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
Er, the attachment is not there...
Remember, you can't attach a database, you have to zip it and attach the zip file. File size at most 256 KB.
Remember, you can't attach a database, you have to zip it and attach the zip file. File size at most 256 KB.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Report formatting question--Access 2007
OK, it was too big, so I removed the three image files I was using in it, rezipped it and it should be here now.
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
Here is something very strange: when I open the report in your database, I see this:
The # of shots is displayed only once per antigen. Even stranger, if I remove the code but leave Hide Duplicates set to Yes, it still looks like that!
Is your version of Access 2007 up-to-date? I have SP2.
The # of shots is displayed only once per antigen. Even stranger, if I remove the code but leave Hide Duplicates set to Yes, it still looks like that!
Is your version of Access 2007 up-to-date? I have SP2.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Report formatting question--Access 2007
i will download and install SP2... thanks! Then I'll see...
Anne
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Report formatting question--Access 2007
I have Sp2... the report still does its weird thing for me... sigh. am I doomed?
Anne
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
Have you tried the report in the stripped down copy of the database that you posted?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
See if the report (preferably in the "real" database) behaves the same on another computer...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Report formatting question--Access 2007
Yes, it most certainly does. I took the "real" db to someone else's pc and saw... no numbers next to the Antigen names, where # of shots used to show up.
Maybe am I not putting the code in the right place? Could someone look at that? when you run the report, you'll see the numbers, and they'll look correct, but then when you go to print preview, they're gone. Help!
Maybe am I not putting the code in the right place? Could someone look at that? when you run the report, you'll see the numbers, and they'll look correct, but then when you go to print preview, they're gone. Help!
Anne
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report formatting question--Access 2007
I notice one problem with the code in your subreport: the name of the group header is GroupHeader0, not GroupHeaderAntigen, so you should have used the code as posted by me.
Best wishes,
Hans
Hans