Coordinating Data on Subforms of Main Form

[sickenhoofer]

I have a form w several subforms. The ultimate goal would be to click on a location, and have the data in the other subforms change to the data specific to that location.

For this form, there are 4 main tables (below). Here is the pertinent detail for the tables involved.

tblProviderMain (table 1)
ProviderMainID (PK, Autonum)

tblProviderNumb ers (table 2)
ProviderNumberI D (PK, Autonum)
ProviderMainID (global join to tblProviderMain )
NumberType (types = Medicare, Medicaid, BCBS, et al.)
LocationCode (global join to tblLocations)
Number

tblLocations (table 2)
LocationCode (PK, Autonum)

tblProviderbyLo cation (table 3)
ProvbyLocationI D (PK, Autonum)
ProviderMainID (global join to tblProviderMain )
LocationID (global join to tblLocations)

The parent form is based on qry of tblProviderMain . I have a subform for each NumberType within tblProviderNumb ers (Medicare, Medicaid, BCBS, et al). I want to be able to select one of the locations for a provider, and make the data specific to that location appear in the appropriate box (subforms).

For example: I select Charles Brown on the parent form. He has 3 locations listed (Peanutville, Snoopy Town, and Schultzway) If I click on "peanutvill e," the Medicare number and related data for that record should appear in the Medicare subform. The number type subforms (ex: medicare) are fed by similar queries, the only difference being the number type.

I attempted to reach the desired goal in 2 different ways. A brief explanation of each follows:

1. Created a simple query to feed the location subform (to create a list of locations for each provider)

SQL = "SELECT tblProviderbyLocation.LocationID, " & _
             "tblLocations.LocationName, " & _
             "tblProviderbyLocation.ProviderMainID " & _
      "FROM tblLocations INNER JOIN tblProviderbyLocation " & _
        "ON tblLocations.LocationCode = tblProviderbyLocation.LocationID;"

I tried to create an event procedure which would then populate the appropriate info in the number type subforms. However, this is where I got stuck.

2 The other attempt was to make a list box for the locations which was fed by a query of the tblLocations of only the location names. However, this listed ALL locations (not just ones specific to the provider in the active page).

If I could have gotten this method to work, I would have tried to add a FindRecord event procedure to the LocationCode control on the numbertype subform. I am unsure as to whether or not that would have worked, since I cannot get the first part to work.

I hope you can help. I appreciate any feedback.

[NeoPa]

From what I understood of your situation (many details provided - good. Not clear exactly what's going on - less good) you would design all the subforms to handle the full list of possible records (The Medicare subform would handle all records from [tblProviderNumb ers] where NumberType="Med icare" for instance).

From this point, the setup of the main form (specifically the subform control that contains the actual form used) would be set up such that Link Master Fields="ProviderMainI D" & Link Child Fields="ProviderMainI D".

Welcome to Bytes!

[sickenhoofer]

I have all the subforms set as you described (linked by providerMainID) . The trouble spot for me is that I need to create an additional subform or listbox that lists the locations for each provider. We have approx. 20 locations, but each provider works at only 1-4 locations. When the user clicks on a location, the subform data for Medicare, Medicaid, et al. should change to display the related number data for that location.

For example Charlie Brown works at location A B & C. I want to click on location A and see appropriate data in subforms Medicare, Medicaid, et al for location A. Clicking location B would have same effect. Does that make sense?

Here's what's happening:
I first created a subform (linked as above). So the location subform listed only locations at which the specific provider on the master form works. However, I wasn't sure how to use that to populate the related data in the other subforms: meaning, if I click one, I did not know how to use that location to change the display in the other subforms. I did not know if I could use an event procedure to change the other subforms, or if I need to do some other programming (I am at newbie level - I know jsut enought to be dangerous). Here is the SQL for the query feeding the location subform.

SELECT tblProviderbyLocation.LocationID, tblLocations.LocationName, tblProviderbyLocation.ProviderMainID
FROM tblLocations INNER JOIN tblProviderbyLocation ON tblLocations.LocationCode = tblProviderbyLocation.LocationID;

Second attempt:
I then tried to make a listbox of locations. However, I was not sure how to link to masterform, so that it lists all locations for all providers, rather than just those where the provider on masterform works. This is the query for the listbox.

SELECT tblProviderbyLocation.LocationID, tblProviderbyLocation.ProviderMainID, tblLocations.LocationName, tblProviderbyLocation.ProvLocationEffectDate, tblProviderbyLocation.ProvLcoationTermDate, tblProviderbyLocation.Status, tblProviderbyLocation.GroupLocationNPI
FROM tblLocations INNER JOIN tblProviderbyLocation ON tblLocations.LocationCode = tblProviderbyLocation.LocationID;

I thought along with this method, that I could use a FindRecord event procedure in the locationcode control in the related subforms (Medicare, Medicaid, et al), to aid in the change in display. However, I never got that far due to the way the results of the listbox query.

[NeoPa]

It sounds like you have a two-dimensional cut required.

One dimension (ProviderMainID ) is controlled by the link to the main form.

The second cannot be managed that way.

I would consider setting the filter properties of the various subforms when a location is selected from a ComboBox control on your main form.

In the AfterUpdate event procedure of the control cycle through the forms in all your subform controls and set these properties to filter for only those items that match the selected location.

Does that make sense?

[sickenhoofer]

I understand what you are saying. I will give it whirl. Thank you, very much for your help . . .invaluable resource!

[NeoPa]

It's a pleasure to help.

If you can run with a solution that's even better.