Solved DoCmd. GoToRecord does not work correctly

[zelarra821]

Good Morning.

I'm trying to get when I open a record to go to a new record, but showing the three previous records, because otherwise it just shows me the new record.

I have tried with

Dim Registros As Variant
Registros = DCount("*", "MyTable")
DoCmd.GoToRecord , , acGoTo, Registros - 3
DoCmd.GoToRecord , , acNewRec

It works fine on all forms, except, coincidentally, the ones with eleven records, which shows me the new record.

Can anyone help me find what I am doing wrong?

Thank you!

 

[Minty]

You need to check the number of records, no point moving around otherwise.

If me Registros > 3 then ' You might want to adjust this for the number available on the form?
DoCmd.GoToRecord acActiveDataObject, , acGoTo, Registros - 3
end if

 

[zelarra821]

Thanks for answering.

I did not want to send the whole function, because I apply this in several forms, and to simplify the database code a bit.

Function PersonalizarVista(Tabla As String, Optional HayCriterio As Boolean = False, Optional CampoCriterio As String, Optional Criterio As Variant)
    Dim Registros As Variant
    On Error GoTo err_lbl
    If HayCriterio = False Then
        Registros = DCount("*", Tabla)
    Else
        Registros = DCount("*", Tabla, CampoCriterio & "=" & Criterio)
    End If
    Select Case Registros
        Case Is >= 6
            DoCmd.GoToRecord , , acGoTo, Registros - 3
        Case 2 To 5
            DoCmd.GoToRecord , , acGoTo, Registros - 1
        Case Is = 1, Is = 0
    End Select
    DoCmd.GoToRecord , , acNewRec
err_lbl:
    Select Case Err.Number
        Case 3075
            Exit Function
    End Select
End Function

But it doesn't work either.

 

[Minty]

I think your select case statement is incorrect and I would also dim your count as a long not a Variant.
A Dcount will always return a number.

I would also add Option Explicit to the top of ALL your code modules. It would highlight some errors

Dim Registros As Long

 Select Case Registros
        Case Is > 5
            DoCmd.GoToRecord , , acGoTo, Registros - 3
        Case 2 To 5
            DoCmd.GoToRecord , , acGoTo, Registros - 1

    End Select

 

[arnelgp]

can you use the Recordset:

    With Me.Recordset
        .MoveFirst
        Do Until .EOF
            Me.Bookmark = .Bookmark
            .MoveNext
        Loop
        .AddNew
    End With

 

[zelarra821]

I think your select case statement is incorrect and I would also dim your count as a long not a Variant.
A Dcount will always return a number.

I would also add Option Explicit to the top of ALL your code modules. It would highlight some errors

Dim Registros As Long

Select Case Registros
        Case Is > 5
            DoCmd.GoToRecord , , acGoTo, Registros - 3
        Case 2 To 5
            DoCmd.GoToRecord , , acGoTo, Registros - 1

    End Select

Let's see, if I have used Variant it has been because, according to Access help, it is the type of value that a DCount returns.

Anyway, I have made the changes you have proposed, and it still does not work.

 

[zelarra821]

can you use the Recordset:

    With Me.Recordset
        .MoveFirst
        Do Until .EOF
            Me.Bookmark = .Bookmark
            .MoveNext
        Loop
        .AddNew
    End With

Hi thanks. With that method, I "save" the first record, but not three above the new record.

 

[arnelgp]

adding Me.Painting = False to the code, minimized the "scrolling" effect:

    Me.Painting = False
    With Me.Recordset
        .MoveFirst
        Do Until .EOF
            Me.Bookmark = .Bookmark
            .MoveNext
        Loop
        .AddNew
    End With
    Me.Painting = True

 

[arnelgp]

Hi thanks. With that method, I "save" the first record, but not three above the new record.

what does that mean? plz explain.

 

[zelarra821]

what does that mean? plz explain.

I want you to show me the last three plus the new record. Look at the picture.

Also, that code can I use or adapt to use in a subform from a main form?

 

[arnelgp]

yes, you can use that on subform.
but need some modification:

Me![theSubformName].SetFocus
Me![theSubformName].Form.Painting = False
With Me![theSubformName].Form.Recordset
    .MoveFirst
    Do Until .EOF
        Me![theSubformName].Form.Bookmark = .Bookmark
        .MoveNext
    Loop
    .AddNew
End With
Me![theSubformName].Form.Painting = True

 

[zelarra821]

Awesome. And what about showing the last three and the new register? Is it possible?

 

[Minty]

What happens that makes your original method not work. An error or what does it display?

I have used something similar many times on a continuous form when adding new records and it works fine.

 

[Gasman]

This does not have the new record, but this is all I used to get the last five?

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "qryEmails"
    Me.sfrmEmails.SourceObject = "cfrmEmails"
    Set Me.sfrmEmails.Form.Recordset = Me.Recordset
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.Maximize
End Sub

 

[zelarra821]

Option Explicit

I think your select case statement is incorrect and I would also dim your count as a long not a Variant.
A Dcount will always return a number.

I would also add Option Explicit to the top of ALL your code modules. It would highlight some errors

Dim Registros As Long

Select Case Registros
        Case Is > 5
            DoCmd.GoToRecord , , acGoTo, Registros - 3
        Case 2 To 5
            DoCmd.GoToRecord , , acGoTo, Registros - 1

    End Select

It does not give any error. I have only changed the one of the module where I have the code, because it is not affected by the code of another module. I have yet to change it in the other modules, because it did give me an error (not associated with this) and I have to see it slowly.

 

[zelarra821]

Me.RecordSource = "qryEmails"

Me.sfrmEmails.SourceObject = "cfrmEmails"

Set Me.sfrmEmails.Form.Recordset = Me.Recordset

I don't know how to adapt that for my case.

 

[arnelgp]

for test:

    Dim lngCount As Long
    SendKeys "{PGDN 10}", True
    DoEvents
    lngCount = Me.Recordset.RecordCount
    DoCmd.GoToRecord , , acGoTo, (lngCount - 2)

 

[arnelgp]

    Dim f As subform
    Dim lngCount As Long
    Set f = Me![theSubformName]
    f.SetFocus
    SendKeys "{PGDN 10}", True
    DoEvents
    lngCount = f.Form.Recordset.RecordCount
    DoCmd.GoToRecord , , acGoTo, (lngCount - 2)
    Set f = Nothing

 

[zelarra821]

    Dim f As subform
    Dim lngCount As Long
    Set f = Me![theSubformName]
    f.SetFocus
    SendKeys "{PGDN 10}", True
    DoEvents
    lngCount = f.Form.Recordset.RecordCount
    DoCmd.GoToRecord , , acGoTo, (lngCount - 2)
    Set f = Nothing

Thank you very much, but I wouldn't want to have to use sendkeys. I was using it in another database and I didn't like the behavior. I'll keep looking for something else.

 

[Gasman]

I don't know how to adapt that for my case.

You don't. I just posted the whole event, expecting you to identify what you could take from that.?
Look at the GotoRecord lines.