problem referencing a control

  • Thread starter Thread starter Mr. T.
  • Start date Start date
M

Mr. T.

Hi,

i've got a form with a tabpage on it. On one of the tabs there's a second
tab-page. On one of the tabs of that 2nd tab-page there's a subform.

That subform is shown in datasheet view.

One of the fields on that subform is a dropdown with an sql instruction as
it's rowsource. Now i want the sql to be dependant of the value of a field
on the main form. The problem is i can't seem to reference that field from
the subform.

So in the dropdown's rowsource field i type

SELECT * FROM MyTable WHERE MyKeyField =

and after the = i've already tried with

Me.Parent.Form!MyControlName

Me.Parent!MyControlName

Parent!MyControlName

but that didn't work. When i go to the tab where that subform is, i get a
popup where it asks for the value of MyControlName.

Then i thought i'll set the sql from the main form, so in the main form
Current Event i put

Me.MySubForm.Forms.MyControlName.Rowsource = MySqlQuery

or MySubForm.MyControlName.Rowsource = MySqlQuery

but that didn't work either. I got the error that i referenced to the form
in the wrong way.

Has anyone got an idea how to do this or what i'm doing wrong? I'd prefer to
work the first way, so with the dropdown's Rowsource referencing to the
field on the main form throught the sql. I think that's the best way to do
it.

Any suggestions would greatly be appreciated.

Regards & thx in advance,

Thomas
 
Mr. T. said:
i've got a form with a tabpage on it. On one of the tabs there's a second
tab-page. On one of the tabs of that 2nd tab-page there's a subform.

That subform is shown in datasheet view.

One of the fields on that subform is a dropdown with an sql instruction as
it's rowsource. Now i want the sql to be dependant of the value of a field
on the main form. The problem is i can't seem to reference that field from
the subform.

So in the dropdown's rowsource field i type

SELECT * FROM MyTable WHERE MyKeyField =

and after the = i've already tried with

Me.Parent.Form!MyControlName

Me.Parent!MyControlName

Parent!MyControlName

but that didn't work. When i go to the tab where that subform is, i get a
popup where it asks for the value of MyControlName.

Then i thought i'll set the sql from the main form, so in the main form
Current Event i put

Me.MySubForm.Forms.MyControlName.Rowsource = MySqlQuery

or MySubForm.MyControlName.Rowsource = MySqlQuery

but that didn't work either. I got the error that i referenced to the form
in the wrong way.

Has anyone got an idea how to do this or what i'm doing wrong? I'd prefer to
work the first way, so with the dropdown's Rowsource referencing to the
field on the main form throught the sql. I think that's the best way to do
it.
Click to expand...


You are on the right track, but you can not use Me and
Parent in a query or control source expression or anywhere
else outside of VBA.

If you use this in MySqlQuery:
SELECT * FROM MyTable WHERE MyKeyField =
Forms!nameofmainform!mainformfield

then you do not need to reset the combo box's RowSource.
Instead you can use:

Me.subformcontrol.Form.combobox.Requery

in both the mainformfield's AfterUpdate event and the main
form's Current event.
 
Hi,
You are on the right track, but you can not use Me and
Parent in a query or control source expression or anywhere
else outside of VBA.

If you use this in MySqlQuery:
SELECT * FROM MyTable WHERE MyKeyField =
Forms!nameofmainform!mainformfield

then you do not need to reset the combo box's RowSource.
Instead you can use:

Me.subformcontrol.Form.combobox.Requery

in both the mainformfield's AfterUpdate event and the main
form's Current event.
Click to expand...

i've got one problem that i forgot to mention as i see now (sorry for that).
The subform is used in 2 different main forms. So the name of the main form
differs dependant on what main form it's used in.

So then i can't use this solutoin either, no?

Regards,

Thomas
 
Mr. T. said:
i've got one problem that i forgot to mention as i see now (sorry for that).
The subform is used in 2 different main forms. So the name of the main form
differs dependant on what main form it's used in.

So then i can't use this solutoin either, no?
Click to expand...


Alright, with that information, you need to reset the combo
box's row source SQL statement on the fly.

Me.subformcontrol.Form.combobox.RowSource = _
"SELECT * FROM MyTable WHERE MyKeyField = " _
& Parent.mainformfield

Since that will also requery the combo box, there is no need
for any other actions.
 
Back
Top