Refer to control on 3 pages tab control in subform within form

[dsdevonsomer]

Hello All,
Windows XP
Access 2003
ADP Project with all unbound fields.
SQL Server backend.

It may be simple, and probably it is. But I can't figure it out.
I have main form FRM1, which has combo box CMB1
I have subform within main form SUBFRM1
On the subform, I have a tab control with 3 pages.
On one of the page (PAGE1), I have text box, combo box (CMB 2) and few
other control.

When I select a value in combo box (CMB1) on main form (FRM1), I have
to just reflect that value in combo box(CMB2) on 1st page(PAGE1) in
subform (SUBFRM1). and vice versa.

Just in case, reason for doing this is because main form has about 30
controls and based on status, tabbed subform shows additional info. So
while on that page, I need to show current status.

So, to access CMB2, I use
FORMS!FRM1!SUBFRM1.FORM!CMB2.VALUE . It does not work and get "Method
'form' of object '_subform' not found.
If I just use.
FORMS!FRM1!SUBFRM1.CMB2.VALUE, gives me automation error or property
not found.

I am clueless as to how shall I refer to it.
Many thanks for your help..
Regards,
Snehal

 

[Wayne-I-M]

Hi

I'm a bit confused by your reson for wanting to have your CBM2 based on your
selection in CMB2 ???

Rather than using CMB2 to refer to CMB1 you may be better to simply filter
the record source of your subform. The reason for this is that if you select
data in CMB1 then (using your current method) you are trying to fill CBM2
with just one item as it’s record source – in this case you would better
using an unbound text box.

If you still want to use CBM2 (can’t really see the point but you have more
information regarding the usage – so you may be right) you "coul" simply use
the same record source as CMB1 (but like I said - can't see the point in
having the same combo on 2 pages).

What you really need to do is to filter the subform so it will display
corresponding data to the main form. You do this from the sub forms record
source.

Use something like this

Select * From TableName Where “whatever†= Forms![MainForm]![Combo]

The “whatever†is the filter – remove the “ “.

Don’t forget that you will need to requery the subform after you select an
item from CMB1 - use the After Update action of CMB1

Me.[SubForm].Requery

Hope this helps

 

[dsdevonsomer]

Hey Wayne,
Thank you much for response.
You have a valid point, but CMB2 is a subset of statuses that apply to
a particular item. Also, everything on any form is UNBOUND control.

So in CMB1, I have about 8 status, but in CMB2, it has only 3 which are
final stage status for a process. It is called "Close Request" subform
and CMB2 has "Closed", "Cancelled", "Deleted" status. Now, for just 3
static values, it's an overhead to run query to filter against
database. right ? So, I have static values, just reflect status from
CMB1 to CMB2 or vice versa. CMB1 has status values from database using
Exec SP statement.

Basic issue is, if I have to read/set the value off of tab control in a
subform from a main form, how do I do that. I used Combo box to help
understand the situation. But there are about 10 fields on that tab
page in a subform for which I have to read values to create
insert/update statements or even update their values based on the
select query that I run on main form. So, if I had " Select * from
Tbl_ItemProcess where Id = 5 " and bring the form/subform/tabs in Edit
mode, I need to refer to controls from main form. Am I using right
approach ?

Hope I have explained little better this time.
Regards,
Snehal

Hi

I'm a bit confused by your reson for wanting to have your CBM2 based on your
selection in CMB2 ???

Rather than using CMB2 to refer to CMB1 you may be better to simply filter
the record source of your subform. The reason for this is that if you select
data in CMB1 then (using your current method) you are trying to fill CBM2
with just one item as it's record source - in this case you would better
using an unbound text box.

If you still want to use CBM2 (can't really see the point but you have more
information regarding the usage - so you may be right) you "coul" simply use
the same record source as CMB1 (but like I said - can't see the point in
having the same combo on 2 pages).

What you really need to do is to filter the subform so it will display
corresponding data to the main form. You do this from the sub forms record
source.

Use something like this

Select * From TableName Where "whatever" = Forms![MainForm]![Combo]

The "whatever" is the filter - remove the " ".

Don't forget that you will need to requery the subform after you select an
item from CMB1 - use the After Update action of CMB1

Me.[SubForm].Requery

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do

Hello All,
Windows XP
Access 2003
ADP Project with all unbound fields.
SQL Server backend.

It may be simple, and probably it is. But I can't figure it out.
I have main form FRM1, which has combo box CMB1
I have subform within main form SUBFRM1
On the subform, I have a tab control with 3 pages.
On one of the page (PAGE1), I have text box, combo box (CMB 2) and few
other control.

When I select a value in combo box (CMB1) on main form (FRM1), I have
to just reflect that value in combo box(CMB2) on 1st page(PAGE1) in
subform (SUBFRM1). and vice versa.

Just in case, reason for doing this is because main form has about 30
controls and based on status, tabbed subform shows additional info. So
while on that page, I need to show current status.

So, to access CMB2, I use
FORMS!FRM1!SUBFRM1.FORM!CMB2.VALUE . It does not work and get "Method
'form' of object '_subform' not found.
If I just use.
FORMS!FRM1!SUBFRM1.CMB2.VALUE, gives me automation error or property
not found.

I am clueless as to how shall I refer to it.
Many thanks for your help..
Regards,
Snehal

 

[Ricter]

I had a lot of trouble following the standard "Refer to controls and
properties..." stuff from The Access Web, then I picked up a tip here on this
board: use a two-step approach to set focus on buried controls.

Set focus on the subform, then
set focus on the subform's control.

 

[dsdevonsomer]

Hey Ricter,
Thanks for the help. I tried your suggestion , but didn't help. Though
I tried on my own something interesting. I created another subform
without tabs on it. Just few controls.
Made it part of the main form with tab. Launched application, and there
it goes, I can access fields on the sub form to and from main form.

So what does this mean...?
that I can not access fields on the sub form with tabs. Following
works...
=======================
frmReq main form.( with tabs )
frm2_1 is subform ( without tabs )
label0 is control on frm2_1
Text1 is control on frmReq

MsgBox Forms!frmReq.frm2_1.Form.label0.Caption
MsgBox "value in text box " & Me.Text1.Value
MsgBox "Setting value in subform "
Me.frm2_1.Form.label0.Caption = Me.Text1.Value
==================
weird..
can anyone help me???