I have created a database that seems to be coming along nicely.
However I have came across a stumbling block. Hence why I'm here
I would like to be able to produce reports from one of my tables. Simple enough. However I would like the user to select from a dropdown or something to that effect. The soecific information on that report.
Hopefully this example will make sense:
If you have a database for all students in a school. And you only want a report from the students within "Maths". Now I know I could use a query, but I need the user to be able to select what the filter is.
so use a filter
I'd suggest he best way to handle this is to have a parameters form with all your relevant filter/selection criteria on (eg class, age, gender / whatever), then pull thiose filters through ontot he report.
base the report on the relevant query
then apply filters when you run the report.. havea look at the docmd.openreport function....
set the filters for the report and then on a button trigger the report to open with the required filter to limit data accordingly. the wizard behind the button should get you to open the repoprt, you just need to apply the filter correctly.
I think the basics of what you want is a custom dialog box (reporting dialog) that has a combo box on it that allows the user to select Maths. Then when the user click OK, you code the openreport action to open the report with a filter based on the combo box selection.
so create a form so that a user can select what parameters they want
on that form put list or combo box(es) populated by the relevant table(s) so that the user can select what parameter(s) they require
put a button on the form, set that to open the required report
select the button, look at the properties and change the code/build event to read something like....
'build our where clause
Dim strWhere As String 'defien a variable to hold our where clause
If <nameofparameter1control> > 0 Then 'has the user selected a parameter 1?
strWhere = "Manu_ID=" & <nameofparameter1control>
If <nameofparameter2control> > 0 Then 'has the user selected a parameter 2?
If Len(strWhere) > 0 Then 'have we already got a manufacturer where clause?
strWhere = strWhere & " and " 'if so add AND to make the where clasue make sense
strWhere = strWhere & "Prod_Type=" & <nameofparameter2control>
Dim stDocName As String
place this before the
Dim stDocName As String
note rather than use a different query for the report (which we could by supplying the third parameter in docmd.openreport, we instead supply the fourth parameter, a where clause without the "where"
ie in SQL we would use
select blah from mytable where Manu_ID = x
, so the where clause would
"be where Manu_ID = x"
here we supply
Manu_ID = x
Modify the openreport line to read..
DoCmd.OpenReport stDocName, acPreview, , strWhere
save the form
open the form select the parameters as required