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

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:\Users\807140\Downloads\" & 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:\Users\807140\Downloads\" & 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.7k7 gold badges36 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:\Users\807140\Downloads\" & 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:\Users\807140\Downloads\" & rs1.Fields("CostCentre") & ".xlsx"

answered Jul 28, 2016 at 20:50

HansUpHansUp

96k11 gold badges77 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:\Users\807140\Downloads\" & 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,3714 gold badges24 silver badges38 bronze badges

Источник

You may receive the Access run time error 3021- “No current record.” when trying to modify the records in the database using the Recordset object (DAO) or running VBA (Visual Basic Analysis) code to import the tables. It usually occurs due to insufficient permissions to edit a file or an incorrect shared SysData location. In this blog, I will discuss other causes of no current record Access & the possible fixes to solve the below-shown error.

Also, I will mention an advanced Access Database Repair Software that can assist to fix the error if it has arisen due to file corruption.

Rated Excellent on Trustpilot
Free MS Access Database Repair Tool
Repair corrupt MDB and ACCDB database files and recover deleted database tables, queries, indexes and records easily. Try Now!

Download
By clicking the button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

Quick Fixes:

Grant Permissions to SysData Folder
Use BOF or EOF Properties
Check SysData Folder’s Path to Fix Error 3021
Use Compact & Repair Tool
Recommended- Repair Corrupted & Damaged MS Access Database

What Is No Current Record in Access?

When MS Access no current record error occurs it simply means that you cannot modify the records in the database or change the shared SysData folder location from an Admin screen. However, it is important to address it as soon as possible.

But before that, it’s important to know the major factors behind the occurrence of this error. So, let’s take a look:

Due to the Access database records corruption.
When a current record is deleted.
Showing an error in the mapped drive.
Inadequate permissions for editing the database file that is saved in the shared SysData location.
Incorrect path of shared SysData folder.

After knowing the causes, it’s time to explore the solutions to fix the runtime error ‘3021’ No current record” in Access.

Fix 1- Grant Permissions to SysData Folder

As already mentioned in the causes section, inadequate permissions for editing the database that is saved in the shared SysData location can trigger this error. In that case, you will have to grant the required permission to the folder. Follow the below steps to do so:

Open the folder and make right-tap on it >> click on the Properties.

Under the Attribute, make sure that Read-Only checkbox is not selected. If it’s selected then clear it & click OK.

After this, click on Security tab & click Edit.
Verify that all the users have Write, Read, Modify, and Read & Execute permissions for that folder. But if users don’t these permissions, then add permissions & click OK.

Fix 2- Use BOF or EOF Properties

Another effective method that you can try to fix MS Access no current record error is to use BOF or EOF properties for checking records.

The Access “No Current Record error” occurs after the failure of the Find methods or when the original recordset contains no record or has been deleted somehow.

In that case, you can simply check the BOF or EOF properties using the Recordset object in order to determine the database records. If the BOF or EOF property is in True, it means there’s no record.

Also Read- Fixes for Access Error 3048: Cannot Open Any More Databases

Fix 3- Check SysData Folder’s Path to Fix Error 3021: No Current Record Access

The error code 3021 can also trigger due to the incorrect path of shared SysData folder. Therefore, it is important to first check whether you’re attempting to access the SysData folder from the right location of your system.

Also, check that the workstation has the same version/service pack level as other workstations. For this:

Search the target directory for FRx32.exe, which open FRx Designer.
Now edit the FRx32.cfg file in this directory using Notepad, to show the correct SysData location using UNC mapping.

For Example: [SysData]Directory=\\\\Servername\\Sharename\\SysData\\]

CAUTION: Don’t do editing of the FRx32.cfg file prior to open FRx. This may lead to data loss in the shared Sysdata location.

After making the complete saving of the FRx32.cfg, open FRx.

Fix 4- Use Compact & Repair Tool to Repair Corrupted Access Database

You can try repairing the database file that contains corrupted records using an in-built Access Compact and Repair tool.

Here’s how you can use this utility to fix database corruption:

Step 1- Open the Access database that contains corrupted records.

Step 2- Then, go to the File >> Info option.

Step 3- Click on Compact & Repair Database.

After completion of these steps, you will get a copy of the fixed file in the same place.

Recommended- Repair Corrupted & Damaged MS Access Database

In case, if your Access database is severely corrupted and the Compact & Repair tool is unable to fix it, you must opt for Access Repair and Recovery Tool. This software can repair corrupted .accdb and .mdb files along with restoring the macros. Moreover, it shows a preview of all the recoverable database objects before saving them to the chosen location.

So, just download this tool on your PC to fix Access database corruption. You can even go for the free trial version of this software.

* By clicking the Download button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

Here are the complete steps to use this program:

access-repair-main-screen

access-repairing-completed

How to Search For a Specific Record in MS Access?

If your Access Database is showing a “No Current Record” issue when you try to work with the Access records, our recommendation is to first search for the specific records in the table or form. You can do so by using the Find tab in the Find and Replace dialog box.

Steps To Search For A Specific Record In MS Access Using Find & Replace:

First of all, open the Table Or Form and then tap on the Field which you want to look for.
Now on the home tab, in the find group, tap on the Find option, or press CTRL+F.

This option will open the Find and Replace dialog box, with the Find tab selected.
In the Find What box, just write the value which you want to look for.

Update the field that you want to search or make a search for the complete underlying table.
Choose the most suited option in the Look-In list.

Tip: The matching list shows your comparison operator (such as “equals” or “contains”). In order to make a broader search, in the Match list, click on Any Part of Field.

In your Search list, choose All option, and then hit the Find Next option.
When the searched item is highlighted, tap to the Cancel option in the Find and Replace dialog box to close the dialog box.
Finally, you will get all the records that match well with your condition get highlighted.

Also Read- Resolve Access Runtime Error 3044: Not A Valid Path

Related FAQs:

What Is a Record in Access?

MS Access record consists of fields, like name, address, and telephone number. Basically, a Record is commonly known as a row and its consisting field is known as a column. MS Access allows easy access to records such as adding new data, editing already existing data, and deletion of unwanted records in the table directly in the datasheet view.

How Do I Fix Error 3021?

You can fix run time error 3021 in Access by applying these methods:

Use BOF or EOF Properties for checking records.
Allow permission to SysData folder.
Repair corrupted Access database.
Check the SysData folder’s path.

Bottom Line

The “runtime error 3021- No current record Access” can trouble you & hamper your work. Well, in such a case, you can apply the fixes mentioned above to solve this error effortlessly.

However, if the error has arisen due to database corruption, then use the Access Repair Tool mentioned in this post.

That’s all…

tip Still having issues? Fix them with this Access repair tool:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
Click Browse and Search option to locate corrupt Access database.
Click Repair button to repair & preview the database objects.

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.

Источник

Many Access users have reported encountering the error 3021 – No current record while using the Recordset Object in the VBA code. This error occurs when you try to run a query that requires a current record but is unavailable or when you try to edit the records in the database. It can also occur when you use incorrect queries while importing tables into the database. This article will discuss the methods to fix this VBA error in MS Access.

Table of Contents

Why does MS Access Error 3021 – No Current Record Occurs?

There are many reasons for the Access error 3021 – No current record error. Some of them are:

Mapped drive issues
Corrupted records in the Access database
Incorrect path to the shared SysData folder
You do not have permission to edit the records in the file
When the recordset is empty
The recordsets are not properly initialized

Methods to Resolve MS Access Error 3021 – No Current Record Error

First, make sure the query you are using to modify the record is correct. Check if the conditions are correctly defined. If still the problem is not resolved, then follow the below steps:

Method 1: Verify BOF/EOF Properties to Check Records

The error 3021 can occur if you try to open a recordset that is unavailable or empty. In Access, you can’t position the empty recordset. To check this, you can use BOF/EOF property. This property helps you determine the position of the current records. If the BOF/EOF properties are set as True, then there is no current record.

Method 2: Check and Correct the Path of the SysData Folder

The SysData folder is a directory folder on the network containing all the important files that are required to execute FRx database files. If the path to this shared folder is incorrect, then you can get the error 3021. You can check and correct the folder location to fix the issue. Here are the steps:

First, check whether all the workstations have installed the same service pack.
Then, locate the exe folder and use Notepad to edit the FRx32.cfg file to show the correct SysData location using the Standard UNC paths.

Caution: Changing the FRx32.cfg file prior to opening FRx can lead to data loss in the shared SysData location.

Once you applied all the changes to the FRx32.cfg file, open FRx.

Method 3: Verify SysData Folder Permissions

You may be unable to edit the SysData folder if you do not have sufficient permissions to modify this folder. In such a case, you can verify and change the folder permissions. Follow these steps:

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

Go to the Attribution section and check whether the Read-only checkbox is checked or not. If it is selected, then clear it.

Select OK.
Locate the Security option and then click Edit

Check if all the users have complete permissions (Read, Read & Execute, Write, and Modify) for the folder. If not, then assign the permissions and select OK.

Method 4: Repair Your Access Database

The MS Access Error 3021 – No Current Record can occur if the Access database contains corrupted records or tables. You can repair the database using the Compact and Repair database tool in MS Access. Here are the steps:

Note: Ensure that no other user is using the database file.

Open Microsoft Access.
On the Templates page, double-click the Blank Database option.
Select File > Close.

Select Database Tools and then select Compact and Repair Database.
On the Database to Compact From window, navigate to the affected Admission database file and double-click on it.
It resolve make a copy of the repaired file at the same place.

The Compact and Repair tool can help fix minor corruption issues within the database. However, Compact and Repair may not work in certain scenarios. If it does not repair the database, opt for a professional Access database recovery tool, such as Stellar Repair for Access. It can easily repair severely corrupted database files and recover all the objects, including objects and macros, with complete consistency. This application is quite powerful and extremely easy-to-use, and helps you fix errors that occurred due to corrupted data in the database. It is compatible with all the MS Access versions.

Closure

You can get the MS Access error 3021 – No current record when trying to access data from an unavailable record set or when editing records in the database file. You can try the overhead methods to fix the issue. If the Access database file is severely corrupted, then use an advanced Access database recovery tool, like Stellar Repair for Access to repair the corrupt file. Download the software’s demo version to preview the recoverable objects in the database.

Источник

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!

Источник

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

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

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

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 0

Источник

Quick Fixes:

What Is No Current Record in Access?

Fix 1- Grant Permissions to SysData Folder

Fix 2- Use BOF or EOF Properties

Fix 3- Check SysData Folder’s Path to Fix Error 3021: No Current Record Access

Fix 4- Use Compact & Repair Tool to Repair Corrupted Access Database

Recommended- Repair Corrupted & Damaged MS Access Database

How to Search For a Specific Record in MS Access?

Steps To Search For A Specific Record In MS Access Using Find & Replace:

Related FAQs:

What Is a Record in Access?

How Do I Fix Error 3021?

Bottom Line

Why does MS Access Error 3021 – No Current Record Occurs?

Methods to Resolve MS Access Error 3021 – No Current Record Error

Method 1: Verify BOF/EOF Properties to Check Records

Method 2: Check and Correct the Path of the SysData Folder

Method 3: Verify SysData Folder Permissions

Method 4: Repair Your Access Database

Closure

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

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

Интересное по теме: