Displaying records of continuous sub form in new column after every 12 records. (1 Viewer)

[Ashisht76]

Hello Everyone and thanks in advance to everyone who will try to solve my issue.

I have form and continuous subform that will show serial number and for each serial number I have some numbers (Picture attached).

#1 ) Now I want to see this subform having 12 records in each column and next 12 in next column right next to the previous 12,
#2) I want limit number of recorss to be 60 in total so that there will be total 5 column.

I searched and found no answers and hence finally seeking you genius peoples help. Please advise if you have any way out with that . Once again your efforts are highly appreciated and tanks a lot in advance.

 

[ebs17]

limit number of recorss to be 60

SELECT TOP 60 SerialNumb, ReceiveMeter FROM TableX ORDER BY SerialNumb

there will be total 5 column

The two fields shown are not sufficient for a convincing solution via query (e.g. crosstab query).

But you can put 5 subforms next to each other and give them a query as datasource as follows.

SELECT SerialNumb, ReceiveMeter FROM TableX WHERE (SerialNumb - 1) \ 12 = [xx]

The parameter xx would have to be used with 0, 1, ..., 4.

 

[Uncle Gizmo]

I answered a similar question about a year ago and blogged about it on my website here:-

Split a Large MS Access Datasheet in to 3 Smaller Datasheets - Nifty Access

Split a Large MS Access Datasheet in to 3 Smaller Datasheets You've got a large "VERTICAL" List of Data in datasheet form, you might prefer to have it spread out across the screen instead of constantly having to page up and down the list. In this set of videos I demonstrate two ways of implementing

www.niftyaccess.com

Also in Videos:-

1) Split Datasheet in 3 - Nifty Access​

I call it "pagination" as in making separate pages out of the data.

 

[isladogs]

Achieving the 5 subforms is actually quite simple. Karl Donaubauer demonstrated this in his recent presentation to Access Europe.
In fact you use 5 instances of the same subform each with a different record source.
See the Horizontal Continuous form at

Limiting the number of records is also easy. See my article:

Limit the Number of Records

This is a simple application demonstrating how the number of records in a subform can be limited to a specified value

isladogs.co.uk

Or the accompanying video:

 

[Uncle Gizmo]

In fact you use 5 instances of the same subform each with a different record source.

As demonstrated in my videos...

I also have an example which takes list items from tables, and using a data-driven approach automatically fills in the label for each subform, automatically generates the correct record source:-

A Look at the Multiple Checklist Code - Nifty Access​

For more information see my blog:-

Multiple Checklist User Case Examples​

Multiple Checklist User Case Examples - Nifty Access

Multiple Checklist User Case Examples I have set this page up to record user case examples using the the Nifty Multiple Checklist System. Demonstration of another product from Nifty Access... The Nifty Multiple Check-List System. Create a Form with Many Check-Boxes Create a Form with Many...

www.niftyaccess.com

 

[ebs17]

Limit the Number of Records: Limiting the creation of new records is different than limiting the display of existing records.

Whichever approach is used, it is ESSENTIAL that end users are NOT allowed direct access to the tables otherwise they will still be able to add additional records

With a validity rule or a CONSTRAINT, a limit can also be enforced at table level.

 

[Ashisht76]

Wow! I am amazed by getting so many replies and so many options. Thanks a ton everyone for responding. I am checking each and every reply in my database and will come up with result soon.

 

[MajP]

Some questions
1. Is this just for display or also editing and/or entry and/or deletion?
2. Will you limit to 60 records or only displaying 60 at a time?
3. If you can have more than 60 records will you need to navigate to display in groups (1-60, 61-120....) ?
4. If you have to edit, are you fine with clicking on a record and pulling up a popup form or do you want to edit directly?

 

[MajP]

Of course a good custom class can simplify this. If a listbox is acceptable this class can turn any listbox into what I call a DownUpList. I was a doubter, but this actually works better than I thought and it could be useful when you have a long list. Only requires 2 lines of code to make work. The hard part was moving through the recordset displaying the next "page" of records.
Features include:
1. Set the number of columns and number of records in column
2. Pass in the number of columns only and it will fit to a single "page"
3. Allows you to move forward and backward displaying the next batch of records.
4. Allows you to click on any value in any column and return that value.

The image is the 5X10 page 1 (1 to 50 of 75)
The below image is the second page after selecting Next

The below image is a pop up from clicking on the third column

Here is the code to make a listbox have these features

Private DUL As DownUpListbox
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Initialize Demos ---------------------------------------------------------------------------------

Private Sub demo1_Click()
  Set DUL = New DownUpListbox
  DUL.Initialize Me.lstCodes, "qryDisplay", "Display", 5, 10
End Sub

Here is the code to move the pages
Private Sub cmdFirst_Click()
  DUL.MoveFirst
  Me.UpdateDisplay
End Sub

Private Sub cmdNext_Click()
  DUL.MoveNext
  Me.UpdateDisplay
End Sub

Private Sub cmdPrevious_Click()
  DUL.MovePrevious
  Me.UpdateDisplay
End Sub

Private Sub cmdLast_Click()
  DUL.MoveLast
  Me.UpdateDisplay
End Sub

Public Sub UpdateDisplay()
  Me.txtCount = DUL.RecordCountDisplay
End Sub

In the initialize you pass in
DUL.Initialize Me.lstCodes, "qryDisplay", "Display", 5, 10
qryDisplay is the Sorted query
Display is the field to Display in the listbox
5 is the number of columns
10 is records in a column

 

[Ashisht76]

Some questions
1. Is this just for display or also editing and/or entry and/or deletion?
2. Will you limit to 60 records or only displaying 60 at a time?
3. If you can have more than 60 records will you need to navigate to display in groups (1-60, 61-120....) ?
4. If you have to edit, are you fine with clicking on a record and pulling up a popup form or do you want to edit directly?

Hi Sorry for replying you late, I was busy on other projects.
1. I would prefer for Entry, Edit and delete purpose. but I am willing to show it only for display if I cannot achieve that.
2.Its going to be 60 records and 60 records only, not a single more..ever.
3. I believe its irrelevant
4. If I have choice to, I will prefer to edit it directly.

 

[MajP]

I will try to demo.
1. Limiting it to 60 records Total, and not having a requirement to only display and navigate through 60 records at a time makes the solutions actually much easier
2. Doing the five subforms for display and edit is easy and would be the way to go. Without trying it, I am not sure about how the data entry would work.

 

[MajP]

This does what you want. It is also configurable. You can set the number of records to display per subform. Would work with any number of subfrm instances. It is not trivial to do this, so if you want this I can talk you through. I tried to make as reuseable as possible, but you have to ensure the recordsource code is correct.

1. very fast to add, but not in the subform for many reasons
2. can edit
3. You can delete.
4. I made an incrementing serial per fabric. This could instead be an input in the add button and also editable.
5. Cannot add more than 60

 

[Ashisht76]

This does what you want. It is also configurable. You can set the number of records to display per subform. Would work with any number of subfrm instances. It is not trivial to do this, so if you want this I can talk you through. I tried to make as reuseable as possible, but you have to ensure the recordsource code is correct.

1. very fast to add, but not in the subform for many reasons
2. can edit
3. You can delete.
4. I made an incrementing serial per fabric. This could instead be an input in the add button and also editable.
5. Cannot add more than 60

View attachment 105929
View attachment 105933

Hi! majP. I am highly obliged that you have helped me so much. I have no way to thank you enough. I figured out your initial code because i just don't want to copy paste, and i will go thru all that codes that you have given above. If i have any issues i will definitely write here.

 

[MajP]

Here is some explanation how it works.

1. For several reasons I did not link the master form to the subforms but control all off the filtering of the subforms in the main forms current event. Because you have 5 of the same subforms with different recordsource, I think trying to use subform linking would be hard or would not work.
2. Multiple instances of the same subform (or form) can be given unique properties at run time. You could even change colors, fonts, etc on each one. This allows me to give each subform a unique Recordsource and other properties
3. For each subform I added two custom properties. Number of records to display, and the Subform number. These are passed in by the main form.

Private s1 As Form_subFrmTakaMeter
Private s2 As Form_subFrmTakaMeter
Private s3 As Form_subFrmTakaMeter
Private s4 As Form_subFrmTakaMeter
Private s5 As Form_subFrmTakaMeter

Private Sub Form_Load()
  'Me.subFrmOne.Recordset = "qryOne"
   Set s1 = Me.subFrmOne.Form
   Set s2 = Me.subFrmTwo.Form
   Set s3 = Me.subFrmthree.Form
   Set s4 = Me.subFrmFour.Form
   Set s5 = Me.subFrmFive.Form
   s1.Initialize 12, 1
   s2.Initialize 12, 2
   s3.Initialize 12, 3
   s4.Initialize 12, 4
   s5.Initialize 12, 5
End Sub

4. Each subform then uses this information to set its Recordsource

Public Sub SetRecordSource()
 Dim strSql As String
 If Me.SubFormNumber = 1 Then
    strSql = "SELECT Top " & Me.NumberRecordsDisplayed & " tblTakaMeter.FabricID_FK, tblTakaMeter.takaID, tblTakaMeter.SerialNumber, tblTakaMeter.ReceiveMeter FROM tblTakaMeter " _
           & " WHERE FabricID_FK = [Forms]![frmDownUp]![FabricID] ORDER BY tblTakaMeter.TakaID"
  Else
    strSql = " SELECT TOP " & Me.NumberRecordsDisplayed & " tblTakaMeter.FabricID_FK, tblTakaMeter.takaID, tblTakaMeter.SerialNumber, tblTakaMeter.ReceiveMeter FROM tblTakaMeter " _
    & " WHERE fabricID_FK = [Forms]![frmDownUp]![FabricID] AND tblTakaMeter.takaID not in (SELECT TOP " & (Me.SubFormNumber - 1) * (Me.NumberRecordsDisplayed) & " takaID from tblTakaMeter  where fabricID_FK = " & Me.Parent.fabricID & " order by takaID)"
    
  End If
  Me.RecordSource = strSql
  Debug.Print strSql
End Sub

5. So here is the big trick shown in the print out of each forms SQL wiith fields removed.

SELECT Top 12 tblTakaMeter.FabricID_FK, tblTakaMeter.takaID...  FROM tblTakaMeter  WHERE FabricID_FK = [Forms]![FrmFabric]![FabricID] ORDER BY tblTakaMeter.TakaID

SELECT TOP 12 tblTakaMeter.FabricID_FK.... FROM tblTakaMeter  WHERE fabricID_FK = [Forms]![FrmFabric]![FabricID] AND tblTakaMeter.takaID not in (SELECT TOP 12 takaID from tblTakaMeter  where fabricID_FK = 1 order by takaID)

SELECT TOP 12 tblTakaMeter.FabricID_FK, .... FROM tblTakaMeter  WHERE fabricID_FK = [Forms]![FrmFabric]![FabricID] AND tblTakaMeter.takaID not in (SELECT TOP 24 takaID from tblTakaMeter  where fabricID_FK = 1 order by takaID)

SELECT TOP 12 tblTakaMeter.FabricID_FK, t.... FROM tblTakaMeter  WHERE fabricID_FK = [Forms]![FrmFabric]![FabricID] AND tblTakaMeter.takaID not in (SELECT TOP 36 takaID from tblTakaMeter  where fabricID_FK = 1 order by takaID)

SELECT TOP 12 tblTakaMeter.FabricID_FK, .... FROM tblTakaMeter  WHERE fabricID_FK = [Forms]![FrmFabric]![FabricID] AND tblTakaMeter.takaID not in (SELECT TOP 48 takaID from tblTakaMeter  where fabricID_FK = 1 order by takaID)

The second subform selects the Top 12 records not in the first 12
The third selects the the Top 12 records not in the first 24
....
The fifth selects the Top 12 not in the first 48
6. Because there is not linking the main form has to set these recordsource everytime the main form goes to a new FabricID

Private Sub Form_Current()
  s2.SetRecordSource
  s3.SetRecordSource
  s4.SetRecordSource
  s5.SetRecordSource
  RequeryAll
End Sub

Private Sub RequeryAll()
    s1.Requery
    s2.Requery
    s3.Requery
    s4.Requery
    s5.Requery
End Sub

 

[ebs17]

A nice and convincing solution by @MajP

But I would like to make one more remark: Since the takaID used is a sequential number, you can simplify the RecordSource of the subforms:
=> Waiver of the NOT IN, because an unfavorable formulation in terms of performance (although no performance problem will arise here due to the data set numbers)
=> no subquery needed
=> index can be used for filters on takaID
=> no case distinction between subform1 and the others

' available (shortened)
strSql = "SELECT TOP " & Me.NumberRecordsDisplayed & " FabricID_FK, takaID, SerialNumber, ReceiveMeter FROM tblTakaMeter" _
         & " WHERE fabricID_FK = [Forms]![FrmFabric]![FabricID]" _
         & " AND takaID not in" _
         & " (SELECT TOP " & (Me.SubFormNumber - 1) * Me.NumberRecordsDisplayed & " takaID from tblTakaMeter" _
         & " where fabricID_FK = " & Me.Parent.fabricID & " order by takaID)"

'possible alternative
strSql = "SELECT FabricID_FK, takaID, SerialNumber, ReceiveMeter FROM tblTakaMeter" _
         & " WHERE fabricID_FK = [Forms]![FrmFabric]![FabricID]" _
         & " AND takaID BETWEEN " & (Me.SubFormNumber - 1) * Me.NumberRecordsDisplayed + 1 _
         & " AND " & (Me.SubFormNumber - 1) * Me.NumberRecordsDisplayed + Me.NumberRecordsDisplayed

 

[MajP]

@ebs17,
I Purposely avoided that solution. I try always to make my solution as generic, reusable and simple for the user to implement as possible. I was only guessing that the serial numbers are contiguous and sequential, but my solution was meant to work with the more likely case of any type of serial numbers and non sequential and non contiguous. Purposely did not want to limit the solution,. However if there was thousands (maybe 10s of thousands) there could be performance impact. The OP also mentioned wanting to add, edit, and delete. If the user deletes a SN/s (probably not likely) from the middle then your solution no longer works. However, potentially that could be fixed by renumbering SNs (also uncommon).

This is why I favor the listbox approach since it requires only instantiation of the class and really no code has to be altered. The solution to "page" 60 records at a time is pretty ingenious if I have to say so myself. I originally tried subqueries on a large data set and performance was poor. I read the recordset and store in a collection where the "page" starts. Then when I move a page I simply move to that location in the reordset. I could have done a similar solution with more code for this example using that idea (provides the performance but flexibility of the current design)