linking tables

[chiefmsb]

I have three tables as follows
1. Information table with autogenerated key
2. History table with a number key I want to link with the autogenerated key above
3. Encounter table with dates of encounters and what was done with a number field to link it to one and two above.
When I open the first database I want to have tabs to open the others so that only information for that persons autogenerated number appears. I made relationships with the autogenerated ID key and the other number fields in the other two databases.


I used to program with foxpro and am new to access and haven't been programming in quite a while and its coming back slowly. How do I link these. Is it done via the forms putting code into the number id field making info.id=history.id. any help would be appreciated. thanks

[Rod]

Hi there.

You write about 'linking' your tables. There are two kinds of link:

1. A permanent database relationship between your tables. The main purpose of this relationship is to ensure the integrity of your data. It is also used as a default link when defining queries;

2. A join between tables that exists only for the purpose of the SQL query in which it is defined. The join can be (and often is) different from the database relationship; it overrides the database relationship.

Links can also be simulated by filtering data, by using the WHERE clause of SQL statements or by defining Master/Child fields in a Parent/Subform configuation.

What you describe - I assume you mean tables or forms and not databases - is a fairly standard requirement usually solved by the Parent/Subform configuration and to achieve it you need not define formal db relationships. However I would strongly recommend that you do.

OK, how do you set up the Parent/Subform? I shall use the Information and History tables in the following. You can then apply the same principles for the Encounter table.

Design two forms: one is a standard single record form bound to the Information table and the other is a continuous (multi-line) form bound to the History table.

In design view for the Information form either:

1. Drag the second form into the first form, or
2. Insert a subform control and enter the second form name in the Source Object property of this control

Finally enter values in the Link Master Fields and Link Child Fields properties of the subform control. The master field is the name of your autogenerated key and the child field is the name of your number key on the History form.

Hope this is what you want and if I've been too detailed, forgive me.

[chiefmsb]

rod,
thanks a lot. It seems to be what I am looking for. One thing. When I do this the subform is under the initial form. Can this be set up with tabs so the info is the opening page and there is a history tab that is linked in this manner
thanks, mark

[Rod]

Hi Mark,

I hope I'm answering the right question(s); tell me if I'm not.

A parent form may contain more than one subform. You can insert two subforms, one for history and one for encounters, both linked as I have described in my previous post. If you have room then you may display both subforms simultaneously, say side by side. If not I suggest you overlay them and have a mechanism on the parent form to display one and hide the other and vice versa.

You could use a label, text box, combo box, list box or command button (toggle is good) for the control on the parent form that hides/shows the subform. In this case I would tend to choose either a label or a button; you don't need the functionality of a list control and an enabled text box allows the user to enter spurious data.

You hide/show the subforms by changing the visible property of the subform control, not the subform itself. Here's some sample code for the OnClick event of a label.

 
If Me.lblToggleSubForm.Caption = "History" then
Me.sfrHistory.Visible = False
Me.sfrEncounters.Visible = True
Me.lblToggleSubForm.Caption = "Encounters"
Else
Me.sfrHistory.Visible = True
Me.sfrEncounters.Visible = False
Me.lblToggleSubForm.Caption = "History"
End If

Starting conditions can sometimes prove a little tricky so you may have to tune the parent form's OnLoad event.

...

Another way of showing/hiding subforms is to place each subform on a separate page of a tab control. This is maybe the solution you have in mind. In this case link the subforms in exactly the same way.