Linking Subdatasheets to Main Table

[abcdef]

I have a main table and multiple other tables that I want to link to each row of the main table.

The main table "Data" consists of columns (Name, x, y) where "Name" is the primary key and all values are unique.

Each of the other tables have columns (Name2, z) where the value of "Name2" is the same in each row and also corresponds to the table name.

I want to make each table a subdatasheet of "Data" where each row in "Data" shows the values in the table corresponding to its name. (i.e. where Name = Name2)

Below is what I have so far, but the code doesn't work because linkchildfields and linkmasterfields need to be run from a subform.(?) I'm new to Access so can anyone please help me set these subforms up or show me how this can be done?

The error I am getting is 'Property Not Found' once the code reached the linkchildfields line.

Thanks.

Sub STS()

Dim i As TableDef
Dim db As Database
Dim tbl As TableDef

Set db = CurrentDb()
Set tbl = db.TableDefs("Data")

For Each i In db.TableDefs
If Left$(i.Name, 4) <> "MSys" Or i.Name <> "Data" Then
tbl.Properties("SubdatasheetName") = i.Name
tbl.Properties("LinkChildFields") = "Name2"
tbl.Properties("LinkMasterFields") = "Name"
End If
Next
End Sub

 

[jdraw]

How about a few sentences overviewing WHAT you are trying to do in business terms?

 

[abcdef]

Thanks for the reply.

I basically have multiple tables that I want linked to a main table.
The database is used for viewing purposed, and I would like to be able to open the corresponding subdatasheet for any row in the main table by clicking the + sign.

Thanks

 

[jdraw]

Sounds like you want to be viewing a Form, selecting some entry, then opening a subform for detailed info. Is that close?

You don't let users access tables directly. You manage your data (stored in tables) by creating an interface (form(s)) that control what a user can and can not do.

 

[abcdef]

Yes, I think that sound right. Would I need to make a subform for each of the sheets to be inserted into the main form?
How would I code this through vba?

Thanks

 

[jdraw]

Suggest you look to youtube for MsAccess Form and Subform

Good luck

 

[abcdef]

Hi jdraw,

I looked into Forms and Subforms and I'm not sure that's what I'm looking for.
I will be the only user of the database and all of the data is already being imported from excel.

To put my question simply, I am looking for a way to automate the process of setting one table as a Subdatasheet of another. I could do this manually directly from the access table, but I want to automate it for when a new table is created and needs to be set as a subdatasheet of the main table.

Any advice on how this could be done?

Really appreciate your help

 

[Galaxiom]

The properties you seek don't exist. Look at the Object Browser window in VBA.

Alternatively, list the properties of a table with something like this in the Immediate Window:

set db = Currentdb: for each prpty in db.TableDefs("table1").properties:debug.Print prpty.name:next

Automation in Access is aimed at Forms and Reports. As jdraw suggested, that is what you need to be using.

 

[abcdef]

Thanks Galaxiom,

You're right that the LinkChildFileds and LinkMasterFields don't exist. If I run the code without those 2 lines (so just update the Subdatasheetname), the code runs through all the tables and links them like I want, but changes the Subdatasheet to only the table from the last iteration.

Does this mean it's impossible for a table to have more than one other table as a subdatasheet? I'm asking this because the Subdatasheetname field can only contain one table name, so would have to remove the link after every iteration.

I tried merging all of the other sheets into one large sheet, and set that one as the Subdatasheet, and it works by just changing the Subdatasheet name. However, the sheet will be updated regularly, so I would prefer that the data all come from multiple tables.

 

[jdraw]

I'm suggesting you work through the attached tutorial. When you say things like

I am looking for a way to automate the process of setting one table as a Subdatasheet of another. I could do this manually directly from the access table, but I want to automate it for when a new table is created and needs to be set as a subdatasheet of the main table.

my guess is a misunderstanding of tables and concepts.

If you work through the tutorial, you'll see that it isn't you that decides how tables are related, it's your business rules.
Tables are for storing data. Forms and subforms are the way users get to see the data held in tables.

Work through this tutorial, you'll be amazed at what you will learn.

Good luck.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip