Link two subforms (datasheet view and form view) to the same record in the main form

[Fazolator]

For starters, I'm not asking the usual question about how to link the records in a subform to the main form. I know how to do that by linking Master and Child fields. The problem I'm trying to solve is that Access won't allow split form view on a sub-form.

A scenario: Assume there are two tables, like this:
Table1 - Invoices
InvoiceNumber as String
Lots of other fields

Table2 - InvoiceLineItem s
InvoiceNumber as string
ItemNr as Long
Lots of other fields

I want to display a form that looks like this:

╔═══════════════════════════════╦═════════════════════════════════╗
║  MAIN FORM - SPLIT VIEW       ║  SUBFORM (SAME FORM USED TWICE  ║
║ .-------------------------.   ║     .---------------------.     ║
║ | AREA 1 (form view)      |   ║     | SUBFORM-Datasheet   |     ║
║ | InvoiceNumber           |   ║     | ItemNr = 1          |     ║
║ | Lots of other fields    |   ║     | ItemNr = 2          |     ║
║ '-------------------------'   ║     '---------------------'     ║
║ .-------------------------.   ║     .----------------------.    ║
║ | AREA 2 (datasheet)      |   ║     | SUBFORM-FormView     |    ║
║ | Line 1                  |   ║     | Field-1   Field-2    |    ║
║ | Line 2 (etc)            |   ║     | Field-3   Field-4    |    ║
║ '-------------------------'   ║     `----------------------'    ║
╚═══════════════════════════════╩═════════════════════════════════╝

Desired operation:
1) Click on an invoice in the datasheet view (Area 2)
2) Corresponding invoice data appears in Area 1
3) The data from InvoiceLineItem s should appear in the subform-Datasheet
4) Click on a row in Subform-Datasheet - details should display in the Subform-FormView

I'm basically trying to emulate what would happen if Access allowed split display in a subform. To be clearer, the Invoice-Item is the same in both subforms. It's the display mode that changes.

I'm using Windows 10 and Access 2013.
Upgrading to different version of Access is not an option.
Installing any third-party add-ons is not an option
I am allowed to add code and am allowed to use system functions.

[NeoPa]

Interesting. And question nicely laid out and prepared.

So you want/have - a list of invoices; a single invoice in detail; a list of invoice lines; an invoice line in detail. Essentially the full ability to navigate your invoices from top to bottom.

May I ask how flexible your approach can be? My instinct would be start with a main form where the invoice lines are shown in the detail section but shown in Continuous Forms mode. The Header section could/would include various options for filtering the list. From there a linked Sub-Form would reflect the details of the invoice record selected in the main form. Again, the Header would show all the specifics and the Detail lines would include the invoice lines - again in continuous mode. Either the main form, or the sub-form, could then have a separate Sub-Form showing the full detail of the invoice line selected.

Does that sound like it could work for you? Or are your requirements too specific for that?

A point to bear in mind :
Any Form object, whether linked as a Sub-Form or stand-alone, can have filtering specified to your requirements. Linking Fields & Controls are there to make life easier for you but with code you can make the Forms show whatever you want by filtering and setting DefaultValues for Controls if creating new linked records.

[isladogs]

The built in Access split form has many drawbacks.
For that reason, I led a group of developers who collaborated to create an alternative with the same features but without its disadvantages.
You can find that alternative version here: Emulated Split Form.
Have a look and see whether it will allow you to do what you want.
If not, come back and I'll try to assist in other ways

[Fazolator]

NeoPa, Thanks for the insights! My issues aren't with the linkings, but with the display. If an Access subform could be displayed in split-form mode I would have been done days ago :-).
That said, I'm going to try your idea for the main form on the sub-form instead... that is, display the subform as a continuous datasheet and put the displayed fields in the subform's header.
BTW, the reason for the rather tortuous display is simple: The user needs to see all of the data for the invoice and the specific line item at the same time, but the screen does not have enough space to display all of the fields in the line item detail in the datasheet. (There are about 85 fields to display, plus another 35 for the invoice.)
I will post again once I've tried your idea. Thanks again!

[Fazolator]

isladogs, thanks for the reply. I like your emulated split form and no doubt will use it in the future. I'm going to try NeoPa's solution first as it will not require as much recoding. If that does not work, I'll check out your emulated subform solution. Again, thanks!

[isladogs]

No problem. For info, the ESF requires almost no coding if you do decide to use it

[Fazolator]

NeoPa, well, it turns out that Access suppresses the display of the form header, too. It's frustrating.

[NeoPa]

Fazolator:
NeoPa, well, it turns out that Access suppresses the display of the form header, too. It's frustrating.

Perhaps you could elaborate on that last comment. It's not been my experience. I've used similar setups before. Is it possible the Header is hidden for some other reason that you're in control of maybe?

Like IslaDogs, I'm no big fan of Split Forms. I just don't see the case for them when the judicious use of Sub-Forms can give you similar results.

[isladogs]

Just re-checked.
The emulated split form (ESF) works fine in a subform:





Although I've used a continuous form in the lower part, it works equally well with a datasheet if preferred