Subform question

[LSGKelly]

I have two subforms both attached to a main form. The first subform has information based on a field in the main form. When that field is changed, the data in the subform changes. This subform is called Carrier Information. It is linked to the Comparison field on the main form. The Comparison field is a dropdown box. When changes in that field are made, the information automatically brings up the carrier information based on that change.

I now have another subform where the user can select more than one Comparison (this is a continuous form). I am trying to figure out a way for the user to be able to click on any comparison in that subform and have the carrier information shown on the other subform. Is this possible?

Kelly

[PianoMan64]

I have two subforms both attached to a main form. The first subform has information based on a field in the main form. When that field is changed, the data in the subform changes. This subform is called Carrier Information. It is linked to the Comparison field on the main form. The Comparison field is a dropdown box. When changes in that field are made, the information automatically brings up the carrier information based on that change.

I now have another subform where the user can select more than one Comparison (this is a continuous form). I am trying to figure out a way for the user to be able to click on any comparison in that subform and have the carrier information shown on the other subform. Is this possible?

Kelly

Yes, it is possible, but you would need to provide more details as to how, by providing detailed information about table, query structure as well as form details, Control names, and what you have setup so far.

If you provide that information, then I would be able to give you a more detailed answer.

Thanks, and hopes that helps,

Joe P.

[LSGKelly]

Yes, it is possible, but you would need to provide more details as to how, by providing detailed information about table, query structure as well as form details, Control names, and what you have setup so far.

If you provide that information, then I would be able to give you a more detailed answer.

Thanks, and hopes that helps,

Joe P.

Main Form Query:

SELECT tblPlanInfo.[BRET Number], tblPlanInfo.[Plan #], tblPlanInfo.TypePD, tblPlanInfo.HDHPPD, tblPlanInfo.OutOfNetPD, tblPlanInfo.GatekeeperPD, tblPlanInfo.InPatCoPayPD, tblPlanInfo.[4tierRXPD], tblPlanInfo.DedorCoinsPD, tblPlanInfo.DedSingPDIn, tblPlanInfo.DedFamPDIn, "$" & [dedsingpdin] & "/" & "$" & [dedfampdin] AS DedInNetComb, tblPlanInfo.DedSingPOut, tblPlanInfo.DedFamPDOut, "$" & [dedsingpout] & "/" & "$" & [dedfampdout] AS DedOutNetComb, tblPlanInfo.CoinsPDIn, tblPlanInfo.CoinsPDOut, tblPlanInfo.MaxSingIN, tblPlanInfo.MaxFamPDIn, "$" & [maxsingin] & "/" & "$" & [maxfampdin] AS OOPMaxInComb, tblPlanInfo.PhysVPDIn, tblPlanInfo.SpecCoPayPDIn, [physvpdin] & "/" & [speccopaypdin] AS PhysSpecCopayComb, tblPlanInfo.InPatHospPDIn, tblPlanInfo.ERPDIn, tblPlanInfo.RxTier1PDIn, tblPlanInfo.RXTier2PDIn, tblPlanInfo.RXTierePDIn, tblPlanInfo.RXTier4PDIn, tblPlanInfo.MaxSingPDOut, tblPlanInfo.MaxFamPDOut, tblPlanInfo.PhysVPDOut, tblPlanInfo.SpecCoPayPDOut, tblPlanInfo.InPatHospPDOut, tblPlanInfo.[Comparable Plan 1a], tblPlanInfo.[Rating Area 1a], tblPlanInfo.[Comparable Plan 2a], tblPlanInfo.[Rating area 2a], tblPlanInfo.[Comparable Plan 3a], tblPlanInfo.[Rating area 3a], tblPlanInfo.[Comparable Plan 1b], tblPlanInfo.[Rating Area 1b], tblPlanInfo.[Comparable Plan 1c], tblPlanInfo.[Rating Area 1c], tblPlanInfo.[Comparable Plan 1d], tblPlanInfo.[Rating Area 1d], tblPlanInfo.[Comparable Plan 1e], tblPlanInfo.[Rating area 1e], tblPlanInfo.[Comparable Plan 2b], tblPlanInfo.[Rating area 2b], tblPlanInfo.[Comparable Plan 2c], tblPlanInfo.[Rating Area 2c], tblPlanInfo.[Comparable Plan 2d], tblPlanInfo.[Rating Area 2d], tblPlanInfo.[Comparable Plan 2e], tblPlanInfo.[Rating Area 2e], tblPlanInfo.[Comparable Plan 3b], tblPlanInfo.[Rating area 3b], tblPlanInfo.[Comparable Plan 3c], tblPlanInfo.[Rating area 3c], tblPlanInfo.[Comparable Plan 3d], tblPlanInfo.[Rating Area 3d], tblPlanInfo.[Comparable Plan 3e], tblPlanInfo.[Rating Area 3e], tblPlanInfo.[Current Prem1 EE], tblPlanInfo.[Current Prem1 ES], tblPlanInfo.[Current Prem1 EC], tblPlanInfo.[Current Prem1 EF], tblPlanInfo.[Monthly Premium], tblPlanInfo.PrimRateArea
FROM tblGroupInfo INNER JOIN tblPlanInfo ON tblGroupInfo.[BRET Number]=tblPlanInfo.[BRET Number]
ORDER BY tblPlanInfo.[BRET Number], tblPlanInfo.[Plan #];

Query with Carrier Info:

SELECT qryPlanInfo.[Comparable Plan 1a], qryPlanDetails.[Carrier/Plan:], qryPlanDetails.Type, qryPlanDetails.[Pharmacy 4 Tier?], qryPlanDetails.[HDHP? (Y/N)], qryPlanDetails.[Out-of-network?], qryPlanDetails.Gatekeeper, qryPlanDetails.[In-Patient Co-pay?], qryPlanDetails.[Ded or Coinsurance Rx?], qryPlanDetails.[Single Deductible In-Network], qryPlanDetails.[Family Deductible In-Network], qryPlanDetails.[Combined In Deductible], qryPlanDetails.[Single Deductible Out-of-Network], qryPlanDetails.[Family Deductible Out-of-Network], qryPlanDetails.[Coinsurance In-Network], qryPlanDetails.[Coinsurance Out-of-Network], qryPlanDetails.[Single OOP Max In-Network], qryPlanDetails.[Single OOP In W/O Ded], qryPlanDetails.[Family OOP Max In-Network], qryPlanDetails.[Single OOP Max Out-of-Network], qryPlanDetails.[Family OOP Max Out-of-Network], qryPlanDetails.[Phy Office Visit In], qryPlanDetails.[Spc Office Visit In], qryPlanDetails.[Phys Office Visit Out], qryPlanDetails.[In-Patient Hosp In-Network], qryPlanDetails.[In-Patient - In - Detailed], qryPlanDetails.[In-Patient Hosp Out-of-Network], qryPlanDetails.[In-Patient Phys In-Network], qryPlanDetails.[In-Patient Phys Out-of-Network], qryPlanDetails.[Out-Patient Fac In-Network], qryPlanDetails.[Out-Patient Fac Out-of-Network], qryPlanDetails.[Out-Patient Phys In-Network], qryPlanDetails.[Out-Patient Phys Out-of-Network], qryPlanDetails.[Urgent Care], qryPlanDetails.[Emer Rm], qryPlanDetails.Tier1Rx, qryPlanDetails.Tier2Rx, qryPlanDetails.Tier3Rx, qryPlanDetails.Tier4Rx
FROM qryPlanInfo INNER JOIN qryPlanDetails ON qryPlanInfo.[Comparable Plan 1a]=qryPlanDetails.[Carrier/Plan:];

Query for Comparison:

SELECT tblCompare.[Bret #], tblCompare.[Plan #], tblCompare.[Carrier #], tblCompare.Carrier, tblCompare.[Rating Areas]
FROM tblCompare;

Main table is tied to comparison query by Bret# and Plan #. Main query is tied to Carrier query by the comparison 1a field. Once you put information in the comparison 1a field, it populates the information in the carrier form. What I want it to do is...when I fill out the carrier in the Comparison form, I would like it to change in the Carrier form.

I hope that makes it clearer. Sorry for the long post.

Kelly

[nico5038]

You have two options:
1 set the mainform's comparison 1a field to the actual 'active' row of the second subform.
2 have the second subform as the 'master' and use a hidden field to sync both subforms.

In the subform you can use:

parent.[comparison 1a field] = me.[comparison 1a field]

in the OnCurrent event to fill the field.
Be aware that opening the form will cause the value of the [comparison 1a field] from the first row to be pushed to the mainform's field.

Nic;o)

[LSGKelly]

You have two options:
1 set the mainform's comparison 1a field to the actual 'active' row of the second subform.
2 have the second subform as the 'master' and use a hidden field to sync both subforms.

In the subform you can use:

parent.[comparison 1a field] = me.[comparison 1a field]

in the OnCurrent event to fill the field.
Be aware that opening the form will cause the value of the [comparison 1a field] from the first row to be pushed to the mainform's field.

Nic;o)

Nico,

Okay, so ... where do I put the hidden field? Do I put it in the table and then add it to the query, then add it to the form as a hidden field? The OnCurrent event should go in the continuous form?

This is exactly what I'm looking for. I want the main form to show the first field of the continuous form.

Thanks,

[hjozinovic]

I solved it using code like suggested above:

parent.[comparison 1a field] = me.[comparison 1a field]

Only the code fires on Got focus event of each field in the subform instead of OnCurrent event of the subform.

The problem with that aproach was that it gave me error while opening the main form.
Once it's opened it worked fine but I couldn't find a solution to prevent the error while opening the form in the first place.

[nico5038]

Best to create a new field (from toolbox) on the main form and e.g. name it "txtLink".
Then change the sub form's code to fill Parent.txtLink.
Now single click the sub form that needs to show the selected entry and open the properties. Under the datatab You'll see two "linkage" fields.
Place txtLink in the Master and [comparison 1a field] in the Sub field.

That's all.

Nic;o)