MS Access 2010, How to show fields/controls from a subform in a split form - datasheet view

Question

Hello,

I am unable to see fields that I created in a subform show up in the datasheet portion of a split form. I created the original split form from a table (table 1) and then added controls that are not linked to any table (they are just used for basic expressions). From there I added a Tab Control and a subform.  That subform came from a separate form that I built around a different table (table 2).

I don't want to add fields to the form (because they are already on there in the subform) I just want them to appear in the data sheet view.

I've attached a screen shot with the form in layout view. You can see that the controls on the top part of the form (not in subform) DO appear in the data sheet view. It's only those fields/controls that appear in the tabbed subfrom that do not appear in the datasheet view.

Any help is certainly appreciated!

Answer 1

And they are not going to. A split form displays only the fields in the forms Recordsource. You would need to use a split form as your subform, to see the fields in the subform in datasheet view. Either that or use a query joining the two tables as the Recordsource. And if the relationship is one to many, that will defeat the purpose of the subform.

Answer 2

Scott's final sentence, 'And if the relationship is one to many, that will defeat the purpose of the subform', is the crux of the matter.  If the relationship type is one-to-many then basing the split form on a query which joins the two tables would allow you to exclude the unwanted additional columns from the parent form, showing them only in the datasheet, but would return duplicate records in each case where there is more than one row in the referencing table for a row in the referenced table.

A solution in that context would be to use a conventional form/subform, the latter in continuous forms view, to simulate a split form, basing both on the referenced table in the relationship.  You'll find an example in FindRecord.zip in my public databases folder at:

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

You could then correlate a second subform based on the referencing table, also in continuous forms view, with the parent form.  You'll find an example of correlated subforms as CorrelatedSubs.zip in the same OneDrive folder.

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.