472,805 Members | 862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

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

4 Nibble
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 - InvoiceLineItems
InvoiceNumber as string
ItemNr as Long
Lots of other fields

I want to display a form that looks like this:
Expand|Select|Wrap|Line Numbers
  1. ╔═══════════════════════════════╦═════════════════════════════════╗
  3. ║ .-------------------------.   ║     .---------------------.     ║
  4. ║ | AREA 1 (form view)      |   ║     | SUBFORM-Datasheet   |     ║
  5. ║ | InvoiceNumber           |   ║     | ItemNr = 1          |     ║
  6. ║ | Lots of other fields    |   ║     | ItemNr = 2          |     ║
  7. ║ '-------------------------'   ║     '---------------------'     ║
  8. ║ .-------------------------.   ║     .----------------------.    ║
  9. ║ | AREA 2 (datasheet)      |   ║     | SUBFORM-FormView     |    ║
  10. ║ | Line 1                  |   ║     | Field-1   Field-2    |    ║
  11. ║ | Line 2 (etc)            |   ║     | Field-3   Field-4    |    ║
  12. ║ '-------------------------'   ║     `----------------------'    ║
  13. ╚═══════════════════════════════╩═════════════════════════════════╝
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 InvoiceLineItems 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.
Mar 11 '21 #1
8 2840
32,534 Expert Mod 16PB
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.
Mar 11 '21 #2
437 Expert Mod 256MB
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
Mar 11 '21 #3
4 Nibble
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!
Mar 11 '21 #4
4 Nibble
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!
Mar 11 '21 #5
437 Expert Mod 256MB
No problem. For info, the ESF requires almost no coding if you do decide to use it
Mar 11 '21 #6
4 Nibble
NeoPa, well, it turns out that Access suppresses the display of the form header, too. It's frustrating.
Mar 11 '21 #7
32,534 Expert Mod 16PB
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.
Mar 11 '21 #8
437 Expert Mod 256MB
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
Attached Images
File Type: png Capture.PNG (28.5 KB, 1025 views)
File Type: png Capture2.PNG (32.0 KB, 892 views)
Mar 11 '21 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: iggie | last post by:
Hello, everyone. How can I display text from another form that currently has the focus. I'm doing a search in sql 2000, to dot it. I call a search form and query my criteria, when it founds the...
by: Hasanain F. Esmail | last post by:
Hi All, I am using A2 for this database. I have a main form with bound fields of FlightNumber and FlightDate I also have a subform. This subform takes details of a passanger along with other...
by: sasan3 | last post by:
when I set visible=false, then entry is not visible in form view, but still shows up in datasheet view. How do I make a field not to show based on some condition in datasheet view? Tx.
by: Randy | last post by:
The following code, which runs when I click File->Save in my main form, finds my save button in my mdi child for just fine. The question is: how do I invoke the Save method in the child form? ...
by: HyBry | last post by:
I am quite new to this and my VB knowledge is close to zero. I have created a for that shows info about applications and it includes a subform with all the people responsible for the application....
by: Bface | last post by:
Hi All, I am stuck on a problem and hope someone can help me out. I have 8 users who will be using a form . For the users to access their accounts I use the SendKeys function, the user click on...
by: Alan Forsyth | last post by:
That rather long subject says it. In Access97 running on XP - I want to display a Maximized size Form01, then open a Restore size Datasheet View Form02 on top of the first Form. PopUp won't give...
by: mdzeier | last post by:
I need to know how to maxinimze subforms to the size of the main form. I have managed to do the code to maximize the main form, but it does not work on the subforms. The main form contains 6...
by: kelsey22 | last post by:
Good Day! It's my first question to ask here in bytes. I'm having difficulties about my search button. I have a main form with a sub form containing our records. On the main form is a text box and...
by: irsmalik | last post by:
Hello friends. I am making a form to Select Some Records and then send it to Print. I want to use a Form in DataSheet View. Using ComboBox for Selecting a Record from thousands of records. ...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.