Single form multiple tables

[andrewhoddie]

Hi all

I have created a single form (with a sub form with a number of tabs) what I would like to do is have one table per tab of the form to keep the data manageable.

The form is called HomelessnessApp
The first table is called Homelessness
the second table is called Access
the third table is called Priority

I currently have a couple of issues, for each of the tables I want it to use the ID from the Homelessness table to link to all of the other tables so I can easily identify which record relates to other records in tables. The ID on the homeless is the primary key, I am struggling to bring this into the other tables

the second issue I am having is that on each of the different tables I am trying to link them to the form but the only options I am getting is link to objects in the first table (Homelessness), I think the two issues could be related.

Please can anyone help or advise on a simple alternative. I don't want to restart the form as it took a lot of time getting to that stage

thanks
Andrew

 

[ranman256]

The master table tHomelessness (& form) has field [ID] autonumber PK.
ALL the other tables will also have
an autonumber primary key, but ALSO a HomelessID that is the [ID] from tHomelessness (but LONG, not auto) ...

tAccess
-----------
[AccessID] auto
[ID] (long)

tPriority
-----------
[PriorityID] auto
[ID] (long)


the subforms MUST have the keys bound under the form property LINK MASTER FIELD, and LINK CHILD FIELD
for the ACCESS subform
LINK MASTER FIELD, [ID]
LINK CHILD FIELD: [ID]
(both belong to the master)

this insures the master key is locked into the child record. do this for ALL sub forms.

NOTE : IF YOU PUT TOO MANY SUB FORM ON THE MASTER FORM, YOU WILL GET AN 'OUT OF MEMORY' ERROR.
you cant put too much data on the screen at once.
I only have many sub tables but only 1 active sub form. When a user clicks on a page Tab, I swap out the recordset in the subform, to reduce memory use.
The user clicks OrderDetails, so
subform.sourceObject = "frmOrderDtls"

when the user clicks Contacts,
subform.sourceObject = "frmContacts"