Issue MoveLast and MoveFirst VBA

Daniel Corral Ruiz 21 Reputation points
2022-06-29T11:32:01.797+00:00

Dear all,

I have a problem with this code (since 1 month ago). There are problems with the MoveLast and MoveFirst function and I have been looking for it about 4 weeks ago and no solution found, so please if you can help me::

Sub ImportarModelos()
Dim strSource As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim consulta As String

Dim archivoAbrir As Excel.Workbook
Dim RUTAExcel As String
Dim strRespuesta As String

Dim posicionhoja As Integer
Dim rangocopiar As Range
Dim rangopegar As Range

Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long

Dim cantcarroc As Long
Dim matrizcarroc(150) As String
Dim ETYPE As String
Dim modelo As String
Static cantmodelos As String
Dim planta As String

Dim numMES1 As Integer
Dim numMES2 As Integer
Dim numMES3 As Integer
Dim numMES4 As Integer
Dim numMES5 As Integer
Dim numMES6 As Integer
Dim numMES7 As Integer
Dim numMES8 As Integer
Dim numMES9 As Integer
Dim numMES10 As Integer
Dim numMES11 As Integer
Dim numMES12 As Integer

Dim StartTime As Double
Dim MinutesElapsed As String

strRespuesta = MsgBox("La importación de Modelos eliminará todos los datos anteriores. ¿Desea continuar?", _
vbQuestion + vbYesNo, "Importación de Modelos")

If strRespuesta = vbNo Then
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

'Colocar ACT y FC en la Plantilla
Call Etiquetar_FC_ACT

'Ruta Excel con rutas almacenadas (Excel formulario cargar ficheros)
RUTAExcel = RutaTool.Range("D2") & "" & RutaTool.Range("C2")
Set archivoAbrir = Workbooks.Open(RUTAExcel)

'Ruta bbdd
With archivoAbrir
strSource = ActiveSheet.Range("D6").Value & "" & ActiveSheet.Range("C6").Value
.Close
End With
Set dbs = DBEngine.OpenDatabase(strSource, False, False)

sFechaBotonModelos = "UPDATE Fechas_Modelos SET Fecha_BotonModelos= Now()"
dbs.Execute sFechaBotonModelos

'Aviso de ejecucion importar datos
Application.StatusBar = "Importando MODELOS desde Base de Datos"

marca = indice.Range("B10")

If marca = "TODAS" Then

consulta = "SELECT DISTINCT ModelosBD.E_CS " _
& "FROM ModelosBD " _
& "WHERE (((ModelosBD.Modelcode) In (SELECT Modelcode FROM [InvoicesAG]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_NSCStock]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_DealerStock]) " _
& "OR (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_CC) " _
& "OR (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_RAC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasCC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasRAC) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [WholesaleTOTAL]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [IncomingORDERS]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [RetailTOTAL])))"

Else
consulta = "SELECT DISTINCT ModelosBD.E_CS " _
& "FROM ModelosBD " _
& "WHERE (((ModelosBD.Brand)='" & marca & "') AND ((ModelosBD.Modelcode) In (SELECT Modelcode FROM [InvoicesAG]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_NSCStock]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_DealerStock]) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_CC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_RAC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasCC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasRAC) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [WholesaleTOTAL]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [IncomingORDERS]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [RetailTOTAL])))"

End If

Set rst = dbs.OpenRecordset(consulta)
rst.MoveLast <---------- error
rst.MoveFirst <---------- error

cantcarroc = rst.RecordCount
For i = 1 To cantcarroc
matrizcarroc(i) = rst.Fields(0)
rst.MoveNext
Next i

'** para modelos, elimina todas las hojas y crea nueva por cada carrocería (para WH,RT e IO no elimina hojas)

canthojas = Worksheets.Count

If canthojas > 14 Then
For m = canthojas To 15 Step -1 'PARA CADA HOJA
Sheets(m).Delete
Next
End If 'canthojas >14
Sheets(3).Visible = True 'NuevaPlantilla

For m = 1 To cantcarroc
Sheets(3).Copy after:=Sheets(14) 'copia la hoja Plantilla (oculta)
Next m
Sheets(3).Visible = False

For i = 1 To cantcarroc

0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 46,041 Reputation points
    2022-06-29T13:19:35.93+00:00

    There are problems with the MoveLast and MoveFirst function

    And which problems do you have with; you haven't mentioned it in any way?
    Please post the complete error message.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Daniel Corral Ruiz 21 Reputation points
    2022-06-29T13:09:04.027+00:00

    I have checked if there is at least one record obtained by the recordset and the case is that there is NO record; the error has to be before the MoveLast function

    Set rst = dbs.OpenRecordset(consulta)
    If not (rst.BOF and rst.EOF) Then
    .MoveLast
    .MoveFirst
    'continue with the code
    Else
    'the recordset is empty
    Msgbox "No record was found!"
    End If
    rst.Close
    set rst = Nothing

    0 comments No comments

  2. Daniel Corral Ruiz 21 Reputation points
    2022-06-29T13:26:36.793+00:00

    Hello, thank you for your reply and sorry for not being able to explain the error. The macro is extracting data from current months in order to predict future months. The error message I get is the following: "An error '3021' has occurred at runtime: there is no active record" When it has been working correctly for months and no one has touched the code.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.