Persistent Data Problem

[Guest]

Harvesters has a program for teaching nutrition to kids. I record their
grades using two combo boxes on a form. I use "Me.class" to choose a class
among all classes. I use the second combo box to list students who attended
the class chosen in the first combo box. I have code in the Before Update
event that verifies that this result has not been recorded for this
class/student combination before. If in error, it returns an MsgBox error,
issues Me.undo and clears the screen for the next entry. One is then
expected to choose another student, another class or to exit the application.

When you choose another class, the list of students continues being those
from the previously chosen class. I use "Me.class" in the row source query
to find the list of students for "Me.student". It reacts as if the contents
of "Me.class" does not change in spite of being changed on the form.

The only ways around this I have found is to exit the application and enter
it again or to programatically close the application and reopen it, but the
latter causes the screen to blink annoyingly.

Is there some way to refresh "Me.class" for succeeding classes without
leaving the application?

EarlCPhillips
Ex-mainframer Learning Access To Make
Harvesters Feed The Hungry Efficiently

 

[BruceM]

Harvesters has a program for teaching nutrition to kids. I record their
grades using two combo boxes on a form. I use "Me.class" to choose a
class
among all classes. I use the second combo box to list students who
attended
the class chosen in the first combo box. I have code in the Before Update
event that verifies that this result has not been recorded for this
class/student combination before. If in error, it returns an MsgBox
error,
issues Me.undo and clears the screen for the next entry. One is then
expected to choose another student, another class or to exit the
application.

When you choose another class, the list of students continues being those
from the previously chosen class. I use "Me.class" in the row source
query
to find the list of students for "Me.student". It reacts as if the
contents
of "Me.class" does not change in spite of being changed on the form.

The only ways around this I have found is to exit the application and
enter
it again or to programatically close the application and reopen it, but
the
latter causes the screen to blink annoyingly.

Is there some way to refresh "Me.class" for succeeding classes without
leaving the application?

EarlCPhillips
Ex-mainframer Learning Access To Make
Harvesters Feed The Hungry Efficiently

When you mention code it is usually best to post it, or at least to describe
how it works.

If I understand correctly, you want to select a class, then have the list of
students in another combo box limited to those in the chosen class.
Assuming that the Student record has a field that identifies the class, you
can requery the Student combo box based on the selection in the Class combo
box. You say that you "use the second combo box to list students who
attended the class chosen in the first combo box", but you don't say how
this is done. What you need to do is requery the Student combo box after
selecting a class from the Class combo box.

What you want is often referred to as a cascasding combo box. There is some
information here about how to implement that:
http://www.mvps.org/access/forms/frm0028.htm

In particular, note the part after the end of the code example.

How is Class determined from the Student record? Is there a Class field
that is updated each year as needed? Or is there a graduation year that is
entered when the student record is first created? What is the Row Source
for the Class combo box?

 

[Guest]

The site you put in your reply had just the code I needed. I was not aware
of the REQUERY command. The code used to fetch the student names was
contained in the student combo row source parameter, which was:

SELECT DISTINCT tblParticipants.chrParticiName AS Name FROM tblParticipants,
tblPgmHostInfo WHERE
(((tblParticipants.chrPgmHostName)=Forms!frmOldKITKitchenPrePostTest!cboPgmHostName)
And ((tblParticipants.chrPgmType)="Kids in the Kitchen")) ORDER BY
tblParticipants.chrParticiName;

Class is cboPgmHostName (Host name, meaning where the class was held)
whereas Student is cboParticiName derived from tblParticipants.chrParticiName.

You have helped feed the hungry and teach children how to eat healthy meals.
Thank you on behalf of the needy.

EarlCPhillips
Ex-Mainframer Learning Access To Help
Feed the Hungry Efficiently

 

[BruceM]

Wow! It's not often I can do so much just by pointing out work that
somebody else already did. For myself and on behalf of those who provide so
much free code and help, thanks for your kind words. Best of luck with the
project. Post again any time.