Share via

Displaying records in datasheet view together with related subforms

Anonymous
2023-05-05T12:29:14+00:00

Hi all,

I am trying to do a little Access project and I am encountering some difficulties. I would appreciate any help with this!

I have a table Sources with a one-to-many relationship with table ImpactsOnPillars, which in turn has a one-to-many relationship with table InformationControl.

The whole idea is that reports come in and I keep track of them in Sources. Each report can contain events that have an impact in the "pillars' (these are kind of rules). These impacts will be notified to certain people and this is tracked in InformationControl.

The relationships look like this then (the other two tables are not important):

I would like to have a form where all records from ImpactsOnPillars are displayed (strongly preferably in datasheet view, to apply filtering). When a record from that table is selected, its corresponding parent report from Sources should be shown, together with the corresponding records from InformationControl.

I have tried different approaches and only one is working so far: using the wizard, I create a split form with Record Source ImpactsOnPillars. Text boxes for every field appear automatically so make then not visible. Over these hidden boxes I place subforms for Sources and InformationControl, with the adequate Link Master/Child Field property so that they talk to the selected record in the datasheet half of the form. This achieves what I need, but it seems to me it's a very inelegant solution.

Is there a better way to do this?

Cheers!

These have been my unsuccessful attempts:

  • Create a datasheet view form with Record source ImpactsOnPillars. I can't display any subforms containing Sources or InformationControl in datasheet view, so this doesn't work.
  • Create a tabular view form with Record source ImpactsOnPillars so that it kind of looks like datasheet view. However I can't use a continuous form together with a subform, so this doesn't work.
  • Create a blank form with three subforms - one for each table - and try to get them to speak to each other via Link Master/Child Fields. This doesn't work either
  • A different approach consisting on a query which displays records from ImpactsOnPillars and Sources, with InformationControl as subdatasheet. However, when interacting with the query in datasheet view and expanding the subdatasheet, all the records in InformationControl are shown, instead of only the ones linked to each record in ImpactsOnPillars.
Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-09T10:03:51+00:00

    Thanks for the video! It helps greatly.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-05-09T09:42:08+00:00

    Thanks for all the suggestions! I have tried the continuous form + subform method as explained in dhookom's post and it works!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-05-05T14:49:33+00:00

    You might like to take a look at CorrelatedSubs.zip in my public databases folder at: 

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169 

    If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

    This little demo file includes an illustration of how to correlate two subforms.  In the demo the subforms are in continuous forms view, but could be changed to datasheet view if preferred.  The second subform is correlated with the first by referencing its key as a parameter.  The second subform is then requeried in the Current event procedure of the first.

    Was this answer helpful?

    0 comments
  4. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2023-05-05T13:58:55+00:00

    I think what you are looking for (at least what is possible) can be found in a nice YouTube video Nested Subforms by Richard Rost.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2023-05-05T13:20:22+00:00

    Hello

    I am Abdal and I would be glad to help you with your question.

    Your solution of using a split form with subforms is actually a common approach for displaying related records in Access, so it's not inelegant at all! However, if you want to explore other options, here are a few suggestions:

    1. Use a continuous form with subforms: You mentioned that you couldn't use a continuous form with a subform, but it is actually possible. You can create a continuous form for ImpactsOnPillars and add a subform for Sources and InformationControl in each row of the continuous form. To do this, you need to set the Link Master/Child Fields for each subform to the appropriate fields in ImpactsOnPillars.
    2. Use a query with subqueries: You can create a query that joins the three tables and then use subqueries to display the related records. For example, your main query could be based on ImpactsOnPillars and include the relevant fields from Sources. You can then create two subqueries to display the related records from InformationControl and link them to the main query using the Link Master/Child Fields property.
    3. Use VBA code: You can also use VBA code to display the related records. For example, you can use a listbox or a combobox to display the records from ImpactsOnPillars and use VBA code to populate textboxes or subforms with the related records from Sources and InformationControl.

    Overall, using subforms with a split form or a continuous form is probably the easiest and most common approach, but the other options can be useful if you have specific requirements or want more control over the layout and behavior of the form.

    I hope this information helps.

    Regards,

    Abdal

    Was this answer helpful?

    0 comments