Display a specified number of records in a continuous form

[zelarra821]

Hello people.

Let's see if you can help me.

I attach a database where I have put the code

DoCmd.GoToRecord , , acLast

in the Form_Load event.

When I enter the form, it appears like this:



That is, it shows me only one record, because it is the last one.

Now, here I pose two questions:

1. Is it possible that instead of showing the last one, it shows the last three?

2. Is it possible to get point 1 after updating a record?

Thank you.

 

[CJ_London]

yes it is possible - you need code to go back 2, 3, whatever records - see this link

Go to the Previous record

Go to the Previous record on a form

www.msaccessgurus.com

or google - plenty of examples out there

 

[KitaYama]

1-
DoCmd.GoToRecord , , , DCount("*", "Nombres") - 3

2-
I don't understand your second question.

 

[zelarra821]

I don't understand your second question.

I mean:

Go to record = all record - 3, for example, when you finish a new record or update an existing record.

TThanks to all.

 

[MajP]

This may interest you

Option Compare Database
Option Explicit
Dim CmbSexo As New FindAsYouTypeCombo

Private Sub cmboNumber_AfterUpdate()
  FilterForm
End Sub

Private Sub Form_AfterUpdate()
  FilterForm
End Sub

Private Sub Form_Load()
    CmbSexo.InitalizeFilterCombo Me.IdSexo, "Sexo", anywhereinstring, True, True
    Me.cmboNumber = 3
    FilterForm
End Sub

Public Sub FilterForm()
  Dim strSql As String
  Dim numbertoshow As Integer
  Dim TotalRecords As Integer
 
  TotalRecords = DCount("*", "Nombres")
  strSql = "Select * from Nombres Order By idNombre"
  If cmboNumber <> "<All>" And Not IsNull(Me.cmboNumber) Then
     numbertoshow = CInt(Me.cmboNumber)
    Debug.Print TotalRecords - numbertoshow
     If TotalRecords > numbertoshow Then
         strSql = "Select * from Nombres where idNombre NOT IN (Select Top " & (TotalRecords - numbertoshow) & " idNombre from Nombres Order By Idnombre) Order By idNombre"
     End If
  End If
 Me.RecordSource = strSql
 Debug.Print strSql
End Sub

This way you are only displaying X amount of the last records or you can choose All

 

[zelarra821]

Well yes, it interests me and helps me achieve what I want.

Just a very small detail: when you reach the last field of the record in question, it takes you to the first record of the number that you have selected in the drop-down menu. The natural behavior would be to move to the next record. Can this be done? I don't know if I explain myself.






Thanks.

 

[MajP]

How about this where you check that the number of records did not change

Option Compare Database
Option Explicit
Dim CmbSexo As New FindAsYouTypeCombo
Dim CmbOtraOpcion As New FindAsYouTypeCombo
Dim PreviousCount As Long

Private Sub cmboNumber_AfterUpdate()
  FilterForm
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
  FilterForm
End Sub

Private Sub Form_AfterUpdate()
  FilterForm
End Sub

Private Sub Form_Load()
    CmbSexo.InitalizeFilterCombo Me.IdSexo, "Sexo", anywhereinstring, True, True
    CmbOtraOpcion.InitalizeFilterCombo Me.IdOtraOpcion, "OtraOpcion", anywhereinstring, True, True
    Me.cmboNumber = 3
    PreviousCount = DCount("*", "Nombres")
    FilterForm True
    
End Sub

Public Sub FilterForm(Optional FirstPass As Boolean = False)
  Dim strSql As String
  Dim numbertoshow As Integer
  Dim TotalRecords As Integer
 
  TotalRecords = DCount("*", "Nombres")
  If PreviousCount <> TotalRecords Or FirstPass Then
     PreviousCount = TotalRecords
     strSql = "Select * from Nombres Order By idNombre"
     If cmboNumber <> "<All>" And Not IsNull(Me.cmboNumber) Then
        numbertoshow = CInt(Me.cmboNumber)
        If TotalRecords > numbertoshow Then
            strSql = "Select * from Nombres where idNombre NOT IN (Select Top " & (TotalRecords - numbertoshow) & " idNombre from Nombres Order By Idnombre) Order By idNombre"
        End If
     End If
    Me.RecordSource = strSql
    Debug.Print strSql
    Me.Recordset.MoveLast
 End If
End Sub

 

[zelarra821]

I just saw the operation itself.

I want to have all the records available and to see the last three. Now I only have the ones I marked in the drop-down menu, in this case, three, so to see all of them, I have to mark them in the drop-down menu. No, this is not what I was looking for. Is what I want possible?





On the other hand, with the change you have made now, you have fixed what it said that it went to the first visible record as long as it did not check the All drop-down menu.

Thank you so much.

 

[MajP]

Option Compare Database
Option Explicit
Dim CmbSexo As New FindAsYouTypeCombo
Dim CmbOtraOpcion As New FindAsYouTypeCombo
Dim PreviousCount As Long

Private Sub cmboNumber_AfterUpdate()
  moveForm True
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
  moveForm
End Sub

Private Sub Form_AfterUpdate()
  moveForm
End Sub

Private Sub Form_Load()
    CmbSexo.InitalizeFilterCombo Me.IdSexo, "Sexo", anywhereinstring, True, True
    CmbOtraOpcion.InitalizeFilterCombo Me.IdOtraOpcion, "OtraOpcion", anywhereinstring, True, True
    Me.cmboNumber = 3
    PreviousCount = DCount("*", "Nombres")
    moveform True
   
End Sub

Public Sub moveForm(Optional FirstPass As Boolean = False)
  Dim strSql As String
  Dim numbertoshow As Integer
  Dim TotalRecords As Integer

  TotalRecords = DCount("*", "Nombres")
  If PreviousCount <> TotalRecords Or FirstPass Then
     PreviousCount = TotalRecords
      If cmboNumber <> "<All>" And Not IsNull(Me.cmboNumber) Then
        numbertoshow = CInt(Me.cmboNumber)
        Me.Recordset.MoveFirst
        Me.Recordset.AbsolutePosition = TotalRecords - numbertoshow
        If TotalRecords > numbertoshow Then
        End If
     End If
End If
End Sub

 

[zelarra821]

I must be doing something wrong that I get an error. Here the database

 

[MajP]

Your On load was wrong. I must have copied something wrong. It called the old filterform not the new moveform. But I added another check anyways.

Option Compare Database
Option Explicit
Dim CmbSexo As New FindAsYouTypeCombo
Dim CmbOtraOpcion As New FindAsYouTypeCombo
Dim PreviousCount As Long

Private Sub cmboNumber_AfterUpdate()
  moveForm True
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
  moveForm
End Sub

Private Sub Form_AfterUpdate()
  moveForm
End Sub

Private Sub Form_Load()
    CmbSexo.InitalizeFilterCombo Me.IdSexo, "Sexo", anywhereinstring, True, True
    CmbOtraOpcion.InitalizeFilterCombo Me.IdOtraOpcion, "OtraOpcion", anywhereinstring, True, True
    Me.cmboNumber = 3
    PreviousCount = DCount("*", "Nombres")
    moveForm True
    
End Sub

Public Sub moveForm(Optional FirstPass As Boolean = False)
  Dim strSql As String
  Dim numbertoshow As Integer
  Dim TotalRecords As Integer
  Dim rs As DAO.Recordset
 
 
  TotalRecords = DCount("*", "Nombres")
  If PreviousCount <> TotalRecords Or FirstPass Then
      PreviousCount = TotalRecords
      Set rs = Me.Recordset
      If cmboNumber <> "<All>" And Not IsNull(Me.cmboNumber) And rs.RecordCount > Me.cmboNumber Then
        numbertoshow = CInt(Me.cmboNumber)
        rs.MoveFirst
        Debug.Print TotalRecords - numbertoshow
        rs.AbsolutePosition = (TotalRecords - numbertoshow)
        If TotalRecords > numbertoshow Then
        End If
     End If
 End If
End Sub

 

[zelarra821]

That's ok for me. Thanks.