Access vba ошибка 3021 текущая запись отсутствует - Ремонт и установка крупной бытовой техники

I am trying to export multiple datasets to the respective new Excel file.

   Public Sub MultipleQueries()

Dim i As Integer
Dim Mailer As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim qdf As QueryDef

Set Mailer = CurrentDb
Set rs1 = Mailer.OpenRecordset("MailerData")
Set qdf = Mailer.CreateQueryDef("CCspl", "PARAMETERS CostCentre Text ( 255 );SELECT MonthlyFteData.CostCentre, MonthlyFteData.EmpName, MonthlyFteData.Workload FROM MonthlyFteData WHERE (((MonthlyFteData.CostCentre)=[CostCentre]))")

For i = 0 To rs1.RecordCount - 1

qdf.Parameters("CostCentre") = rs1.Fields("CostCentre")

    Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)

Set rs2 = qdf.OpenRecordset()

With rs2

oSheet.Range("A2").CopyFromRecordset rs2
oBook.SaveAs "C:Users807140Downloads" & rs2.Fields("CostCentre") & ".xlsx"

rs2.Close
oExcel.Quit
Set oExcel = Nothing

End With

rs1.MoveNext
Next i

qdf.Close
Set qdf = Nothing
rs1.Close

End Sub

But I get the Runtime Error 3021 — No Current Record

I substituted the

oSheet.Range("A2").CopyFromRecordset rs2
oBook.SaveAs "C:Users807140Downloads" & rs2.Fields("CostCentre") & ".xlsx"

with

Debug.Print .RecordCount

And I do actually get the appropriate record count for rs2.

How can I fix my code to eliminate the error?

asked Jul 28, 2016 at 16:27

Don’t use For..Next loops with Recordsets. Use this:

Do While Not rs1.EOF
    ' do stuff with rs1
    rs1.MoveNext
Loop
rs1.close

And as Ryan wrote, Dim don’t belong into any loop, move them to the start of the sub.

If this doesn’t help, please tell us on which line the error occurs.

answered Jul 28, 2016 at 16:45

AndreAndre

26.6k6 gold badges35 silver badges80 bronze badges

The 3021 error («No current record.») occurs at the second of these two lines:

oSheet.Range("A2").CopyFromRecordset rs2
oBook.SaveAs "C:Users807140Downloads" & rs2.Fields("CostCentre") & ".xlsx"

That happens because the rs2 recordset pointer is at EOF after you do CopyFromRecordset rs2. Then at SaveAs, you ask for rs2.Fields("CostCentre"), but there is no available record («no current record») when the recordset pointer is at EOF.

However the rs1.Fields("CostCentre") value you used as the query parameter when opening rs2 is still accessible. So you can make the error go away by asking for rs1.Fields("CostCentre") instead of rs2.Fields("CostCentre")

oBook.SaveAs "C:Users807140Downloads" & rs1.Fields("CostCentre") & ".xlsx"

answered Jul 28, 2016 at 20:50

HansUpHansUp

95.8k11 gold badges76 silver badges135 bronze badges

This code has a few issues pointed out by @Andre and Ryan.

You’re not reusing your Excel object, you’re re-dimming objects that should only be defined once, using a With that never gets referenced so it just adds to code with no benefit.

You’re also creating a parameter query on the fly in code — instead of creating it in SQL and saving it to be reused by name.

You can try this rewritten code and see if it works better for you. I do believe that a predefined query is the better way to go — and then I’d close the query inside the loop and reset it at the start each time. I’ve just seen weird stuff happen when querydefs are reused inside loops without resetting them.

Anyways give this a try — and report on specific line that causes error

Public Sub MultipleQueries()

    Dim i       As Integer
    Dim Mailer  As Database
    Dim rs1     As Recordset
    Dim rs2     As Recordset
    Dim qdf     As QueryDef

    Dim oExcel  As Object
    Dim oBook   As Object
    Dim oSheet  As Object

    ' Only Open and Close Excel once
    Set oExcel = CreateObject("Excel.Application")

    Set Mailer = CurrentDb
    Set rs1 = Mailer.OpenRecordset("MailerData")

    ' Ideally you'd put this create query ahead of time instead of dynamically
    Set qdf = Mailer.CreateQueryDef("CCspl", "PARAMETERS CostCentre Text ( 255 );SELECT MonthlyFteData.CostCentre, MonthlyFteData.EmpName, MonthlyFteData.Workload FROM MonthlyFteData WHERE (((MonthlyFteData.CostCentre)=[CostCentre]))")

    Do Until rs1.EOF

        ' Sometimes weird things happen when you reuse querydef with new parameters
        qdf.Parameters("CostCentre") = rs1.Fields("CostCentre")
        Set rs2 = qdf.OpenRecordset()

        If Not rs2.EOF Then
            Set oBook = oExcel.Workbooks.Add
            Set oSheet = oBook.Worksheets(1)

            oSheet.Range("A2").CopyFromRecordset rs2
            oBook.SaveAs "C:Users807140Downloads" & rs2.Fields("CostCentre") & ".xlsx"
        Else
            Msgbox "No Data Found for: " & rs1.Fields("CostCentre") 
            Exit Do
        End If

        rs2.Close

        Set rs2 = Nothing
        Set oBook = Nothing     
        Set oSheet = Nothing        

        rs1.MoveNext
    Loop

    oExcel.Quit

    qdf.Close
    rs1.Close
    Mailer.Close

    Set qdf = Nothing
    Set rs1 = Nothing
    Set Mailer = Nothing

    ' Remove Excel references
    Set oBook = Nothing
    Set oSheet = Nothing
    Set oExcel = Nothing

End Sub

answered Jul 28, 2016 at 17:09

dbmitchdbmitch

5,3614 gold badges24 silver badges38 bronze badges

Источник

Remove From My Forums

Updating recordset — error 3021 — no current record

Question
Fellow developers, I am using Access 2010

I inherited some code for in an existing app — for updating. Normally I write direct sql but current code is doing below. All of this in in Access 2010 vba. The mykeyid is captured after we do an insert to create a user records in the beginning of the app.
This has been working flawleslly for weeks — hit about 100 times per day without error.

Here is the code

mysql = «Select * from myAgentTable where keyid =» & mykeyid

set myrs = mydb.openrecordset(mysql, dbopdbOpenDynaset)

With myrs

.Edit
.Fields(«EndDate»).Value =Now()

End With

myrs.Update
myrs.close

mydb.close

This code has been working flawlessly for weeks. Today, I saw too cases where my error handler bubled up a 3021. No current record. And that is pretty much impossible. When the app opens we insert the record for the key, when they close we update that
record using the identity value key. There record IS in the db. I have solid error handles on «every» db call.

Is there something wrong with they type of recordset we are using for this or is there another locking option we could try. As you know, finding somethign that happens rarely is very difficult becasue we cannot get this to happen all of the time.

Thanks for any suggestions.

MG

Answers

Hi Celieste,

It has not occurred since, we have additional traps put in to see if the values is coming back as zero. So far, nothing yet.
- Proposed as answer by
  
  Tuesday, September 6, 2016 1:19 AM
- Marked as answer by
  Chenchen Li
  Wednesday, September 7, 2016 5:43 PM

Источник

Правила форума
Темы, в которых будет сначала написано «что нужно сделать», а затем просьба «помогите», будут закрыты.
Читайте требования к создаваемым темам.

burik

Постоялец

Сообщения: 514

Зарегистрирован: 03.11.2005 (Чт) 22:04

Откуда: Беларусь, Рогачев

Ошибка 3021. Текущая запись отсутствует.

Здравствуйте!

Вобщем возникает ошибка 3021 (Текущая запись отсутствует).

Есть такой участок кода:

Код: Выделить всё: For i = 1 To rs.RecordCount Load Days(i) With Days(i) .Top = Days(i - 1).Top + Days(i - 1).Height + 60 .TDate = rs.Fields(1) .Text = rs.Fields(3) .BackColor = vbWhite .Visible = True End With rs.MoveNext Next i

Days — это массив моих контролов.

Ошибка возникает в …Property Let TDate.. :

Код: Выделить всё: Public Property Let TDate(ByVal New_TDate As Variant) DTPicker1.Value() = Mid$(New_TDate, 1, 10) '<<<<<<<< Здесь ошибка возникает tTime(0).Text = IIf(Len(CStr(Hour(New_TDate))) = 1, "0" & CStr(Hour(New_TDate)), CStr(Hour(New_TDate))) tTime(1).Text = IIf(Len(CStr(Minute(New_TDate))) = 1, "0" & CStr(Minute(New_TDate)), CStr(Minute(New_TDate))) tTime(2).Text = IIf(Len(CStr(Second(New_TDate))) = 1, "0" & CStr(Second(New_TDate)), CStr(Second(New_TDate))) PropertyChanged "TDate" End Property

Кол-во записей в rs — 4 у всех fields(1) = «24.06.2007 11:30:25».

Вроде все проверил.. Не знаю где ошибка..

Между слухов, сказок, мифов,
просто лжи, легенд сомнений
мы враждуем жарче скифов
за несходство заблуждений
Игорь Губерман

Antonariy

Повелитель Internet Explorer

Сообщения: 4824

Зарегистрирован: 28.04.2005 (Чт) 14:33

Откуда: Мимо проходил

Antonariy » 25.06.2007 (Пн) 15:14

А ты там нигде DataField/DataSource не проставляешь? Такая ошибка возникает, когда присваивается значение контролу, связанному с полем рекордета, который находится в BOF/EOF.

Лучший способ понять что-то самому — объяснить это другому.

burik

Постоялец

Сообщения: 514

Зарегистрирован: 03.11.2005 (Чт) 22:04

Откуда: Беларусь, Рогачев

burik » 25.06.2007 (Пн) 15:36

А ты там нигде DataField/DataSource не проставляешь? Такая ошибка возникает, когда присваивается значение контролу, связанному с полем рекордета, который находится в BOF/EOF.

Нет. Там только две процедуры с БД работают (эта и еще одна), обе такого типа:

Код: Выделить всё: sub sub_name() set db = dao.opendatabase.. set rs = db.opendatabase.. ... rs.close set rs = nothing db.close set db = nothing end sub

Связанных с БД контролов нет. В моем контроле есть DTPicker(календарь), image и два текст бокса

Antonariy

Повелитель Internet Explorer

Сообщения: 4824

Зарегистрирован: 28.04.2005 (Чт) 14:33

Откуда: Мимо проходил

Antonariy » 25.06.2007 (Пн) 15:51

Возможно рекордсет таки находится в EOF потому что For i = 1 To rs.RecordCount — безграмотно. Грамотно так:

Код: Выделить всё: While Not rs.EOF ... i = i + 1 rs.MoveNext Wend

Кроме того ты передаешь rs.Fields(1) в функцию, которая принимает Variant. Из-за этого передается не значение свойства по умолчанию — Value, — а объект Field.

Лучший способ понять что-то самому — объяснить это другому.

burik

Постоялец

Сообщения: 514

Зарегистрирован: 03.11.2005 (Чт) 22:04

Откуда: Беларусь, Рогачев

burik » 25.06.2007 (Пн) 16:00

Antonariy спасибо!

Заработало.

Вернуться в Visual Basic 1–6

Кто сейчас на конференции

Сейчас этот форум просматривают: Yandex-бот и гости: 1

Источник

For each «ConcatID», I want to concatenate «Major» into one row separated by a «;»

Source table:
ConcatID    Major
A           Math
A           English
A           Theatre
B           Math
C           Biology

Target table:
A           Math; English; Theatre
B           Math
C           Biology

The code runs and performs what I need it to do, but I get this error «Run-time error ‘3021’ No current record» on this line of code

Do While (ID_prev = rs_source![ConcatID].Value And Not (rs_source.EOF))

Option Compare Database
Option Explicit

Sub Concat()
    Dim dbs As DAO.Database

    Dim rs_source As DAO.Recordset
    Dim rs_target As DAO.Recordset

    Dim MajorList As String 'Placeholder for concatenating list of college majors
    Dim ID_prev As String 'Retains ID from previous record

    Set dbs = CurrentDb
    Set rs_source = dbs.OpenRecordset("tbl_ConcatMajorsSource") 'many records per student/college
    Set rs_target = dbs.OpenRecordset("tbl_ConcatMajorsTarget") 'one record per student/college
    dbs.Execute ("DELETE * FROM tbl_ConcatMajorsTarget") 'clear out table

    ID_prev = rs_source![ConcatID].Value ' set equal to first ID
    MajorList = rs_source![Major].Value ' set equal to first major
    rs_source.MoveNext ' move to the second record

    Do While Not (rs_source.EOF)
        Do While (ID_prev = rs_source![ConcatID].Value And Not (rs_source.EOF))
            MajorList = rs_source![Major].Value & "; " & MajorList 'concatenate majors
            rs_source.MoveNext
        Loop
        rs_target.AddNew ' add new record in target table
        rs_target![ConcatID].Value = ID_prev ' populate ID
        rs_target![Major].Value = MajorList ' populate MajorList
        rs_target.Update
        ID_prev = rs_source![ConcatID].Value ' set ID_prev to the new ID
        MajorList = "" 'blank out MajorList
    Loop

    rs_source.Close
    rs_target.Close
    Set rs_source = Nothing
    Set rs_target = Nothing
End Sub

Thank you in advance!

Источник

The MS Access error 3021 — No current record can occur when you try to modify the records in the Access database. Some users have reported encountering this error while using the Recordset object (DAO) to modify the records in the database or using VBA code to import tables. It may also occur when changing the location of the shared SysData folder from the Admin screen. The SysData folder is a directory folder on a shared network drive containing all files that are required for running FRx application.

Causes of MS Access Error 3021

There are many reasons that may cause the Access error 3021. Some possible reasons are:

Current record has been deleted.
Records in the MS Access database are corrupted.
Insufficient permissions to edit the file that stores the shared SysData location.
Mapped drive errors.
Wrong path to the shared SysData folder.

Solutions to Fix the MS Access Error 3021

Try the following methods to fix the MS Access error 3021 — No current record.

Method 1: Use BOF/EOF Properties to Check Records

You can get the MS Access error 3021 — No current record when you try to open an empty Recordset or if the current record has been deleted. You can’t position the current record, if the Recordset is empty. In such a case, you can check the BOF and EOF properties in a Recordset object to determine the records. If you see that the EOF or BOF property is set to True, it means that there is no record.

Note: The BOF indicates that the current record position is before the first record in a Recordset object whereas EOF indicates that the current record position is after the last record in a Recordset object.

Method 2: Check Permissions of SysData Folder

The MS Access error 3021 can occur if you do not have sufficient permissions to modify the SysData folder. You can check and set the folder permissions using the below steps:

Go to the folder, right-click on it, and then click Properties.

property wizard

In the Attribute section, check if the Read-Only checkbox is selected or not. If it is, then clear it.

folder properties for attributes

Click OK.
Now go to the Security tab and click Edit.

permissions for new folder window

Check that all users have Read, Read & Execute, Write, and Modifypermissions for the folder. If users are missing these permissions, add the permissions, and then click OK.

Method 3: Verify the Path of SysData Folder

The error 3021 — No current record may also occur when you try to change the location of the SysData folder using the Admin window or access the folder using the wrong path. So first check whether you are trying to access the folder from the correct location on the system. To check the path, follow these steps:

If there are multiple workstations, first verify all workstations have installed the same version/service pack.
Now go the FRx32.exe folder and edit the FRx32.cfg file in this directory using Notepad, to show the correct SysData location using the Standard UNC paths.

Caution: Do not edit the FRx32.cfg file prior to opening FRx, as this can cause loss of data in the shared SysData location.

Once you saved the changes to the FRx32.cfg, open FRx.

Method 4: Repair the Corrupted Database

Sometimes, the MS Access error 3021 can occur due to corrupted records in the database. In such a case, you can repair the corrupted database using Microsoft’s built-in tool — Compact and Repair. To use this tool, follow the below steps:

Open the desired database.
Select File > Info > Compact & Repair Database.

MS Access creates a copy of the compacted and repaired database at the same location.

If the Compact and Repair tool does not work or fails to repair the corrupted database, then you can use a reliable MS Access database repair tool, such as Stellar Repair for Access. This tool can repair corrupt Access database (.ACCDB and .MDB) files. It can recover all the objects of the database, such as records, macros, tables, etc. with complete integrity. The software supports Windows 11, 10, 8.1, 8, 7, Vista, 2003, and XP.

Closure

The MS Access error 3021 can occur when trying to access the records of the Access database. This error can occur due to different reasons. You can follow the methods discussed in this post to fix the error. If it occurs due to corruption, try the Compact and Repair utility in MS Access. If the utility fails to fix the issue, then use Stellar Repair for Access to repair the corrupt database file and recover all its objects

Источник

Question

Answers

Ошибка 3021. Текущая запись отсутствует.

Кто сейчас на конференции

Causes of MS Access Error 3021

Solutions to Fix the MS Access Error 3021

Method 1: Use BOF/EOF Properties to Check Records

Method 2: Check Permissions of SysData Folder

Method 3: Verify the Path of SysData Folder

Method 4: Repair the Corrupted Database

Closure

Возможно, вам также будет интересно: