Generating Report from Criteria Selected in Form

[Talen]

I am trying to create a temporary report in Access 2000 by selecting the criteria from a form. This is a disabled students records database and the users would like the option to select the different information that prints out. For example the form would look like something like this:

What Information do you want included in your report?

Student Name Yes X No __
Town Yes __ No X
District Yes X No __
Evaluation Date Yes X No __

On the top of the form would be a generate report button that would create a report of the selected records. Of course this is simplistic version as the actual form would have 10-15 records that you could select. I am fairly new to the programming aspect of Access and not sure what is easiest way to tackle this problem. Any help would be greatly appreciated.

 

[FancyPrairie]

What you need to do is build the Where clause for your Select statement. On the OnClick event of the button, insert code that looks something like this:

Dim strWhere as string

strWhere = vbNullString

if (StudentNameChecked) then
strWhere = strWhere & "ysnSudentName = True" & " AND "
end if

if (TownChecked) then
strWhere = strWhere & "ysnTown = True" & " AND "
end if

if (DistrictChecked) then
strWhere = strWhere & "ysnDistrict = True" & " AND "
end if

if (EvaluationDateChecked) then
strWhere = strWhere & "ysnEvaluationDate = True" & " AND "
end if

'******************************************
'* Strip off last " AND " from strWhere *
'******************************************

if (strWhere <> vbNullString) then
strWhere = mid(strWhere,1,len(strWhere)-5)
End if

DoCmd.OpenReport &quot;yourreport&quot;,,,strWhere

You may need to change the AND to OR in the Where clause depending on what you want to do.

 

[Talen]

Thanks for your quick response FancyPrairie but I’m a bit dense when it comes to programming. Could you clarify a few parts?

Is (StudenNameChecked) where you refer to the actual record [strStudentName] or do you put all these fields in the report and the button will determine which records are actually printed?

Also could you explain what the following code does:

if (strWhere <> vbNullString) then
strWhere = mid(strWhere,1,len(strWhere)-5)

This is my first database that I had to get into complex coding and I am trying to learn as I go. Thanks again for your help.