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.

 

[Ken Snell [MVP]]

If the combo box is in the subform, then you must reference it via the main
form and the subform control:

Set MyControl = Forms!FormName!SubformName!ControlName

where SubformName is the name of the subform control (the control that holds
the subform) in the main form.

 

[Guest]

Hey Ken,
Thanks for your reply; what do you mean by the 'control that holds the
subform'? Are you talking about the Primary key link? If so, it's still not
coming out right. It gives me a syntax error.
I'm entering:

Dim MyControl As Control
Set Mycontrol=Forms!frmstudents!Employee Number!teachers
MyControl.Requery

In the Onclose property of the form.
My PK (Employee Number) has a space. Does it matter?
Even when I enter the name of the subform (tbltraining subform) like this:

Dim MyControl As Control
Set Mycontrol=Forms!frmstudents!tbltraining subform!teachers
MyControl.Requery

I still get an error.

Any ideas?

Thanks in advance,

John.

 

[Guest]

Hey Ken,
I got it. In my control name, I was putting 'teachers' instead of 'teacher.'
Believe it or not, it took me about 2 hours to spot that.

I can hear you saying, "You have much to learn, my young padwan..."

Thanks again,

John.

 

[Ken Snell [MVP]]

Been there, keep returning there, done that.... < g >

Glad you found the error.