Select Subform based on Current Data

[BLW]

I have a form that show various insurance claims; WC, MVA, GL, PL. I have a
subform for each type of claim showing various details of each claim. Is it
possible to have only the corresponding subform show depending upon the type
of claim the current record is?

If it is possible, where would I put the event procedure?

Thanks for any help.

BLW

 

[scubadiver]

It is easier to have one subform, use a query as the source and use a combo
to select the type you need and refresh the data.

Simply reference the combo in the query and put

me.requery
me.refresh

in the after update event of the combo

 

[boblarson]

Actually, you can have several subforms and then assign the appropriate one
on the combo's after update event.

Private Sub YourComboName_AfterUpdate()
Dim strSource As String

Select Case YourComboName
Case "Whatever"
strSource = "YourFrmName
Case "Whatever Else"
strSource = "YourForm2Name"
Case "Another Value"
strSource = "YourOtherFormName"
End Select

Me.YourSubformContainerOnMainForm.SourceObject = strSource

Hope that helps
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[boblarson]

And actually to clarify on my other post. You have ONE subform container
control on the main form and then you assign it's SourceObject property to
the applicable subform.

Just realize that the subform container control on the main form is not the
subform but is the container that HOUSES the subform on the main form. So,
you have some flexibility there. Just remember to use its name in the code
when referring to the SourceObject.

See the screenshot her
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
to clarify what the subform container is.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[BLW]

Thanks for your help - but I must be doing something wrong.

My form is titled "Claims Review". The data is pulled from a union query.
In that form I have a field called "Type of Claim". Right now, until I get
this working, I have two subforms "Claims Review MVA subform" and "Claims
Review WC subform". The house for the subforms is "Child51"; the link
between the subform and the main form is "Case #".

Right now the "Claims Review MVA subform" is set in the subform house with
the "Case #" linked. I have changed the "Type of Claim" field to a combo box
and I added the following code:

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case [Type of Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

What did I do wrong - this is not doing anything; I am not even getting an
error message - the subform just remains the orginal one.

BLW

 

[boblarson]

I think your control is not correct. You have the sub listed as
Type_of_Claim but the case is checking it without the underscores.
Also, I would not have spaces in my field, or object names so you don't have
to worry about brackets, etc.

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case Me![Type_of_Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

Thanks for your help - but I must be doing something wrong.

My form is titled "Claims Review". The data is pulled from a union query.
In that form I have a field called "Type of Claim". Right now, until I get
this working, I have two subforms "Claims Review MVA subform" and "Claims
Review WC subform". The house for the subforms is "Child51"; the link
between the subform and the main form is "Case #".

Right now the "Claims Review MVA subform" is set in the subform house with
the "Case #" linked. I have changed the "Type of Claim" field to a combo box
and I added the following code:

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case [Type of Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

What did I do wrong - this is not doing anything; I am not even getting an
error message - the subform just remains the orginal one.

BLW

And actually to clarify on my other post. You have ONE subform container
control on the main form and then you assign it's SourceObject property to
the applicable subform.

Just realize that the subform container control on the main form is not the
subform but is the container that HOUSES the subform on the main form. So,
you have some flexibility there. Just remember to use its name in the code
when referring to the SourceObject.

See the screenshot here
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
to clarify what the subform container is.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[BLW]

No - that didn't work either. Any other ideas?

BLW

I think your control is not correct. You have the sub listed as
Type_of_Claim but the case is checking it without the underscores.
Also, I would not have spaces in my field, or object names so you don't have
to worry about brackets, etc.

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case Me![Type_of_Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

Thanks for your help - but I must be doing something wrong.

My form is titled "Claims Review". The data is pulled from a union query.
In that form I have a field called "Type of Claim". Right now, until I get
this working, I have two subforms "Claims Review MVA subform" and "Claims
Review WC subform". The house for the subforms is "Child51"; the link
between the subform and the main form is "Case #".

Right now the "Claims Review MVA subform" is set in the subform house with
the "Case #" linked. I have changed the "Type of Claim" field to a combo box
and I added the following code:

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case [Type of Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

What did I do wrong - this is not doing anything; I am not even getting an
error message - the subform just remains the orginal one.

BLW

And actually to clarify on my other post. You have ONE subform container
control on the main form and then you assign it's SourceObject property to
the applicable subform.

Just realize that the subform container control on the main form is not the
subform but is the container that HOUSES the subform on the main form. So,
you have some flexibility there. Just remember to use its name in the code
when referring to the SourceObject.

See the screenshot here
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
to clarify what the subform container is.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


:

I have a form that show various insurance claims; WC, MVA, GL, PL. I have a
subform for each type of claim showing various details of each claim. Is it
possible to have only the corresponding subform show depending upon the type
of claim the current record is?

If it is possible, where would I put the event procedure?

Thanks for any help.

BLW

 

[boblarson]

If you want to email it to me I can take a look. At this point it would
probably be much faster.

Send it to me at accessbob [at] g mail. c o m. Be sure to include an
explanation of the problem again (as I get a lot of email and can't always
remember which is which).
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

No - that didn't work either. Any other ideas?

BLW

I think your control is not correct. You have the sub listed as
Type_of_Claim but the case is checking it without the underscores.
Also, I would not have spaces in my field, or object names so you don't have
to worry about brackets, etc.

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case Me![Type_of_Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

Thanks for your help - but I must be doing something wrong.

My form is titled "Claims Review". The data is pulled from a union query.
In that form I have a field called "Type of Claim". Right now, until I get
this working, I have two subforms "Claims Review MVA subform" and "Claims
Review WC subform". The house for the subforms is "Child51"; the link
between the subform and the main form is "Case #".

Right now the "Claims Review MVA subform" is set in the subform house with
the "Case #" linked. I have changed the "Type of Claim" field to a combo box
and I added the following code:

Private Sub Type_of_Claim_AfterUpdate()

Dim strSource As String

Select Case [Type of Claim]
Case "WC"
strSource = "Claims Review WC subform"
Case "MVA"
strSource = "Claims Review MVA subform"
End Select

Me.Child51.SourceObject = strSource

End Sub

What did I do wrong - this is not doing anything; I am not even getting an
error message - the subform just remains the orginal one.

BLW


:

And actually to clarify on my other post. You have ONE subform container
control on the main form and then you assign it's SourceObject property to
the applicable subform.

Just realize that the subform container control on the main form is not the
subform but is the container that HOUSES the subform on the main form. So,
you have some flexibility there. Just remember to use its name in the code
when referring to the SourceObject.

See the screenshot here
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
to clarify what the subform container is.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


:

I have a form that show various insurance claims; WC, MVA, GL, PL. I have a
subform for each type of claim showing various details of each claim. Is it
possible to have only the corresponding subform show depending upon the type
of claim the current record is?

If it is possible, where would I put the event procedure?

Thanks for any help.

BLW