PRB: No Current Record Error In VB When Database is Empty

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

SYMPTOMS

If a data control and text box are both bound to an empty table in a database, clicking the data control arrows gives this error:

   No Current Record

The program has difficulty recovering because of the recurring "No Current Record" errors. The On Error statement fails to trap this error.

CAUSE

The program does not know the table is empty until the automatic record update occurs. That automatic record update occurs when you click the data control arrow or you enter text in the text box and then execute an AddNew or Edit method.

When the table is not empty the automatic update is a nice feature, but when the table is empty the automatic update causes the no current record error. The error message occurs because the underlying recordset contains no records.

You must execute AddNew to create a current record before doing anything that causes an automatic record update.

WORKAROUND

To work around this behavior, execute the AddNew method on an empty database table before allowing the user to click the data control or enter text into the bound text box control. For example, set the Enabled property for the text control and data control to False at the beginning of the program. Then you can force the user to click a command button that executes the AddNew method before enabling the text and data controls.

STATUS

This behavior is by design. This design is under review and will be considered for enhancement in a future release.

More Information

How to Create an Empty Microsoft Access Database

Before using Example 1 or 2 shown below, create an empty database by following these steps in Visual Basic:

1. Open the Data Manager program by choosing it from the Window menu in Visual Basic or by running DATAMGR.EXE from the Windows File Manager.

2. In the Data Manager, choose New Database from the File menu and select Access 1.1.

3. Click the New button and enter tbl1 for the table name.

4. Click the Design button. Then click the Add button. Enter fld1 for the Field Name and select Integer for the Field Type.

5. Save this Microsoft Access database with the name TEST1.MDB. Close the Data Manager.

Example One: Steps to Reproduce Behavior using a Data Control

1. Start a new project in Visual Basic. Form1 is created by default.

2. Create the empty Microsoft Access database (TEST1.MDB) as described above.

3. Add the following controls and set the following properties:

      Control Name  Property       New Value         NOTE
      ------------------------------------------------------------------------
      Data1         DatabaseName   C:\VB\TEST1.MDB   Empty MDB created above.
      Data1         RecordSource   tbl1              Table name.
      Text1         DataSource     Data1             Name of data control.
      Text1         DataField      Fld1              Field name.
      Command1      Caption        "Press for AddNew"
   
   

4. Add the following code in the Command1 Click event procedure:

      Sub Command1_Click ()
         data1.Recordset.AddNew
         text1.SetFocus
      End Sub
   
   

5. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program.

6. Either of the following will give the no current record error:

       - Click any of the arrow buttons on the data control.
   

       - Enter text into the text box, and then click the command button.
   

   Notice that if you rerun the program, press the Command1 button first, and then enter some text into the Text1 text box, you can then click any of the arrow buttons on the data control without getting the error.

Example One Workaround

To work around this behavior, set the Enabled property for the text and data controls to False at design time or in the Load procedure for the form. Then add the following to the top of the Command1_Click procedure before the AddNew:

   Data1.enabled = True
   Text1.enabled = True

Example Two: Steps to Reproduce Behavior Using Object Variables

1. Start a new project in Visual Basic. Form1 is created by default.

2. Create the empty Microsoft Access database (TEST1.MDB) as described above.

3. Add a command button. Enter "Press to Test" as its Caption property.

4. Add the following code in the general Declarations section of Form1:

   Dim db As database Dim ds As dynaset

5. Add the following code in the Command1 Click event procedure:

      Sub Command1_Click ()
         Set db = OpenDatabase("TEST1.MDB")
         Set ds = db.CreateDynaset("tbl1")
         ' Execute the following line to work around problem:
         ' ds.AddNew
         If IsNull(ds(0)) Then  'The No Current Record error occurs here
            Print "No entry"
         Else
            Print ds(0)
         End If
      End Sub
   
   

6. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program.

REFERENCES

Additional information can be found in the Visual Basic Help menu. The no current record error is described in the Visual Basic Help topic "Data Access error messages." Here is that description:

   No current record. Error 3021.

   This error occurs following the unsuccessful application of one of the
   FindFirst, FindLast, FindNext, FindPrevious Methods or the Seek Method,
   or when the underlying recordset contains no records. Move to or select
   a record, and then try the operation again.

   The AddNew method clears the copy buffer in preparation for creating a
   new record in a Table or Dynaset. AddNew sets all fields in the copy
   buffer to Null and makes it the current record. After putting data in
   the record, you can use the Update method to add the record to the
   recordset. Update is automatically invoked with a data control if an
   Edit or AddNew operation is pending when you use one of the Find or
   Move methods.