Solved DoCmd. GoToRecord does not work correctly (1 Viewer)

[zelarra821]

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

Okay. If in the event when I open the form I do not put it to go to any record, it shows me all, from the beginning.

Yes I use it

DoCmd.GoToRecord,, acNewRec

It shows me only the last record, and I can't see any more records on the screen.

And if I use

DoCmd.RunCommand acCmdRecordsGoToLast
     DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 3
     DoCmd.RunCommand acCmdRecordsGoToNew

It takes me all the registers adjusted to the height of the screen (8 registers plus the new register). In the form that had the problem I have 11 records. When I add 12, I close and reopen, with your code it already shows me all three plus the new one.

So I guess this is the best I can hope for.

Thank you very much to all.

 

[zelarra821]

Excuse me I have managed to fix it everywhere except in a subform.

I have a code to fill a subform with some dates that I choose. At the end of this code, I call the function to take me to the last record, showing this and the previous three. But it does not work. I have done an error control and it does not give any.

This is the code:

Option Explicit

Function Rellenar(FName As Form, Codigo As String, Tabla As String, Subform As String, ValorCodigo As Variant)
Dim Observaciones As String
Dim FechaInicio As Date
Dim FechaFinal As Date
Dim rstRegistro As DAO.Recordset

    On Error GoTo err_lbl
    If Not IsNull(FName.Controls(Codigo)) Then
        Select Case MsgBox("¿Quieres rellenar con un texto hasta una fecha determinada?", vbYesNo Or vbQuestion Or vbDefaultButton1, NombreBD)
            Case vbYes
                Observaciones = InputBox("¿Qué texto quieres añadir en las observaciones?", NombreBD)
                'Si el usuario cancela el InputBox, salimos
                    If StrPtr(Observaciones) = 0 Then Exit Function
                FechaInicio = InputBox("Desde cuándo quieres rellenar?", NombreBD, Format(Date, "dd-mmm-yyyy"))
                FechaFinal = InputBox("¿Hasta cuándo quieres rellenar?", NombreBD, Format(DateAdd("d", 1, Date), "dd-mmm-yyyy"))
                FechaFinal = DateAdd("d", 1, FechaFinal)
                Do
                    Set rstRegistro = CurrentDb.OpenRecordset(Tabla)
                    If FechaInicio < FechaFinal Then
                        rstRegistro.AddNew
                        rstRegistro!Fecha = FechaInicio
                        rstRegistro.Fields(Codigo) = FName.Controls(Codigo)
                        rstRegistro!SiNo = True
                        rstRegistro!Observaciones = Observaciones
                        rstRegistro.Update
                    End If
                    FechaInicio = DateAdd("d", 1, FechaInicio)
                Loop While FechaInicio < FechaFinal
                FName.Controls(Subform).Requery
            Case vbNo
                Exit Function
        End Select
    Call PersonalizarVista(FName, Tabla, True, Codigo, ValorCodigo)
    Else
        MsgBox "No has seleccionado un registro.", vbInformation, NombreBD
        Exit Function
    End If
err_lbl:
    Select Case Err.Number
        Case 13
            MsgBox Err.Number & " " & Err.Description
            Exit Function
    End Select
End Function

The function PersonalizarVista has ended up like this (it is the one I use to go to the new record ...):

Function PersonalizarVista(FName As Form, Tabla As String, Optional HayCriterio As Boolean = False, Optional CampoCriterio As String, Optional Criterio As Variant)
Dim Registros As Long
On Error GoTo err_lbl
If HayCriterio = False Then
Registros = DCount("*", Tabla)
Else
Registros = DCount("*", Tabla, CampoCriterio & "=" & Criterio)
End If
DoCmd.RunCommand acCmdRecordsGoToLast
Select Case Registros
Case Is > 5
DoCmd.GoToRecord acDataForm, FName.Name, acPrevious, 3
Case 2 To 5
DoCmd.GoToRecord acDataForm, FName.Name, acPrevious, 1
End Select
DoCmd.RunCommand acCmdRecordsGoToNew
err_lbl:
Select Case Err.Number
Case 3075
MsgBox Err.Number & " " & Err.Description
Exit Function
End Select
End Function

Thanks!!!

 

[Gasman]

You need to walk through the code and see what it is actually doing?
This does not look correct to me Registros = DCount("*", Tabla, CampoCriterio & "=" & Criterio) Put the citeria into a string, then you can debug.print it to see if it is correct.

Should be more like

Registros = DCount("*", Tabla, "CampoCriterio =" & Criterio)

and if Criterio is actually a string, it should be surrounded with single quotes or triple double quotes.? Best to put into a string variable to make sure.?

 

[zelarra821]

You need to walk through the code and see what it is actually doing?
This does not look correct to me Registros = DCount("*", Tabla, CampoCriterio & "=" & Criterio) Put the citeria into a string, then you can debug.print it to see if it is correct.

Should be more like

Registros = DCount("*", Tabla, "CampoCriterio =" & Criterio)

and if Criterio is actually a string, it should be surrounded with single quotes or triple double quotes.? Best to put into a string variable to make sure.?

I have tested that and it works correctly. I've already passed the code using F8 and it doesn't give any error. That's why I ask, in case anyone knows what else it could be.

 

[Gasman]

I have tested that and it works correctly. I've already passed the code using F8 and it doesn't give any error. That's why I ask, in case anyone knows what else it could be.

Sorry, I cannot see that as being correct, due to the syntax there.?

 

[zelarra821]

Sorry, I cannot see that as being correct, due to the syntax there.?

Excuse me, I know what you're saying. Now later I correct it and tell you. Thank you.

 

[zelarra821]

I have already fixed that problem:

Function PersonalizarVista(FName As Form, Tabla As String, Optional HayCriterio As Boolean = False, Optional CampoCriterio As String, Optional Criterio As Variant)
    Dim Registros As Long
    On Error GoTo err_lbl
    If HayCriterio = False Then
        Registros = DCount("*", Tabla)
    Else
        Dim Criterio1 As String
        Criterio1 = CampoCriterio & " = " & Criterio
        Registros = DCount("*", Tabla, Criterio1)
    End If
    DoCmd.RunCommand acCmdRecordsGoToLast
    Select Case Registros
        Case Is > 5
            DoCmd.GoToRecord acDataForm, FName.Name, acPrevious, 5
        Case 2 To 5
            DoCmd.GoToRecord acDataForm, FName.Name, acPrevious, 1
    End Select
    DoCmd.RunCommand acCmdRecordsGoToNew
err_lbl:
    Select Case Err.Number
        Case 3075
            MsgBox Err.Number & " " & Err.Description
            Exit Function
    End Select
End Function

Now what I think happens. Where I have the problem is just in the forms with subform. These lines:

DoCmd.RunCommand acCmdRecordsGoToLast
DoCmd.GoToRecord acDataForm, FName.Name, acPrevious, 5
DoCmd.RunCommand acCmdRecordsGoToNew

They refer to the main form, so nothing happens in the subform.

I have tried adding something similar to this

FName.Controls("SubformName").SetFocus

But it doesn't work either.

 

[Minty]

Try passing in the full reference to the subform object. Something like

Forms!Mainform!Subform1.Form

 

[Gasman]

Your syntax is still wrong for the criteria.
Obviously that needs to run in the subform?

 

[zelarra821]

Your syntax is still wrong for the criteria.
Obviously that needs to run in the subform?

Hi.

I honestly don't know what is wrong. Look, I put a Debug.Print, and in the immediate window it shows me the correct values.

I need it for the subform because that is where I want it to show me the new record plus three previous records.

 

[zelarra821]

Try passing in the full reference to the subform object. Something like

Forms!Mainform!Subform1.Form

Where I think I have the problem is here:

DoCmd.GoToRecord acDataForm, FName.Name, acPrevious, 5

That FName.Name I get it from the main form, not from the subform.

 

[Gasman]

Try debug.print Criterio1 ? what does that show?
One path would be fine, but you need to test all paths?

 

[zelarra821]

Try debug.print Criterio1 ? what does that show?
One path would be fine, but you need to test all paths?

 

[Gasman]

Ah OK, sorry.
I thought CampoCriteria was the name of a field?. My apologies.

So how is the subform linked to the mainform?
Why not just have those 3 lines of code in the subform?
I'm all for making code generic, but you need to get the basic code working first, before trying more advanced methods.?
Can you pass a subform name or control in as optional?

 

[zelarra821]

So how is the subform linked to the mainform?

They are linked by a numeric code, which depends on the form, so I pass the name of the code to the function:

Optional CampoCriterio As String

 

[zelarra821]

Why not just have those 3 lines of code in the subform?

What three lines?

 

[Gasman]

What three lines?

GotoLast
Goto -3
GotoNew

 

[zelarra821]

GotoLast
Goto -3
GotoNew

Because what I want to achieve is that the screen shows me the new record and the previous three, not the new record only

 

[zelarra821]

I'm all for making code generic, but you need to get the basic code working first, before trying more advanced methods.?

In the other forms that I have no subform it works fine.

 

[zelarra821]

Look, I already have the error it gives. It's 2493, and it tells me that this action requires an Object Name argument