Multiple sub form problem (2 Viewers)

[RaptorRaptur]

Hi All,
I'm using Access 2016
I am trying to build a "dashboard" that in concept looks if it might be a spreadsheet. Continuous forms won't work for me due to the various formatting needs I have. A Single form will not work due to the restriction of numbers of controls on a form. So I plan to have a Main form with 16 subforms each one line (record)
My problem is that I want to select from a list of records by using filters and then displaying the first 16 on my "board" My problem is that no matter what I do I am tending to get 16 instances of the first record.
Has anyone out there tried anything similar and succeeded? If so, pl;ease could I have some help.
Thanks,
Alan

 

[RonPaii]

You could use a table with all your commands then use a cross tab query to spread them as wide as you want as the source of your dashboard form. The on click event would get the current command and call you programed actions.

 

[plog]

There's really no general help for this, it's gonna have to be specific to your instance. That means we need details. Preferably a sample of your database and a mock up of what you expect your dashboard to look like when finished.

 

[mike60smart]

Hi All,
I'm using Access 2016
I am trying to build a "dashboard" that in concept looks if it might be a spreadsheet. Continuous forms won't work for me due to the various formatting needs I have. A Single form will not work due to the restriction of numbers of controls on a form. So I plan to have a Main form with 16 subforms each one line (record)
My problem is that I want to select from a list of records by using filters and then displaying the first 16 on my "board" My problem is that no matter what I do I am tending to get 16 instances of the first record.
Has anyone out there tried anything similar and succeeded? If so, pl;ease could I have some help.
Thanks,
Alan

As Plog said we need a copy of your database plus an example of your dashboard requirements.

 

[isladogs]

Please explain this sentence. Why does formatting mean continuous forms won't work for you?

Continuous forms won't work for me due to the various formatting needs I have.

 

[The_Doc_Man]

A Single form will not work due to the restriction of numbers of controls on a form.

This statement alone suggests something is incredibly wrong with this design. Nominally, you are saying you need over 768 controls (maybe that limit is more these days, but old MSFT online documents say 768.) You can only have 255 fields in a record, which means if each field in the underlying record was maxed out, you could have 3 controls per field plus 3 unbound controls. This is an astounding statement because it implies that your business has over 750 possible things to do to one record.

Operationally speaking, it sounds like you would need professional tech writers to write the user's manual for this beast, plus a wheelbarrow for every (paper) printed copy of the document. Can you explain the work situation a little better? Because I can't wrap my mind around having 750 things you could do with a single record.

 

[Mark_]

If you can't share the database for any reason, can you at least show us a mockup with description of what you need?

 

[Gasman]

Could you use a calendar approach?

 

[Edgar_]

If I'm not mistaken, you added 16 subform controls to an unbound form, and then you're trying to filter with a search mechanism. You're getting the same record multiple times because you have not given the subforms any means to know which record you wish to display, so they display the first record in their recordset, which is their default behavior.

Try the attached example and see if it helps.

What I'm doing here is turn this:

Into this:

Here's the code used

Option Compare Database
Option Explicit

Private Sub LoadResults()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sf As Control
    Dim sql As String
    Dim searchVal As String

    searchVal = Nz(Me.txtSearch.Value, "")

    sql = "SELECT * FROM stats " & _
          "WHERE Explorer LIKE '*" & searchVal & "*' " & _
          "ORDER BY StatID"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql)

    For Each sf In Me.Controls
    
        If sf.ControlType = acSubform Then
        
            If Not rs.EOF Then
            
                sf.Visible = True
                sf.Form.Filter = "Explorer='" & rs!Explorer & "'"
                sf.Form.FilterOn = True
                
                rs.MoveNext
                
            Else
            
                sf.Form.FilterOn = False
                sf.Visible = False
                
            End If
            
        End If
        
    Next sf

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Private Sub Form_Load()
    Me.txtSearch = ""
    LoadResults
End Sub

Private Sub txtSearch_AfterUpdate()
    LoadResults
End Sub

 

[Ken Sheridan]

My problem is that no matter what I do I am tending to get 16 instances of the first record.

That's overcome easily enough. You just need to base the subforms on a query which sequentially numbers each row, and then restrict each subform to where the serial number = 1, 2, 3...16. The following is an example of such a query:

SELECT
    COUNT(*) AS RowNumber,
    T1.TransactionDate,
    T1.CustomerID,
    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)
GROUP BY
    T1.TransactionDate,
    T1.TransactionID,
    T1.TransactionAmount,
    T1.CustomerID;

The above query numbers the rows in transaction date order, and uses the primary key as the tie breaker if two or more transactions are on the same date. If the rows can be ordered on distinct values the tie breaker can be omitted.

To restrict the rows you'd simply need to add a HAVING clause, e.g.

HAVING COUNT(*) = 1
HAVING COUNT(*) = 2
and so on