Obtaining multiple report parameters via a Menu form lookup control

[rjgriffin46]

In our Student Administration database, we have a Student Evaluation Report which prints a 1 page per student report in memo like format. The instructor had previously input rating numbers (4=Best, 1=Worst) for 9 categories for each student via a database form into the Evaluations Table.

Two other elements of the rating are an Attendance based on number of days absent and a Grade Point Average (GPA) calculated from the student’s test scores. The instructor then prints and reviews the report with each student.

There is an Evaluations Parameter table which has the following fields:

Field	Description
ID	The autonum key field.
Class	eg., 2015-1
Evaluation Number	A single digit (eg., 1, 2). There may be more than 1 evaluation for each class.
EffectiveDate	The “as of” date of the evaluation.

There are then several other tables that are input to a query that will be the record source for the report:

Table	Purpose/Data
Students	Student Name, Class
Absence	Has a record for each student’s absence with date and a 1 or .5 indicating a whole or half day absent.
Test Grades	Has a record with each student’s test results with date and score.
Evaluations	Holds the rating score for each of the 9 rating categories.

The Student Evaluation report is launched from an unbound Reports menu form via a button. On the Reports menu form I wish to have controls for the user to select which evaluation to report on. The Absence and Test Grade information needs to include records that are <= the EffectiveDate in the Evaluation Parameter table. The Evaluations records need to match the Class and Evaluation Number in the Evaluations Parameter table.

I would like the user to be able to select the Evaluation Parameter via a combo box vs. specifying the Class, Evaluation Number and Effective Date separately and then have the query record source for the report use those two fields as criteria. But my limited understanding of the Combo box indicates that only 1 field from the lookup query of the Evaluations Parameter table can be stored in the control whereas I need 3 (Class Evaluation Number and EffectiveDate).

I would appreciate any suggestions for how the user can select the desired Evaluation Parameter record on the Reports menu form and use the 3 fields from the selected record as criteria in the report’s record source query.

Thanks

[June7]

A combobox can have as many field/columns as needed http://www.datapigtechnologies.com/f...combobox3.html. Then the columns can be referenced by their index.

But I am not really understanding why you need the Class and EffectiveDate as criteria. Is the Evaluation Parameter ID saved into Evaluations table?

[rjgriffin46]

June: thanks. I did not know that the combo box columns were accessible. I did find that out also via internet searches and was able to set the control source for other text boxes on the form.

The Evaluations Parameter ID is stored in the Evaluations table so I can use that to select evaluation records. I need the Class and Effective date for selecting the Absence and Test Grade records which are in separate tables.

Another thing perhaps you can advise on: When I select a different Evaluations Parameter record, if the query is open I have to click on Refresh All in the ribbon to update the query. I tried an On Change and On Dirty VBA procedure using Refresh and also Requery without success. I may have been coding incorrectly. Can you prescribe precise code? Let me know if I should open a new thread for this question.

[June7]

Why open query? Open form or report that uses query as RecordSource.

Are you the only user of this db? Users should not interact with tables and queries, only forms and reports.

[rjgriffin46]

I had the query open to test. I am not a user, just the developer. The users only interact with forms and reports. So, the re-query requirement probably won't be an issue for the user - in fact I tested changing the Evaluation Parameter selection on the Reports menu then clicked the report button and it came up with the different/selected evaluation.

In another instance, however, in the Attendance Page of the Student Details form, I display the results of a summary query that totals the number of days absent above the sub-form where the individual absence records are entered. When you initially enter a new absence record, the total isn't updated. Clicking Refresh All on the ribbon does nothing no matter how many times you click it. You have to either click on a different page on the form, click back to the Absence page and then click Refresh All. Or, click on another student and come back to the student you just updated. I suppose there could be some other code or feature in this form which causes this behavior - it was part of the Students Template database I started the application with. I don't know. Regardless, if you have a method of refreshing in such a situation I'd appreciate getting the details of how to do it.

Thanks again!

[orange]

There are several examples/tutorials regarding Reports at Martin Green's site.

[June7]

The new record must be committed to table before refresh.

Record is committed when:

1. table/query/form closes

2. move to another record

3. run code to save

[rjgriffin46]

Thanks for Martin Green reference-should be useful in the future.

June, is the query supposed to automatically refresh when a record is committed? I'm my absence example, I see a new absence record created (there is an additional row above the new record row) but the totals query doesn't update. Do I also have to save the Student record, even though nothing on it has changed?

[June7]

The query has to be requeried to reflect new record.

[rjgriffin46]

I've not done this yet. Can you tell me exactly how or point me to such guidance? I'm guessing it should be an after update event on the sub-form in which absence records are entered...?

Thanks

[June7]

The summary query is displayed in a subform?

Need code somewhere that requeries the subform. The syntax depends on where.

I am a little confused about this form/subforms structure. If you want to provide db for analysis, follow instructions at bottom of my post.