Access vba ошибка 3021 текущая запись отсутствует

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.

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:

1. Due to the Access database records corruption.
2. When a current record is deleted.
3. Showing an error in the mapped drive.
4. Inadequate permissions for editing the database file that is saved in the shared SysData location.
5. 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:

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…

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.

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!

---

---

---

---

---

---

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

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

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