I have a database in which I keep track of audit projects, the sites visted during each project and the staff members assigned to each project. Each of the three entities are indexed on their Project ID, Site ID and Staff ID, numbered consecutively. So I could have a ProjectID #4, a SiteID #4 and a StaffID #4.
I also have a NOTES table in which I keep notes on each of the entities. The NoteType field tells me what kind of note it is (1=Staff, 2=Site, 3=Project) and the EntityID tells which of those various entity types the note is for. Thus NoteType = 3, EntityID = 4 is for Project #4, whereas NoteType = 1, EntityID = 4 is for Staff Member #4.
In the reports for each of these entities, I have a NOTES subreport which displays the associated notes for the entity displayed in the main report. I need to filter the NOTES subreport on NoteType so that StaffMember #4's note does not get displayed for Project #4.
When I open the report in DESIGN mode I can set the subreport's filter manually and everything works fine at runtime. But I cannot set the filter at runtime. I get the following error:
Here is my code:
Why can I do this in DESIGN mode, but not with VBA? What are my options?
I have seen several similar threads that get a lot of hits, but not a lot of responses. I assume then that this is not uncommon, but also not so easy.
Thanks in advance.
I also have a NOTES table in which I keep notes on each of the entities. The NoteType field tells me what kind of note it is (1=Staff, 2=Site, 3=Project) and the EntityID tells which of those various entity types the note is for. Thus NoteType = 3, EntityID = 4 is for Project #4, whereas NoteType = 1, EntityID = 4 is for Staff Member #4.
In the reports for each of these entities, I have a NOTES subreport which displays the associated notes for the entity displayed in the main report. I need to filter the NOTES subreport on NoteType so that StaffMember #4's note does not get displayed for Project #4.
When I open the report in DESIGN mode I can set the subreport's filter manually and everything works fine at runtime. But I cannot set the filter at runtime. I get the following error:
Here is my code:
Code:
Me!sbrNotes.Form.Filter = "NoteType = 3"Why can I do this in DESIGN mode, but not with VBA? What are my options?
I have seen several similar threads that get a lot of hits, but not a lot of responses. I assume then that this is not uncommon, but also not so easy.
Thanks in advance.
