Report formatting question--Access 2007

[mishmish3000]

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.

what id like to see.jpg

what I see.jpg

[HansV]

Check what you see in Print Preview. Report View is not always accurate in Access 2007.

[mishmish3000]

I did... It still does the same thing. Any ideas?

[HansV]

You could use VBA code in the report's module:

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

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.

[mishmish3000]

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:

what i see with VB code.jpg

[HansV]

Could you post a stripped down, compacted and zipped copy of the database?

[mishmish3000]

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...

design view subreport.jpg

[HansV]

Can't you delete the real patient data (from a copy of the database, of course!) and enter a few dummy records?

[mishmish3000]

OK, I'll work on that! Get back to you soon...

[mishmish3000]

Here's the database with pretend data...

[HansV]

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.

[mishmish3000]

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.

Copy 120 Day Report DB.ZIP

[HansV]

Here is something very strange: when I open the report in your database, I see this:

x224.png

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.

[mishmish3000]

i will download and install SP2... thanks! Then I'll see...

[mishmish3000]

I have Sp2... the report still does its weird thing for me... sigh. am I doomed?

[HansV]

Have you tried the report in the stripped down copy of the database that you posted?

[mishmish3000]

Yes...

[HansV]

See if the report (preferably in the "real" database) behaves the same on another computer...

[mishmish3000]

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!

[HansV]

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.