In Access 2007, I want a header to appear for all records even if there's no data for the detail section of my report. I only have one field in the detail section, named Action & I tried the following code, but it didn't make the header appear for all records.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If IsNull([Action]) Then Me![GroupHeader1].Visible = True End If End Sub
I don't even think you have to do any special code. Check the header properties. Make sure the "Can Shrink" property is set to No. If that doesn't work, I would simply add an clear / transparent object in the header so that it always stay open,.
I am fine Leslie. Thanks for asking. I've just been really busy at work lately.
In any event, so are you saying that in the case where there is no data in the detail section, the header disappears? Sort of dynamically displaying and hiding the header based on the detail section?
If you want to see group headers for groups without details, you need to build a special query for this.
Let's imagine you want products grouped by categories, making sure every category is listed even if there are no products.
SELECT Products.*, Categories.CategoryName FROM Categories LEFT JOIN Products ON Categories.CatID = Products.CatID
By using LEFT JOIN, you will have one record for every category, with all the "product" fields being Null if there are no products. With such a Record Source, you will have all the headers for the group "CategoryName", and you can use code to determine what to do with a detail section having Nulls everywhere.
"If you want to see group headers for groups without details, you need to build a special query for this."
Well, I don't think that is always the case. As a test, I have an Employee table ... which besides the EmpID, it has an EmpIDRptsToID ... which is another Employee in the table of course ... the 'self join' idea. So, lets say that a 'supervisor' does not have anyone reporting to them at this time. In the report, if I GroupBy RptsToID/Name ... then, I get a Header for that 'supervisor', but there are no detail records. Of course, that employee (supervisor) will may show up under a header with *no group name* ... as an employee with no reports to.
So ... that is the test I referenced above. There is only one table here ... and the Header with no details (employees reporting to this supervisor) shows up with no problem.
> lets say that a 'supervisor' does not have anyone reporting to them at this time.
That would mean that no record has the number 11 (the 'supervisor' ID) in the field EmpIDPrtsToID. If you group the table by that field, there will be no group 11, hence no group header for that person, even if he/she is a potential supervisor.
If your report is based on a query, it's different, of course: Employees as supervisors left joined on employees as subordinates on supervisor's ID equal to subordinate's reports to ID...
I had three tables linked together for the recordsource of the report. I'm not sure why it wouldn't put on header, but I solved it by using a subreport instead.
