help please with opening a specific report from a form

[chrisbarnett.werneth]

hi - i've created a database for printing school reports but at the
moment it i can only open a report which contains every pupil in the
year group.

i would like to be able to just print off individual reports when
required.

i have created a form based on a table [Basic Pupil Info] which lists
every child in the school. each child has a unique admin no [adm_no].

each year group has different requirements so have their own reports
and queries eg. [Parent Report - Year 7], [Parent Report Query - Year
7] and again the [adm_no] is in this query.

i would like to be able to click on a control next to the pupils name
in my form and that open up the correct year group report and find the
specific pupil.

any help would be much appreciated.

thanks
Chris Barnett

 

[Allen Browne]

Option 1: Move the critiera
Are the separate queries really necessary? Is it possible to remove the
criteria from the query, and use them in the WhereCondition of OpenReport
instead?

This simplifies the process, so in the Click event of your button, all you
have to do is build the correct WhereCondition string, and OpenReport.

Option 2: Lookup the year
Presumably you have some way to look up the year the child is in.
The example below assumes your form has a text box named txtAdm_no that
identifies which child's report you want, and that you can determine the
year from the ChildYear field in a table or query named SomeTable:

Private Sub cmdPreview_Click()
Dim strWhere As String 'Criteria
Dim varYear As Variant 'Year the child is in.
Dim strDoc As String 'name of report.
If Not IsNull(Me.txtAdm_no) Then
strWhere = "adm_no = " & Me.[txtAdm_no]
varYear = DLookup("ChildYear", "SomeTable", strWhere)
If Not IsNull(varYear) Then
strDoc = "[Parent Report - Year " & varYear & "]"
DoCmd.OpenReport strDoc, acViewPreview, , strWhere
End If
End If
End Sub

Note: If [adm_no] is a Text field (not a Number field), you need extra
quotes:
strWhere = "adm_no = """ & Me.[txtAdm_no] & """"

 

[chrisbarnett.werneth]

cheers

got it sorted now thanks

Chris Barnett