Access Subforms (Single Form)

Question

I'm inserting a subform that has more than one record related to the main form.

Is there a way to automatically copy and paste the subform showing all records until all records for the recordset have been shown?  This is a single form view, and I do not want to show record navigators or include buttons.  I just want a simple copy and paste if next record exists.

Can the subform be invisible if no records exist?

Answer 1

So you want a continuous Subform1 containing one or more continuous Subform2, one sub-subform in each record of Subform1? Not easy!

If the purpose is just to display (not edit) data, I'd suggest instead using a Report. You can use the Sorting and Grouping capability of reports to display a Subreport in the Header section of a group.

If you need both the multilayer display and editability, it gets snarky. You may need to have five (or however many) Subforms, based on queries selecting the first, second, third... records of the mainform's Recordsource, and use code to toggle their visibility

In any case, this sounds like an awfully visually busy display - it's going to be hard to read and interpret, and/or pretty hard to set up. Good luck!

I've tried using reports for this, and they work great except they take forever to format.  (Insert subreport into main form).  I'm going to mess around with a couple of other options to see if I can get something to work.

Answer 2

So you want a continuous Subform1 containing one or more continuous Subform2, one sub-subform in each record of Subform1? Not easy!

If the purpose is just to display (not edit) data, I'd suggest instead using a Report. You can use the Sorting and Grouping capability of reports to display a Subreport in the Header section of a group.

If you need both the multilayer display and editability, it gets snarky. You may need to have five (or however many) Subforms, based on queries selecting the first, second, third... records of the mainform's Recordsource, and use code to toggle their visibility

In any case, this sounds like an awfully visually busy display - it's going to be hard to read and interpret, and/or pretty hard to set up. Good luck!

Answer 3

It was explained to you in this thread in the MSDN Access forum that to do what you are attempting would involve adding multiple subforms to the parent form, giving each a separate RecordSource property which returns a single record.  This would mean that the number of records shown would be finite, and you would need to write a query which would return the first, second etc record from the subset of records in the referencing table which relate to the parent form's current record.

As you say there could be up to 5 referencing rows, adding 5 subforms to the parent form would presumably be adequate.  As regards retuning the separate records there are SQL techniques which can number rows distinctly, so you could then restrict each subform's query to where the computed number = 1, 2, 3 etc.

You'll find examples of row numbering queries in RowNumbering.zip in my public databases folder at:

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

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.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In my demo the qryNumberRowsByGroup_Join query numbers rows per customer as a non-updatable result table:

SELECT COUNT(*) AS RowNumber, T1.CustomerID, T1.TransactionDate, T1.TransactionAmount

FROM Transactions AS T1 INNER JOIN Transactions AS T2

ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)

AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID)

GROUP BY T1.CustomerID, T1.TransactionDate, T1.TransactionAmount, T1.TransactionID;

So, if I were to create a Customers parent form I could embed five subforms within it, each identical apart from their RecordSource property.  The RecordSource property for the first would be:

SELECT *

FROM qryNumberRowsByGroup_Join

WHERE RowNumber =1;

That for the second would be:

SELECT *

FROM qryNumberRowsByGroup_Join

WHERE RowNumber =3;

That for the third:

SELECT *

FROM qryNumberRowsByGroup_Join

WHERE RowNumber =3;

and so on.  Each subform would be linked to the parent form on CustomerID.

As I said, the above query returns a non-updatable result table (as a result of using the COUNT aggregation operator).  The demo also contains alternative queries which call the VBA DCount function to return an updatable result table, so if updatable subforms are needed then the following qryNumberRowsByGroup_Updatable query could be used rather than the qryNumberRowsByGroup_Join query:

SELECT DCOUNT("*","Transactions","CustomerID = " & CustomerID & "

AND TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#

AND (TransactionID <= " & TransactionID & "

OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)") AS RowNumber,

CustomerID, TransactionDate, TransactionAmount

FROM Transactions

ORDER BY CustomerID, TransactionDate, TransactionID;

It should not be difficult for you to replicate one or the other of these queries with your own table.  In the above queries the rows are returned in transaction date order per customer, with the primary key TransactionID used as the tie-breaker in the event of two or more transactions by the same customer taking place on the same date.   If the order in which the rows would be returned per customer were unimportant, however, then the criteria could be simplified to:

SELECT DCOUNT("*","Transactions","CustomerID = " & CustomerID & "

AND  TransactionID <= " & TransactionID) AS RowNumber,

CustomerID, TransactionDate, TransactionAmount

FROM Transactions

ORDER BY CustomerID, TransactionID;

The same would be true of the JOIN criteria in the qryNumberRowsByGroup_Join query.  If the subforms are not to be updatable a JOIN is much more efficient than calling the DCount function.

Answer 4

I'm not visualizing what you want here. If you want to see all records, why not use a Continuous Form as the subform (and turn off the navigation buttons if you wish)? Copying and pasting a Form is certainly not a good way to go! And what do you want to see if there are more records on the subform than will fit on the screen? Could you explain the context and what you want the user to see?

Main Form=Record Source is Table

Subform 1=Record Sources is Table

Subform 2 (inside of Subform 1)=Record Sources is Query (Continuous Form)

Answer 5

I'm not visualizing what you want here. If you want to see all records, why not use a Continuous Form as the subform (and turn off the navigation buttons if you wish)? Copying and pasting a Form is certainly not a good way to go! And what do you want to see if there are more records on the subform than will fit on the screen? Could you explain the context and what you want the user to see?