Help on combo requery...

[Guest]

Hello,

I have a main form that holds student information. I also have a subform
that holds the class information that the student has or is taking. In that
subform, I have a drop down combo that drops down names of teachers.
I also have an add teacher command button on the main form that runs a
macro. This macro closes the student main form, opens a new teacher form (in
add mode) and adds the teacher to the teachers table. My close button on this
new teacher form closes the form and reopens the student form.

While the addition is successful (the new teacher now shows in the
combo drop-down), when the main form re-opens, it does not return to the
current record I was working on. Is there a way to do this? Is there a way to
add the teacher without closing the main form? I may have multiple times
where
this may happen, eg. when a teacher name is not there, when a class name is
not there, etc.

I received some help earlier on the forms forum; I was asked to try adding:
Dim MyControl As Control
Set MyControl = Forms!FormName!ControlName
MyControl.Requery

to the OnExit property of the form, but there's no OnExit, only OnClose. I
tried it with that, but it came up with an error about not being able to find
my combo name.
Note that my combo is in my subform, not my main form.

Feeling close to a solution,

Rookie.

 

[Nikos Yannacopoulos]

To begin with, there is no reason why you should close the main form;
you can either leave it open or just hide it if you don't want it
visible, by means of:

Forms!FormName.Visible = False

in the command button's code.

Then, in the second form's On Close event code:

Forms!FormName!SubFormName!ControlName.Requery
Forms!FormName.Visible = True

That way the main form will still be at the same record when you return
to it (notice the reference to the control on the subform).Just make
sure you use the correct form/subform/control names instead of the
generic ones above.

HTH,
Nikos

 

[Steve Schapel]

Rookie,

I would leave the original form open while the add new teacher is being
done. And yes, code on the Close event of the new teacher form to
requery the combobox is then the way to go. The code you quoted is a
bit involuted... Just do it like this in one line:
Forms!MainForm!SubForm.Form!Teacher.Requery