I have a form with my top 10 clients based on theit turnover. Once this graph is in view I would like to have a button to go to the next 10 clients after each click. It should also detect that it has come to the end of the list. Could you give me directions to make this possible?
If you want a form that only displays ten records of a recordset at a time you could change the Top Values property of the record source query from All to 10. In the SQL view of the query you can add this property by adding TOP 10 after the SELECT keyword, e,g.
Code:
SELECT TOP 10 Customers.ID, ....
This would limit the record set to the first ten records. But you want to move the start point of those ten records and so you need to add criteria to the query to do that. You can start this by adding a textbox to your form to hold the key value that will mark the starting point of the 10 records being displayed. Let's say you name this textbox CurrentTopKey. You will populate this with the starting point of the ten records with some button code I will explain shortly but when the form is first open this will be empty and we want the first 10 records. We can achieve this with the IIF function in the criteria of the record source query, e.g.
Code:
WHERE (((IIf(IsNull([forms]![Customers]![CurrentTopKey]),True,[Customers].[ID]>[forms]![Customers]![CurrentTopKey]))<>False));
The textbox becomes a controlling factor when it has something in it. To get the form to display the next ten records we put the follow code in a command button event.
In this example ID is the key field of the record source and CustomersRecordSource is the record source query of the form. This populates the textbox with the key of the last record currently being displayed. That textbox now having something in it is used as criteria in the query, i.e., [Customers].[ID]>[forms]![Customers]![CurrentTopKey] is the criteria now giving the next ten records greater than it.
To indicate that we are on the last set of records we just need to count the records in the record sources in the command button event and after the code above we can put something like:
Code:
If DCount("*", "[CustomersRecordSource]") < 10 Then
MsgBox "At last set of records"
End If
To go to the first record you would execute the follow code in a command button event.
Code:
Me.CurrentTopKey = Null
Me.Requery
All of this is demonstrated in the attached database. I believe navigating backwards through the records would be tricky and since you didn't state a requirement for that I haven't given it much thought.
Thanks Steve! It is exact doing where I was looking for. Great and , indeed, going backwards through the list is another question. For now I can use your advice and examples in my database.
So do you need to go backwards? If so let me know and I'll see what I can figure out. Off the top of my head I'd just stack the start keys during the navigation forward but there's probably a better way.
I've attached a database which implements a button that display the ten previous records. There's probably a way to query the right key value to do this but I couldn't figure that out so I give you a mindless solution that at least works. This solution just stacks the start points as you navigate forward through the records and retrieves them when you navigate backwards. To implement a stack I use code I found here that was posted by Craig0201. I suggest just importing the two classes Stack and StackItem from the attached database into your project.
The attached database is different from the previous version as follows:
1 .It contains the classes Stack and StackItem
2. At the top of the Customers form module there is a global declaration of a Stack object, i.e.
Code:
Dim StartKeys As New Stack
3. The code for the button that navigates forward has been changes as shown in red to add the start key values to the stack.
Code:
Private Sub NextTen_Click()
[COLOR="Red"]StartKeys.Push Me.CurrentTopKey[/COLOR]
Me.CurrentTopKey = DMax("[ID]", "[CustomersRecordSource]")
Me.Requery
If DCount("*", "[CustomersRecordSource]") < 10 Then
MsgBox "At last set of records"
End If
End Sub
4. Code has been added to the button that goes to the first record which clears the stack as shown in red.
Code:
Private Sub FirstRecord_Click()
'[COLOR="red"]clear stacked.
Do While Not StartKeys.StackEmpty
StartKeys.Pop
Loop[/COLOR]
Me.CurrentTopKey = Null
Me.Requery
End Sub
5. A button that navigates to the previous ten records has been added which retrieves the key value from the stack, puts it in the CurrentTopKey textbox and requerys the form. Code to detect if you are at the first group of ten has also been included.
Code:
Private Sub PreviousTen_Click()
Me.CurrentTopKey = StartKeys.Pop
Me.Requery
If IsNull(Me.CurrentTopKey) Then
MsgBox "At first group of ten"
End If
End Sub
I'm working on a simpler version of this that doesn't require a stack. I should have it working in a few hours. You might want to wait and take a look at it before implement my version with the stack.
In this version (attached) all you need to do is add a button (Previous Ten) to the form, add the code below and modify it to your situation. Identifiers that may need change are in blue.
This is fairly simple minded too. It just open the whole table in a recordset, finds the records in it equal to the current key, moves backwards ten records, set the current key to this record and requeries the form. Note that this code uses the table (Customers in this code) in addition to the record source query.
This will act differently than the version with the stack if records are deleted (by other users for example) while navigating back and forth. In the stack version the sets of ten records will always start on the same record going back as forwards unless the first record was delete too. In this version if records are deleted the groups of ten going backwards can be entirely different sets of records. On the other in the stack version if records are added records could be missing from view in the groups as you navigate backwards.
Code:
Dim rs As DAO.Recordset
Dim i As Long
Dim AtFirst As Boolean
Set rs = CurrentDb.OpenRecordset("[COLOR="Blue"]Customers[/COLOR]", dbOpenDynaset)
rs.FindFirst "[[COLOR="Blue"]ID[/COLOR]] = " & Me.[COLOR="Blue"]CurrentTopKey[/COLOR]
If rs.NoMatch Then
'Dmin is used instead of Me.CurrentTopKey in case that record has been deleted.
rs.FindFirst "[[COLOR="blue"]ID[/COLOR]] = " & DMax("[[COLOR="blue"]ID[/COLOR]]", "[[COLOR="blue"]Customers[/COLOR]]", "[COLOR="blue"][ID[/COLOR]] =< " & Me.[COLOR="Blue"]CurrentTopKey[/COLOR])
If rs.NoMatch Then
MsgBox "Record Not Found" 'In case all of the records were deleted
Exit Sub
End If
End If
'move back through the recordset 10 records
For i = 1 To 10
If rs.AbsolutePosition = 0 Then
AtFirst = True
Exit For
End If
rs.MovePrevious
Next i
If AtFirst Then
Me.[COLOR="blue"]CurrentTopKey[/COLOR] = Null
Else
Me[COLOR="blue"].CurrentTopKey [/COLOR]= rs![COLOR="blue"]ID[/COLOR]
End If
Me.Requery
If AtFirst Then
MsgBox "At first group of ten"
End If
Thanks again Steve. I think I prefer your last option. Like you wrote, if I understand you correct, my table/query has not an ID that starts with 1 and every next record it is 1 up. I have gabs in that. Do you mean that due to the gabs I have to go for the second option? Or can I add a autonumber field to the Table/query? For your information a small issue rose when you come to the top of the list and you click the button 'previous ten' once more.
Thanks again Steve. I think I prefer your last option. Like you wrote, if I understand you correct, my table/query has not an ID that starts with 1 and every next record it is 1 up. I have gabs in that. Do you mean that due to the gabs I have to go for the second option? Or can I add a autonumber field to the Table/query?
Either solution should work with gaps in the keys. It's just a matter of what will be displayed if records are deleted while navigating. Please don't add an autonumber for this. If you find problems with these solutions let me know and I'll find a fix. There is one thing I forgotten to mention. If your key is a text field then the code for option 2 needs to be changed. Me.CurrentTopKey would need single quote around it where it is used as criteria, i.e., if ID were a text field then then single quotes would be needed as shown below in red.
Code:
rs.FindFirst "[ID] =[COLOR="Red"] '[/COLOR]" & Me.CurrentTopKey [COLOR="red"]& "'"[/COLOR]
If rs.NoMatch Then
'Dmin is used instead of Me.CurrentTopKey in case that record has been deleted.
rs.FindFirst "[ID] = " & DMax("[ID]", "[Customers]", "[ID] =< [COLOR="red"]'[/COLOR]" & Me.CurrentTopKey [COLOR="red"]& "'"[/COLOR])
For your information a small issue rose when you come to the top of the list and you click the button 'previous ten' once more.
Oops didn't see that one. To fix this add the code shown below in blue.
Code:
Dim rs As DAO.Recordset
Dim i As Long
Dim AtFirst As Boolean
[COLOR="Blue"]If IsNull(Me.CurrentTopKey) Then
MsgBox "At first group of ten"
Exit Sub
End If[/COLOR]
Set rs = CurrentDb.OpenRecordset("Customers", dbOpenDynaset)
rs.FindFirst "[ID] = " & Me.CurrentTopKey
If rs.NoMatch Then
'Dmin is used instead of Me.CurrentTopKey in case that record has been deleted.
rs.FindFirst "[ID] = " & DMax("[ID]", "[Customers]", "[ID] =< " & Me.CurrentTopKey)
If rs.NoMatch Then
MsgBox "Record Not Found" 'In case all of the records were deleted
Exit Sub
End If
End If
'move back through the recordset 10 records
For i = 1 To 10
If rs.AbsolutePosition = 0 Then
AtFirst = True
Exit For
End If
rs.MovePrevious
Next i
If AtFirst Then
Me.CurrentTopKey = Null
Else
Me.CurrentTopKey = rs!ID
End If
Me.Requery
If AtFirst Then
MsgBox "At first group of ten"
End If
I've a problem. As soon as I want to go to the next 5 clients and their remuneration as displayed in a graph with horizontal bars I get the message that 'CurrentTopKey' is not a member. Whem I type 'me.cur' this paramater is also not availlable in the presented list. The code is added to the form and I work in access 2013.
Private Sub cmdVolgendeSet_Click()
Me.CurrentTopKey = DMax("[pkKlant]", "[qryMijnBesteKlantRecordSource]")
Me.Requery
If DCount("*", "[qryMijnBesteKlantRecordSource]") < 5 Then
MsgBox "Dit is de laatste set!"
End If
and , indeed, going backwards through the list is another question. For now I can use your advice and examples in my database.
