Need help with generating report from a parameter form

[dharmacloud]

I want to create a transcript report which contains the student name, address, courses, GPA, units, etc. I am able to generate the transcript for all the students but I want to be able to generate the transcript for a selected student based on the student name or student number.



I created a form with 2 comboboxes: student number and student name. I put the name of the form in the criteria of the query. However, when I select the student number or student name from the form, the query returns 0 results. Also there a parameter box that says "enter parameter value" that opens and asks me to enter in the parameter.

Here's what I put under criteria of the transcript query: [Forms]![ParamForm]![Combo4]

ParamForm is the name of my form with the 2 comboboxes. Combo4 is the student number combobox.

What am I doing wrong?

[June7]

Selecting the student name will not work. Selecting the ID should work. The criteria reference looks correct.

I would have one multi-column combobox that would have both the student ID and name. User would enter name or select from drop list but the filter would use the ID field. Check out this tutorial http://www.datapigtechnologies.com/f...combobox3.html

How are you opening the report - manually or by code?

Post the report query SQL statement for analysis.

[dharmacloud]

My 2 comboboxes (see attached doc.) are bound by StudentID
Combo box #1 is student number which shows both student number followed by student name. Student ID is not visible.

Combo box #2 is student name and shows both student name followed by student number. StudentID is not visible.

I have a code that updates the combo boxes based on the selection in either box. If I select the number in box #1 then the name of the student will automatically appear in the box #2. Here's the code I'm using for that:

Option Compare Database

Private Sub Combo4_AfterUpdate()
Combo6.Value = Combo4.Value
    DoCmd.SearchForRecord , "", acFirst, "StudentID = " & str(Nz([Screen].[ActiveControl], 0))
End Sub

Private Sub Combo6_AfterUpdate()
Combo4.Value = Combo6.Value
    DoCmd.SearchForRecord , "", acFirst, "StudentID = " & str(Nz([Screen].[ActiveControl], 0))
End Sub

Here's the SQL for the report query:

SELECT Student.StudentNumber, Student.StudentID, Student.StudentLastName & ", " & StudentFirstName & " " & StudentMiddleName AS StudentFullName, Student.StudentReligiousName, a.ProgramID, Student.StudentSSN, Student.StudentDOB, a.CourseCode, a.CourseName, a.Unit, a.Grade, a.GradePoint, a.Year, a.TermID, StudentMailingAddress.Address & ", " & StudentMailingAddress.City & ", " & StudentMailingAddress.[State/Province] & " " & StudentMailingAddress.[ZIP/PostalCode] AS StudentFullAddress, a.GradeValidUnit, a.GradeValidPoint
FROM StudentAllCoursesInActiveProgram AS a LEFT JOIN (Student LEFT JOIN StudentMailingAddress ON Student.StudentID = StudentMailingAddress.StudentID) ON a.StudentID = Student.StudentID
WHERE (((Student.StudentNumber)=[Forms]![ParamForm]![Combo4]));

[June7]

Okay - student ID, student number, student name. The query criteria refers to StudentNumber not the StudentID, but the comboboxes are bound to StudentID.

[dharmacloud]

I just changed the comboboxes to being bound by StudentID. But how to change so the query will generate result for the specific student when the user selects by the student number or student name?

[dharmacloud]

Thanks for you help June7!

I finally got it to work by following these instructions:

First, create a query that will display the fields you wish to show in the report.
Second, create a report, using the query as it's record source, that shows the data you wish to display for ALL records.
Let's assume it is a Customer you need as criteria, as well as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth property to 0";1"
Make sure the Combo Box Bound Column is the CustomerID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

[June7]

Glad you got it working.

To answer your question, even though user makes selection based on student number or student name, still do the search with the student ID. If I understand correctly that this ID is unique to the student. I presume it is an Autonumber field. If you want to find a specific student then don't search with names. Names not necessarily unique. What if you have mutiple John J. Smith? I know, unlikely, but not impossible. Search on field you know is unique, either ID or number and bind comboboxes to that field.