Focus to subform recordsource from combobox

S

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_stdProc"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy"
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDesc"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDesc"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainProg"
ElseIf Me.combo1.Value = "Qualification" Then
Me.RecordSource = "tbl_Qual"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpec"
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_sub"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy_sub"
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDesc_sub"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDesc_sub"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainProg_sub"
ElseIf Me.combo1.Value = "Qualification" Then
Me.RecordSource = "tbl_Qual_sub"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpec_sub"
End If
End Sub
From searching the archives, it seems like I need to set the focus to
Click to expand...
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
 
K

Ken Snell [MVP]

You don't need to set focus on the subform in order to change its
recordsource string. Just set it directly... however, you must reference it
correctly:

If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub.FormRecordSource = "tbl_stdProc_sub"
ElseIf Me.combo1.Value = "Policy" Then
Me.frm_Type_sub.Form.RecordSource = "tbl_policy_sub"
(etc.)

--

Ken Snell
<MS ACCESS MVP>



Stu said:
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_stdProc"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy"
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDesc"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDesc"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainProg"
ElseIf Me.combo1.Value = "Qualification" Then
Me.RecordSource = "tbl_Qual"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpec"
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_sub"
ElseIf Me.combo1.Value = "Policy" Then
Me.RecordSource = "tbl_policy_sub"
ElseIf Me.combo1.Value = "Process Description" Then
Me.RecordSource = "tbl_ProcDesc_sub"
ElseIf Me.combo1.Value = "Program Description" Then
Me.RecordSource = "tbl_ProgDesc_sub"
ElseIf Me.combo1.Value = "Training Program" Then
Me.RecordSource = "tbl_TrainProg_sub"
ElseIf Me.combo1.Value = "Qualification" Then
Me.RecordSource = "tbl_Qual_sub"
ElseIf Me.combo1.Value = "Standard / Specification" Then
Me.RecordSource = "tbl_StdSpec_sub"
End If
End Sub
From searching the archives, it seems like I need to set the focus to
Click to expand...
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
Click to expand...
 
S

Stu

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

If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub.FormRecordSource = "tbl_StdProc_sub" ' **

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

I also tried the following code:
If Me.combo1.Value = "Standard Procedures" Then
Me("frm_Type_sub").Form.RecordSource = "tbl_StdProc_sub" '
**

and

If Me.combo1.Value = "Standard Procedures" Then
Forms!frm_Type!frm_Type_sub.Form.RecordSource =
"tbl_stdProc_sub" ' **

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 ** )

any suggestions? I double checked to make sure the form names were
correct, and that the code matches.

thanks!
 
K

Ken Snell [MVP]

Check your post.... what you posted is not what I suggested.

You posted
If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub.FormRecordSource = "tbl_StdProc_sub"

You're missing a . in the above line:

If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub.Form.RecordSource = "tbl_StdProc_sub"


If this still errors, then try this:

If Me.combo1.Value = "Standard Procedures" Then
Me.frm_Type_sub.Form.RecordSource = "SELECT * FROM tbl_StdProc_sub"
 
Top