Can I delete records from a recordset before printing an Access 2007 report?
I am developing an Access 2007 "project" (.adp) as a front-end to data in a SQL Server 2005 Express database.
I will try to describe what I am trying to achieve and the problem I am having doing it.
In a particular Access Report, I allow the operators to tick (or untick) a group of 25 months, to choose which periods sales figures are to be included in a couple of columns in the report. The columns can therefore contain the combined figures from a group of selected periods.
I also allow them to tick a box that allows "all zero" rows with no valid sales figures for the selected periods to be omitted from the report.
In the Report_Open procedure I build-up a T-SQL "SELECT ... FROM .. ORDER BY ... WHERE ..." string to be used as the Report's .RecordSource parameter, which also includes a couple of "LEFT OUTER JOINs" to other "Views" stored in the SQL Server database. I also set a few of the Report's Control's .ControlSources
based on the earlier choices made by the operator using the tick boxes.
This all seems to work OK, except when I try to remove the "all-zero" rows from my report. The reason for this is that in the "SELECT" part of my Report.RecordSource string I use some aliases such as "ISNULL(OWNSHOP.Month1SlsQ
Is there a way (presumably still in the Report_Open procedure - but I will take advice to do it elsewhere), where rows from the recordset compiled for the report can be removed, before any output is generated, if some values are zero using the "alias" field names? This would also need to protect the integrity of some "totals" Controls that I have in the Report's footer section that have also been established dynamically in Report_Open (although if I am removing only "all-zero" lines from the report it should not matter if the totals are still based on the original set of records).
What would the VBA syntax look like for deleting records from such a recordset? I am hoping for an expression along the lines of "DELETE FROM ... WHERE OWNMTH1QNT = 0 AND OWNMTH3QNT = 0 ...."
I will also happily receive any suggestions for achieving my goal by any other strategy.
Many thanks in advance.
I will try to describe what I am trying to achieve and the problem I am having doing it.
In a particular Access Report, I allow the operators to tick (or untick) a group of 25 months, to choose which periods sales figures are to be included in a couple of columns in the report. The columns can therefore contain the combined figures from a group of selected periods.
I also allow them to tick a box that allows "all zero" rows with no valid sales figures for the selected periods to be omitted from the report.
In the Report_Open procedure I build-up a T-SQL "SELECT ... FROM .. ORDER BY ... WHERE ..." string to be used as the Report's .RecordSource parameter, which also includes a couple of "LEFT OUTER JOINs" to other "Views" stored in the SQL Server database. I also set a few of the Report's Control's .ControlSources
based on the earlier choices made by the operator using the tick boxes.
This all seems to work OK, except when I try to remove the "all-zero" rows from my report. The reason for this is that in the "SELECT" part of my Report.RecordSource string I use some aliases such as "ISNULL(OWNSHOP.Month1SlsQ
Is there a way (presumably still in the Report_Open procedure - but I will take advice to do it elsewhere), where rows from the recordset compiled for the report can be removed, before any output is generated, if some values are zero using the "alias" field names? This would also need to protect the integrity of some "totals" Controls that I have in the Report's footer section that have also been established dynamically in Report_Open (although if I am removing only "all-zero" lines from the report it should not matter if the totals are still based on the original set of records).
What would the VBA syntax look like for deleting records from such a recordset? I am hoping for an expression along the lines of "DELETE FROM ... WHERE OWNMTH1QNT = 0 AND OWNMTH3QNT = 0 ...."
I will also happily receive any suggestions for achieving my goal by any other strategy.
Many thanks in advance.
...then something like this might help on the Detail_Format event:
If me.Sales=0 then
me.section(0).visible=Fals
else
me.section(0).visible=True
End if
If me.Sales=0 then
me.section(0).visible=Fals
else
me.section(0).visible=True
End if
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
colinasad:
You can apply a filter to a recordset that returns a recordset a subset of the initial recordset. Simple to code and very flexible. See examples at:
http://msdn.microsoft.com/en-us/library/ff837300.aspx
PS: I belive the line in Microsoft's example that reads:
Set rest = dbs.OpenRecordset(_
should be
Set rst = dbs.OpenRecordset(_
Regards,
AielloJ
You can apply a filter to a recordset that returns a recordset a subset of the initial recordset. Simple to code and very flexible. See examples at:
http://msdn.microsoft.com/en-us/library/ff837300.aspx
PS: I belive the line in Microsoft's example that reads:
Set rest = dbs.OpenRecordset(_
should be
Set rst = dbs.OpenRecordset(_
Regards,
AielloJ
You can do it this way or do the same thing in a query.
I would go with the query approach because then you could use the same query for a form, or just to see the results directly.
For example, this query will only display records where none of the Val fields is Zero:
(In other words, if any Valn value is zero, the record will not display)
SELECT ID, FName, Val1, Val2, Val3, IIf(([Val1]=0) Or ([Val2]=0) Or ([Val3]=0),-1,0) AS Hide
FROM YourTable
WHERE (((IIf(([Val1]=0) Or ([Val2]=0) Or ([Val3]=0),-1,0))=False));
Now you can use this query as the source for the report (or a form or simply run the query to see the results directly)
I an sure you can see where I am going with this...
So you can adapt this basic concept to your distinct situation
;-)
JeffCoachman
Database162.mdb
I would go with the query approach because then you could use the same query for a form, or just to see the results directly.
For example, this query will only display records where none of the Val fields is Zero:
(In other words, if any Valn value is zero, the record will not display)
SELECT ID, FName, Val1, Val2, Val3, IIf(([Val1]=0) Or ([Val2]=0) Or ([Val3]=0),-1,0) AS Hide
FROM YourTable
WHERE (((IIf(([Val1]=0) Or ([Val2]=0) Or ([Val3]=0),-1,0))=False));
Now you can use this query as the source for the report (or a form or simply run the query to see the results directly)
I an sure you can see where I am going with this...
So you can adapt this basic concept to your distinct situation
;-)
JeffCoachman
Database162.mdb
ASKER
Thanks for your suggestions, but I think I am going to stick with my own solution.
The problem is that the Access Report itself consists of only a few columns. However, the Form that the operator sees before the Report is launched offers many (25) periods that can be ticked for inclusion in those columns.
In the Report_Open procedure I build up (Report's and) the columns' underlying "DataSource" strings based on the periods that have been ticked in preceding Form.
The other option I offer the operator in the preceding Form is to "Ignore all-zero" lines. (This means ignoring lines that have ALL zero values rather than ANY zero values.)
This seems easier to do in the Report's "Detail_Format" procdure, using the Report's columns control names, rather than making my "RecordSoutce"/"DataSource
Again, many thanks for your time and suggestions.
The problem is that the Access Report itself consists of only a few columns. However, the Form that the operator sees before the Report is launched offers many (25) periods that can be ticked for inclusion in those columns.
In the Report_Open procedure I build up (Report's and) the columns' underlying "DataSource" strings based on the periods that have been ticked in preceding Form.
The other option I offer the operator in the preceding Form is to "Ignore all-zero" lines. (This means ignoring lines that have ALL zero values rather than ANY zero values.)
This seems easier to do in the Report's "Detail_Format" procdure, using the Report's columns control names, rather than making my "RecordSoutce"/"DataSource
Again, many thanks for your time and suggestions.
ASKER
Please see my previous comment for why I chose my own solution to this question.
ASKER
(It's amazing how things come to you when you have to think through a problem in order to ask a question.)
I will investigate making the Report's "Detail.Visible" value False if all my "sales" Control text boxes are zero. As I mentioned in my original question, it won't matter if the omitted records are included in the totals at the end of the report because their values are all zero anyway.
I will check that out. In the meantime any other suggestions are still welcom.