assigning a subdatasheet to a table via VBA

Frei Pius 6 Reputation points
2022-09-21T19:20:04.33+00:00

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
830 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Frei Pius 6 Reputation points
    2022-09-23T14:04:19.623+00:00

    That is the answer I was looking for :)

    Thank you

    1 person found this answer helpful.
    0 comments No comments

  2. Ken Sheridan 2,671 Reputation points
    2022-09-22T21:04:05.923+00:00

    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

    0 comments No comments

  3. Frei Pius 6 Reputation points
    2022-09-22T21:11:50.233+00:00

    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

    0 comments No comments

  4. Gustav 712 Reputation points MVP
    2022-09-23T07:54:59.047+00:00

    You are looking for the SubdatasheetName property to set:

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