focus from main form to sub form

[Stu]

Hi,
I have a combobox who's values change the recordsource of the form.
Within this form, there is a subform, whos records also need to change
pending the value in the combobox. I am able to get the main forms
values to change, but not the subform. I've only been using Access 97
for about a month now, so it might be fairly simple. Here is my code:

Private Sub combo1_Change()
If Me.combo1.Value = "Standard Procedures" Then
Me.RecordSource = "tbl_stdPro c"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy "
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDe sc"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDe sc"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainP rog"
ElseIf Me.combo1.Value = "Qualificat ion" Then
Me.RecordSource = "tbl_Qual"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpe c"
End If
' Everything up to this point works fine. The problem starts below

Me![frm_Type_sub].SetFocus
If Me.combo1.Value = "Standard Procedures" Then
Me.RecordSource = "tbl_stdProc_su b"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy_sub "
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDesc_s ub"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDesc_s ub"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainProg_ sub"
ElseIf Me.combo1.Value = "Qualificat ion" Then
Me.RecordSource = "tbl_Qual_s ub"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpec_su b"
End If
End Sub

From searching the archives, it seems like I need to set the focus to

the form on one line, then to the object on another, but I'm not sure
how to do so.
So to sum up my problem: Using a combobox in a form, how do I change
the subforms recordsource?

Thanks in advance!
Stuart K

[Salad]

Stu wrote:

Hi,
I have a combobox who's values change the recordsource of the form.
Within this form, there is a subform, whos records also need to change
pending the value in the combobox. I am able to get the main forms
values to change, but not the subform. I've only been using Access 97
for about a month now, so it might be fairly simple. Here is my code:

Private Sub combo1_Change()
If Me.combo1.Value = "Standard Procedures" Then
Me.RecordSource = "tbl_stdPro c"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy "
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDe sc"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDe sc"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainP rog"
ElseIf Me.combo1.Value = "Qualificat ion" Then
Me.RecordSource = "tbl_Qual"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpe c"
End If
' Everything up to this point works fine. The problem starts below

Me![frm_Type_sub].SetFocus
If Me.combo1.Value = "Standard Procedures" Then
Me.RecordSource = "tbl_stdProc_su b"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy_sub "
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDesc_s ub"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDesc_s ub"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainProg_ sub"
ElseIf Me.combo1.Value = "Qualificat ion" Then
Me.RecordSource = "tbl_Qual_s ub"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpec_su b"
End If
End Sub

From searching the archives, it seems like I need to set the focus to the form on one line, then to the object on another, but I'm not sure
how to do so.
So to sum up my problem: Using a combobox in a form, how do I change
the subforms recordsource?

From the MainForm you could enter
'Me![frm_Type_sub].SetFocus no need for this
If Me.combo1.Value = "Standard Procedures" Then
Forms!MainForm! SubForm.Form.Re cordsource = "tbl_stdProc_su b"
elseif Me.combo1.Value = "Policy" Then
Me("SubFormName ").Form.Records ource = "tbl_policy_sub "
..........

That's 2 ways to reference the subform. You might need to requery the
subform.


Thanks in advance!
Stuart K

[Stu]

I changed my code to your suggestion, but I get the following message:

If Me.combo1.Value = "Standard Procedures" Then
Me("frm_Type_su b").Form.Record Source = "tbl_StdProc_su b" ' **

and

If Me.combo1.Value = "Standard Procedures" Then
Forms!frm_Type! frm_Type_sub.Fo rm.RecordSource = "tbl_stdProc_su b"
' **

but on both I got the message:
Runtime error 2465
MS Access can't find the field 'tbl_Type_sub' referred to in your
expression. ( on line ** )

I also tried the following code:
If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub .FormRecordSour ce = "tbl_StdProc_su b" ' **

Error Message:
Compile error: method or data member not found. (refers to line **)

any suggestions? I double checked to make sure the form names were
correct, and that the code matches. On the first two codes, it gave
the error of not finding field 'frm_Type_sub' but it should be finding
a form, not a field.

thanks!

[Stu]

I changed my code to your suggestion, but I get the following message:

If Me.combo1.Value = "Standard Procedures" Then
Me("frm_Type_su b").Form.Record Source = "tbl_StdProc_su b" ' **

and

If Me.combo1.Value = "Standard Procedures" Then
Forms!frm_Type! frm_Type_sub.Fo rm.RecordSource = "tbl_stdProc_su b"
' **

but on both I got the message:
Runtime error 2465
MS Access can't find the field 'tbl_Type_sub' referred to in your
expression. ( on line ** )

I also tried the following code:
If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub .FormRecordSour ce = "tbl_StdProc_su b" ' **

Error Message:
Compile error: method or data member not found. (refers to line **)

any suggestions? I double checked to make sure the form names were
correct, and that the code matches. On the first two codes, it gave
the error of not finding field 'frm_Type_sub' but it should be finding
a form, not a field.

thanks!

[Salad]

Stu wrote:

I changed my code to your suggestion, but I get the following message:

If Me.combo1.Value = "Standard Procedures" Then
Me("frm_Type_su b").Form.Record Source = "tbl_StdProc_su b" ' **

and

If Me.combo1.Value = "Standard Procedures" Then
Forms!frm_Type! frm_Type_sub.Fo rm.RecordSource = "tbl_stdProc_su b"
' **

but on both I got the message:
Runtime error 2465
MS Access can't find the field 'tbl_Type_sub' referred to in your
expression. ( on line ** )

I also tried the following code:
If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub .FormRecordSour ce = "tbl_StdProc_su b" ' **

Error Message:
Compile error: method or data member not found. (refers to line **)

any suggestions? I double checked to make sure the form names were
correct, and that the code matches. On the first two codes, it gave
the error of not finding field 'frm_Type_sub' but it should be finding
a form, not a field.

thanks!

Do all of the tables have the same field names? Lets say Table1 has ID,
Descriptiuon and table2 has ID, Desc. You built the form on Table1
(description). So what is Desc in Table2 in the form?

Your could use queries perhaps. In the above example, you could have a
query something like
Select ID, Desc as Description From Table2
Now there is a Description column. You'd use the query there.

[Salad]

Stu wrote:

I changed my code to your suggestion, but I get the following message:

If Me.combo1.Value = "Standard Procedures" Then
Me("frm_Type_su b").Form.Record Source = "tbl_StdProc_su b" ' **

and

If Me.combo1.Value = "Standard Procedures" Then
Forms!frm_Type! frm_Type_sub.Fo rm.RecordSource = "tbl_stdProc_su b"
' **

but on both I got the message:
Runtime error 2465
MS Access can't find the field 'tbl_Type_sub' referred to in your
expression. ( on line ** )

I also tried the following code:
If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub .FormRecordSour ce = "tbl_StdProc_su b" ' **

Error Message:
Compile error: method or data member not found. (refers to line **)

any suggestions? I double checked to make sure the form names were
correct, and that the code matches. On the first two codes, it gave
the error of not finding field 'frm_Type_sub' but it should be finding
a form, not a field.

thanks!

Do all of the tables have the same field names? Lets say Table1 has ID,
Descriptiuon and table2 has ID, Desc. You built the form on Table1
(description). So what is Desc in Table2 in the form?

Your could use queries perhaps. In the above example, you could have a
query something like
Select ID, Desc as Description From Table2
Now there is a Description column. You'd use the query there.

[John Mishefske]

Stu wrote:

Hi,
I have a combobox who's values change the recordsource of the form.
Within this form, there is a subform, whos records also need to change
pending the value in the combobox. I am able to get the main forms
values to change, but not the subform. I've only been using Access 97
for about a month now, so it might be fairly simple. Here is my code:

Maybe I missed something in your post but a record change in the main form would be
reflected in the subform if there is proper linkage between them in the properties of the
SubFormControl - the control on the main form that "holds" the (subform) form.

That's one of the powers of subforms.

As for setting focus from the main to the sub I've found its usually a two-step process.
Set focus to the subform control on the main form and then to a control within the form
contained in the subform control:

Me!sfrmCtrl1.Se tFocus
Me!sfrmCtrl1.Fo rm!controlNameH ereFromSubForm. SetFocus

--
'---------------
'John Mishefske
'---------------

[John Mishefske]

Stu wrote:

Hi,
I have a combobox who's values change the recordsource of the form.
Within this form, there is a subform, whos records also need to change
pending the value in the combobox. I am able to get the main forms
values to change, but not the subform. I've only been using Access 97
for about a month now, so it might be fairly simple. Here is my code:

Maybe I missed something in your post but a record change in the main form would be
reflected in the subform if there is proper linkage between them in the properties of the
SubFormControl - the control on the main form that "holds" the (subform) form.

That's one of the powers of subforms.

As for setting focus from the main to the sub I've found its usually a two-step process.
Set focus to the subform control on the main form and then to a control within the form
contained in the subform control:

Me!sfrmCtrl1.Se tFocus
Me!sfrmCtrl1.Fo rm!controlNameH ereFromSubForm. SetFocus

--
'---------------
'John Mishefske
'---------------