assigning a subdatasheet to a table via VBA

Question

I am trying to assign and change the subdatasheet for a specific table via VBA code. The tips a found are all for setting the subdatasheet to "NONE" for all tables.

However, I am looking to do the opposite by programmatically (VBA code) setting a specific table as the subdatasheet to another table.

Any help is greatly appreciated

Answer 1

That is the answer I was looking for :)

Thank you

Answer 2

A subdatasheet represents the referencing table in a one-to-many relationship type in which the main datasheet represents the referenced table in the relationship. To create one in VBA, therefore, you need to create the relationship. This can be done with the DAO CreateRelation method. Take a look at the following topic on the method, which includes a detailed example:

https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-createrelation-method-dao?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(dao360.chm1052969)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Answer 3

I have already assigned these one-to-many relationships between the tables. However, depending on situation I would like to change which subdatasheet is shown for a given table (automating the command sequence Home - More - Subdatasheet - Subdatasheet....

Thank you

Answer 4

You are looking for the SubdatasheetName property to set:

CurrentDb.TableDefs("tblParent").Properties("SubdatasheetName").Value = "Table.tblChild"