Two related tables to display in Tabular format on same form 1

[torf66]

I have a form and at the top has the following fields:
AdmitNumber
Admit Date
Discharge Date
Patient First Name
Patient Last Name

Then I have a tabular subform in the middle that has
numerous fields where the user can enter data. They can add records to the Admit Number fields at the top and then have many records listed in the subform. On the form I have
one record displayed at the top for Admit Number, Admit Date Etc. Then if the navigate to the next record on that form then the records at the top change along with the records in the subform. This all works great.

What I want to do is at the top I want to list multiple records from that table at the top(not just one admit number etc.) at a time I want sort of two tabular forms on one page.
For example the top of the form I would look it to like this
with multiple records listed not just the first record which
is what you get when you create a form from the wizard using two tables. This info at the top is in Table 1 Admit # is the Key.
Admit Number Admit Date Discharge Date Patient Nam
12345 01/01/04 01/01/04 Jane Doe
56789 02/01/04 02/02/04 Jime Doe

In the middle of the form is this(in Tablular format)
(these records that display here are for admit # 12345)
Review Type Resolution Status Completion Date
BCBS Appeal Complete 01/01/04
Medicare Agree In Progress

I want to be able to have the user at the top click on the
record and then have the subform display data that pertains to that admit number. How can I get the top of the form to
display in a Tabular format like the subform that can display multiple records and when the user clicks on that record it changes the records in the subform to that admit Numbers info?

 

[RickSpr]

You can not do that. To display multiple records on a form, you must either use Default View = Continuous Forms, or you must make it a datasheet. In the first case (Continuous Forms), the subform would be repeated for each main form record. In the second case (using a datasheet) the subform would not be displayed at all.

To make it work something like you want, you should use linked forms. That is, use two separate forms, one with the admit #s and the other showing the details.

The first form should be bound to Table1 and its Default View should be set to either Continuous Forms or Datasheet. It must have a Form_Current event procedure, which gets executed every time the user selects a different admit#. The event procedure executes DoCmd.OpenForm for the second form, specifying as a Filter parameter something like:
"AdmitNumber = " & Me.Admit#
This will cause the second form to show only those rows with the matching admit#.

The second form is also set to Continuous Forms or Datasheet, and is bound to the child table (Table2).

I'm falling asleep here, but I hope the above makes sense. Give it a try and come back with any questions you have about it.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PHV]

You may consider using a ListBox for the Admit Numbers.
Then in the Click event procedure of this ListBox you rebuild the RowSource property of the continuous subform and ReQuery it.

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

 

[RickSpr]

PH's ListBox solution would be much easier, and you should go that way if there is no need to update the info at the top.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TheAceMan1]

How are ya torf66 . . . . .

You can do what you want, its just the Form Wizard doesn't link/synchronize the way you require. You have to synchronize yourself . . . . so lets do it! ([purple]backup the database so can come back to square one if you desire[/purple]) . . . .

1) Open the MainForm in design view and call up the [blue]form properties[/blue]. Click the [blue]Data Tab[/blue].

2) Click the subForm control. On the [blue]Data Tab[/blue], delete any text in the [purple]Link Master Fields[/purple] & [purple]Link Child Fields[/purple] properties. Save the form but dont close.

3)If the Form [blue]Header/Footer[/blue] are not already showing, make it so. Add some space in the Form Footer.

4) Now, move the subform to the Form Footer.

5) If not already there, move your controls form the MianForm to the Details Section of the MainForm.

6) [purple]Set the MainForm for Continuous View[/purple]. Save the form.

7) As a check, open the MainForm to insure MainForm & subForm are in continuous view. Close the MainForm.

[blue]Now we synchronize the forms:[/blue]

8) Open the subForm. Get to the [blue]RecordSource[/blue] property for the subForm. Put the cursor on that line, then click the three elipses on the right. If the RecordSource is not based on a query or SQL, a message will pop-up. Just continue on to the [blue]query builder[/blue].

9) In the query builder make sure all the fields for the subform are there. Include the [purple]AdmitNumber[/purple] field.

10) In the [blue]criteria[/blue] for AdmitNumber add the following line (you substitute [purple]MainFormName[/purple]):

Forms![purple]MainFormName[/purple]!AdmitNumber

11) Close out & save the subform, but stay in design view.

12) So you can [blue]save records[/blue] in the subForm, add the following line to the [purple]Default Value[/purple] property for the AdmitNumber control.

=Forms![purple]YourMainFormName[/purple]!AdmitNumber

13 Save & close the subform.

Thats it! Give it a whirl and let me know . . . . .

See Ya! . . . . . .

 

[RickSpr]

AceMan, I thought about putting the second subform in the form footer, but I figured the "manual" synchronization would be too complicated. Thanks for making me see that it's quite simple. Have a star.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TheAceMan1]

How are ya Rick . . . . thanks . . . .

Yeah . . . . you can sunchronize any number of forms is this way. Doesn't have to be a subform. I prefer this method because it works even after Dpendent forms are open (most others only synchronize on opening the Dependent form). I have a number of DB's that synchronize forms in this way. One in particular handles four forms that are open at the same time. Change a record on the [blue]Independent[/blue] and the [blue]Dependents[/blue] sync-up.

The biggest concern with this type of synchronization is if [blue]you intend to add records[/blue] thru an Dependent Form. Just gotta remember to set the [blue]Default Value[/blue] respectively. This emulates exactly what happens when form/subform are linked with master/child properties. Goto a new record on the subform and the link field is shown automatically defaulting to the mainform.

So . . . . if the [blue]Form Wizard[/blue] won't do what ya want . . . . go ahead . . . . be the man . . . . [purple]and make your day![/purple]

See Ya! . . . . . .