none
How display 12 records on a form at a time RRS feed

  • Question

  • I have a subform currently that is in continuous form mode.  But there will be up to 36 records to display and we don't want to display all 36 of them in one continuous form.

    So I thought I would create 3 command buttons on the main form.  The first command button will display the first 12 records, the 2nd command button would display the second 12 records, and the 3rd Command button would display the third 12 records. 

    Please note that is some cases there may be less than 36 records total so if  for example there were 20 records, the button would display 12 records, the 2nd button would display 8 records and the 3rd button would display none.

    Each record has a date field and the records will need to be sorted in date order.
    How can I accomplish this?

    Steve C. Leigh

    Tuesday, October 6, 2015 8:03 PM

Answers

  • Well now my issues is this.  The code and query works fine for just one Provider.  But if I add a 2nd provider nothing shows up.


    I'd guess this is because the ProviderEventID values are not separate subsets for each provider, starting at 1 in each case.   You have a number of options:

    1.  Use variations on another query from my RowNunbering demo to which I gave you a link earlier, in this case numbering the rows independently per customer:

    SELECT *
    FROM Transactions
    WHERE  DCOUNT("*","Transactions","CustomerID = " & CustomerID & "
    AND TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#
    AND (TransactionID <= " & TransactionID & "
    OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)")
    BETWEEN 1 AND 12
    ORDER BY CustomerID, TransactionDate, TransactionID;

    2.  The above query is updatable.  I you don't require an updatable query a JOIN is more efficient:

    SELECT 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
    HAVING COUNT(*) BETWEEN 1 And 12;

    3.  Compute the ProviderEventID value per provider when a row is inserted.  The CustomNumber demo in my same OneDrive folder gives an example of sequential numbering per group, by gender in my file.  You could do so by provider.  The ParentActivities demo does similarly, sequentially numbering each activity entered per parent, but unlike the CustomNumber demo, without any provision for handling conflicts in a multi-user environment.

    If you numbered the rows per group in this way you could use your current approach.  However, in the last query you posted I don't see any foreign key which references the primary key of a Providers table, and which would be used as the LinkChildFields property of a subform within a providers parent form.


    Ken Sheridan, Stafford, England

    Thursday, October 8, 2015 6:03 PM

All replies

  • I would use an Option Group instead of 3 command buttons.

    Use this query that has unique data such as an autonumber field instead of my [Member ID] –-

    SELECT Activities.[Member ID], Sum(IIf([YY].[Member ID]<=[Activities].[Member ID],1,0))\12 AS GroupNUM, Sum(IIf([YY].[Member ID]<=[Activities].Member ID],1,0)) Mod 12 AS NUMinGroup

    FROM Activities, Activities AS YY

    GROUP BY Activities.[Member ID]

    ORDER BY Activities.[Member ID], Sum(IIf([YY].[Member ID]<=[Activities].[Member ID],1,0))\12, Sum(IIf([YY].[Member ID]<=[Activities].[Member ID],1,0)) Mod 12;

    Option Group number to match GroupNUM as criteria.  Use a Refresh On Change of the Option Group.



    Build a little, test a little


    Tuesday, October 6, 2015 9:13 PM
  • If you want the form to be updatable you'll need to call the DCount function rather than the SQL SUM or COUNT operators.  You'll find examples of various row numbering queries in RowNumbering.zip in my public databases folder at:

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

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

    If one of my queries in this little demo file is amended as follows:

    SELECT *
    FROM Transactions
    WHERE  DCOUNT("*","Transactions","TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#
    AND (TransactionID <= " & TransactionID & "  OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)")
    BETWEEN 1 AND 12
    ORDER BY TransactionDate, TransactionID;

    it would return the first 12 rows, while

    SELECT *
    FROM Transactions
    WHERE  DCOUNT("*","Transactions","TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#
    AND (TransactionID <= " & TransactionID & "  OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)")
    BETWEEN 13 AND 24
    ORDER BY TransactionDate, TransactionID;

    would return the next 12, and so on.

    Using the AfterUpdate event procedure of an option group as suggested by Karl, or some similar control for selecting the 'band', you could either rebuild the SQL statement for the subform's RecordSource property in code, or you could use parameters in the query which, for instance, reference hidden columns in a combo box in which the 'band' is selected, in which case you'd simply Requery the subform in the control's AfterUpdate event procedure.


    Ken Sheridan, Stafford, England


    Wednesday, October 7, 2015 1:42 PM
  • I ended up with an option group:

    Private Sub optgroupSelectYear_Click()
     
         Dim subVal As Variant
         Dim savVal As Variant
         Dim UserChoice As Variant
     
         UserChoice = [optgroupSelectYear].Value
     
         If UserChoice = 1 Then
             Me.RecordSource = "qryProviderEventsYear1"
             Me.Refresh
         ElseIf UserChoice = 2 Then
             Me.RecordSource = "qryProviderEventsYear2"
             Me.Refresh
         ElseIf UserChoice = 3 Then
             Me.RecordSource = "qryProviderEventsYear3"
             Me.Refresh
         End If
    End Sub

    With this as a query sql example:

    SELECT tblProviderEvents.EventDate, tblLocationsLU.Location, tblProviderEvents.ProviderEventID
    FROM tblProviderEvents LEFT JOIN tblLocationsLU ON tblProviderEvents.Location = tblLocationsLU.LocationID
    GROUP BY tblProviderEvents.EventDate, tblLocationsLU.Location, tblProviderEvents.ProviderEventID, tblProviderEvents.ProviderID, tblProviderEvents.LicenseID
    HAVING (((tblProviderEvents.ProviderEventID) Between 1 And 12) AND ((tblProviderEvents.ProviderID)=[Forms]![frmProviders]![txtProviderID]))
    ORDER BY tblProviderEvents.EventDate, tblProviderEvents.ProviderEventID;


    Steve C. Leigh

    • Proposed as answer by Tony---- Thursday, October 8, 2015 3:30 AM
    Wednesday, October 7, 2015 8:22 PM
  • As you are not aggregating any values you do not need to group the query.  Even if you were aggregating any values the use of a HAVING clause is incorrect as you want to restrict the result set before grouping, not after grouping.  A HAVING clause does the latter and is used for restrictions on the basis of aggregated values, e.g. all customers who have made orders totalling 10,000 GBP or more.  So the following will suffice:

    SELECT tblProviderEvents.EventDate, tblLocationsLU.Location,
    tblProviderEvents.ProviderEventID
    FROM tblProviderEvents LEFT JOIN tblLocationsLU
    ON tblProviderEvents.Location = tblLocationsLU.LocationID
    WHERE tblProviderEvents.ProviderEventID BETWEEN 1 AND 12
    AND tblProviderEvents.ProviderID = [Forms]![frmProviders]![txtProviderID]
    ORDER BY tblProviderEvents.EventDate, tblProviderEvents.ProviderEventID;

    Ken Sheridan, Stafford, England

    Wednesday, October 7, 2015 9:36 PM
  • Well now my issues is this.  The code and query works fine for just one Provider.  But if I add a 2nd provider nothing shows up.  The reason is that I have three queries.  One shows the first 12 records, the 2nd one shows the 2nd 12 records, and the 3rd one shows the 3rh set of 12 records.

    Its still not working if I add another provider.


    Steve C. Leigh

    Thursday, October 8, 2015 12:38 PM
  • Well now my issues is this.  The code and query works fine for just one Provider.  But if I add a 2nd provider nothing shows up.


    I'd guess this is because the ProviderEventID values are not separate subsets for each provider, starting at 1 in each case.   You have a number of options:

    1.  Use variations on another query from my RowNunbering demo to which I gave you a link earlier, in this case numbering the rows independently per customer:

    SELECT *
    FROM Transactions
    WHERE  DCOUNT("*","Transactions","CustomerID = " & CustomerID & "
    AND TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#
    AND (TransactionID <= " & TransactionID & "
    OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)")
    BETWEEN 1 AND 12
    ORDER BY CustomerID, TransactionDate, TransactionID;

    2.  The above query is updatable.  I you don't require an updatable query a JOIN is more efficient:

    SELECT 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
    HAVING COUNT(*) BETWEEN 1 And 12;

    3.  Compute the ProviderEventID value per provider when a row is inserted.  The CustomNumber demo in my same OneDrive folder gives an example of sequential numbering per group, by gender in my file.  You could do so by provider.  The ParentActivities demo does similarly, sequentially numbering each activity entered per parent, but unlike the CustomNumber demo, without any provision for handling conflicts in a multi-user environment.

    If you numbered the rows per group in this way you could use your current approach.  However, in the last query you posted I don't see any foreign key which references the primary key of a Providers table, and which would be used as the LinkChildFields property of a subform within a providers parent form.


    Ken Sheridan, Stafford, England

    Thursday, October 8, 2015 6:03 PM
  • I have a subform currently that is in continuous form mode.  But there will be up to 36 records to display and we don't want to display all 36 of them in one continuous form.

    So I thought I would create 3 command buttons on the main form.  The first command button will display the first 12 records, the 2nd command button would display the second 12 records, and the 3rd Command button would display the third 12 records. 

    Please note that is some cases there may be less than 36 records total so if  for example there were 20 records, the button would display 12 records, the 2nd button would display 8 records and the 3rd button would display none.

    Each record has a date field and the records will need to be sorted in date order.
    How can I accomplish this?

    Steve C. Leigh

    I'm just wondering why it's so important to show only 12 records at a time instead of listing all records?  This isn't a web page you're building, so I can't imagine your purpose for all the effort here.  36 records or less certainly isn't a strain on the system by any means.

    Thursday, October 8, 2015 9:31 PM