I am making a report that displays a set of records that have been filtered for certain attributes.
On this report I have multiple counters, 2 of which are causing a big problem.
You see, one of the filters has to be on the "revision" ("p*") of a document and the report is only supposed to display a certain type, but the "total documents" counter has to display the number of records that is returned without that filter.
So if i have 3 documents type "TQ"...
If 2 of them are revision "p0"
And 1 of them is revision "d0"
The total is 3
The total revision P is 2
And the report must only display the 2....
There is a way of doing this by setting Rowsource of a listbox to select count(*) from query; and setting the filter in the report not the query. Unfortunatly I dont want to display a listbox on the report.
So does anyone know of a way to solve this? Perhaps using a listbox as invisible and somhow using the data in it (i havent found a way yet)?
That and im not sure how it will need to look like.
I dont want the number itself invisible, its just that when i display a listbox with a transparent border it doesent like to be center alined and when you print the report it still prints the cell box (dont know how to get rid of that).
If i can get the total in a texbox as raw data (ie a number) that would be ideal.
I may be getting the wrong end of this, but have you thought about setting a query based on the table that the form is base on, filtering the query so that it only allows the "p0" , then basing the report on the query?
Function GetDocTypeCount() As Long
Dim qDf As QueryDef, rs As Recordset
Set qDf = CurrentDb.QueryDefs("qCountDocType")
qDf.Parameters("DocType") = "TQ"
Set rs = qDf.OpenRecordset
GetDocTypeCount = rs.Fields("CountOfDocs")
Set rs = Nothing
The query "qCountDocType" refered to above would be something like this:
PARAMETERS [DocType] String;
SELECT Count(*) AS CountOfDocs
Then insert a text box in your report. Set the Control Source to "= GetDocTypeCount()"
So basically, base your report on the query that provides the data you need, and use functions to get any extra info not readily available in the main recordsource.
I think that almost works, expect when i open the report i get a User-defined type not defined error on the function name.
I will try and figure out the error, but im a newbie in code so it will help a lot if you could suggest something
Ryker: yea thats not what i meant , i did that and it does thesame thing because texboxes calculate from the report not the query, and if you set the filter on report all it does allow the query to be unfiltered.
I just spent an hour with an experienced access programmer, and hes just as confused as i am lol.
All functions are returning #error.
Ive tried using other functions with recordset, this is just not working. I feel like im arm wrestling with Bill Gates here.
The syntax is correct, it just doesent want to display the value in this report.
Infact it doesent want to do anything other than manipulate the data its showing.
The ONLY thing thats wrking is a separate SQL statement in a listbox. But when you print that it shows an indent effect around the cell, not to mention the fact that it doesent want to justify left right or center.
Now I'm thinking of 2 solutions:
1: more complex. is by setting the list column name to the value in the cell and then resizing the listbox to just show the column title (so it doesent print the cell border)
2: creating 4 ceparate white text boxes and physically covering the borders.
If you are getting #error it means Access cannot evaluate the Control Source setting. Make sure the function is globally available. If you just dump the function inside the report's code it will only be visible within the report itself and cannot be evaluated for Control Source purposes. Dump it into a new or existing module. To confirm that it is globally visible press Ctrl-G to load the immediate window, and type in ?GetDocTypeCount(). If it returns a value you should have no problems using it as a control source.