Subform Problems

[PeterWieland]

I am writing a database to manage assessment grades in a large secondary school, and have come across a problem with selecting data to be displayed and edited in a subform.

I have 2 tables, one (Pupils) with fields pupil_enrolment_number, name, tutor_group and another (Results) with fields pupil_enrolment_number, teaching_set, effort_grade and achievement_grade. The tables are linked with a one to many relationship on pupil_enrolment_number (each pupil has more than one teaching set).

I then have a query which produces a list from the Results table, but looks up the name from the Pupil table. This query then appears as a subform, the main form having a combo box that selects the data based on one of the fields in the query (class).

The problem is that the first record in the query gets the value of class overwritten by the value typed into the combo box on the main form.

I have a work round by having a dummy record as the first record, but there must be a more elegant solution.

 

[Abby N]

I'm not sure I understand how your form is set up. Here are my assumptions. You have a main form with a subform. The subform is based on a query with its criteria set by a combobox on the main form. The Control Source property for the combo box is a control in one of your tables. i.e. It is a bound control. If these assumptions are correct then all you need to do is replace your combobox with an unbound control and change the query's criteria to the new combo box. I hope this helps.

~Abby

 

[PeterWieland]

Thanks for the reply Abby.

This is how I had tried it originally, but typing in the class name in the unbound box does not bring up the data in the subform.

Peter

 

[Abby N]

Sorry, I should have specified in my original reply. You'll need to code the unbound control's 'After Update' event to:

Me.Requery

This will cause the form to rerun it's underlying query and give you the current recordset.

~Abby

 

[PeterWieland]

Thanks a lot, it is working fine now.

Peter