Change SUB Form Record Source? 1

[MkIIISupra]

Okay, I can change a Forms Record source till I am blue in the face, simple, easy CAKE WALK!!!! But I cannot for the life of me seem to get a SUB Form Record Source to change...

Set up (Key components)
Form1 = frmContractInfo - Master Form with all controls
Form2 = SUB_frmMasterDistrict - SUB Form with records that can be selected as needed. This form is embedded into frmContractInfo and the one that needs to have the data source changed based on a Combo Box selection.
ComboBox1 = cboSchTy - User selects Charter or Regular to change SUB_frmMasterDistrict.RecordSource

More info, I have a Combo Box on both forms (I have tried from both forms to make this work, I only want it on the parent form (frmContractInfo) but I have been trying everthing I can think of to get this to work.

Here is how things are supposed to work:
User selects one of two choices from cboSchTy which will then change the record source for SUB_frmMasterDistrict so the user may then proceed on with their task.

Here is the VBA behind each of these Combo Boxes(one on each of the two Forms). And the error message that I am getting. Please keep in mind the error message is identical if I run this code from either form when both are open BUT if I open the SUB_frmMasterDistrict by itself the code works. It only fails when I have frmContractInfo open which in turn opens the embedded SUB_frmMasterDistrict.

Private Sub cboSchTy_AfterUpdate()

If (Me.cboSchTy) = "Charter" Then
Forms!SUB_frmMasterDistrict.RecordSource = "tblCharterSchools_DistList"

ElseIf (Me.cboSchTy) = "Regular" Then
Forms!SUB_frmMasterDistrict.RecordSource = "tblMasterDistrict"

End If

End Sub

ERROR MESSAGE BEGIN
Microsoft Visual Basic
Run-time error '2450'

Microsoft Access can't find the form 'SUB_frmMasterDistrict' referred to in a macro expression or Visual Basic Code
ERROR MESSAGE END

I have tried to Dimension the SUB_frmMasterDistrict but same error...
Dim SUB_frmMasterDistrtict As Form

What am I missing?

One by one the penguins steal my sanity!

 

[formerTexan]

Hi,

I wonder what the penguins do with it..

Try:

Forms("MainFormName")("SubFormControlName").form.recordsource

There is a difference between the subform control and the subform. Make sure that your reference uses the subform control name. This may be different from the subform name.

Cheers,
Bill

 

[PHV]

You may try this:
If (Me!cboSchTy) = "Charter" Then
Me!SUB_frmMasterDistrict.Form.RecordSource = "tblCharterSchools_DistList"
ElseIf (Me!cboSchTy) = "Regular" Then
Me!SUB_frmMasterDistrict.Form.RecordSource = "tblMasterDistrict"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MkIIISupra]

PVH!!! YOU ROCK!!!! THANK YOU!@!!!!!

Okay now I will calm down and ask a question... How does this work? What I mean is the Me! what does it reference, the main form or the sub?

Again THANK YOU!!!!

One by one the penguins steal my sanity!

 

[PHV]

Me always references the form where the code is attached.
Your code is attached to cboSchTy, cboSchTy is in the main form, so Me references the main form in this Sub.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eb24]

I am trying to resolve a similar error (2450), and mine looks very similar to this. Can someone please assist me (PHV)? Does someone see something wrong? Advice?
Code before reading this post:

Set thisRS = Forms![SubFrm].S_ID
Criteria = "[S_ID] = """ & Me![SearchType] & """"
thisRS.FindFirst Criteria
If Not thisRS.NoMatch Then
     Forms![SubFrm].Bookmark = thisRS.Bookmark
End If

Code after reading this post:

Set thisRS = Me![SubFrm].S_ID
Criteria = "[S_ID] = """ & Me![SearchType] & """"
thisRS.FindFirst Criteria
If Not thisRS.NoMatch Then
     Me![SubFrm].Bookmark = thisRS.Bookmark
End If

 

[PHV]

What is S_ID ?
You may try this:
Set thisRS = Me![SubFrm].Form.RecordsetClone
and this:
Me![SubFrm].Form.Bookmark = thisRS.Bookmark

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eb24]

S_ID is the unique identifier on the subform that displays a record set. So on DoubleClick on this (S_ID), it transfers the user to a new form.

So I shouldn't put the actual field at the end, but RecordSetClone instead?

Thank you so much for your assistance!