Adding Records and Editing Data in a DBGrid Cell

Last reviewed: June 6, 1996
Article ID: Q149092
The information in this article applies to:
  • Standard, Professional, and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0

SUMMARY

This article describes how to programmatically edit the data and add new records to a DBGrid control. You can change the contents of a cell and those changes will appear on the screen, but they are not written to the data source bound to the DBGrid control. To write these changes to the data source, you must instead edit the underlying recordset.

MORE INFORMATION

The following example creates a simple database containing one table with four fields and five records. Each record is a number in the field. The database is going to be the data source.

When you click on a cell, the data in that cell is shown in a TextBox. When you change the contents of that cell, the changes are shown on the DBGrid control. However, when you execute the UpdateControls method through a command button, the changes disappear from the cell, indicating the data source was not updated.

You must click the Update Recordset button when you make your changes in the TextBox to ensure that the changes are written to the data source. The Update Recordset button updates the underlying recordset of the Data control. Since the DBGrid control is bound to the Data control, the changes appear in the DBGrid control.

Creating the Sample Application

The following sample application demonstrates how to edit the contents of a DBGrid cell:

  1. Start Visual Basic. Once it is running, on the File menu, click New Project. Add a Data control, a DBGrid control, a TextBox, and three Command buttons. Set the following properties for each control:

       Control       Default Name       Property       Value
       -----------------------------------------------------
    
       DBGrid        DBGrid1            DataSource     Data1
    
    

  2. Copy the following code sample to the Form1 code window:

          Option Explicit
          Dim rs1 As Recordset
          Dim db As Database
          Dim td As TableDef
          Dim fl As Field
          Dim igRow As Integer, igColumn As Integer
          Dim iFields As Integer, iRecords As Integer
          Dim vargBookmark As Variant
    

          Private Sub Command1_Click()
    
             'The Create Database button. Clicking this button creates a
             'database with four fields and five records.
    
             Set db = CreateDatabase("C:\test.mdb", dbLangGeneral)
             Set td = db.CreateTableDef("Table1")
    
             'Now that the database is created, add fields to the database
             For iFields = 1 To 4 'The last number can be changed to the number
                                  'of fields you want in the database.
                Set fl = td.CreateField("Field " & CStr(iFields), dbText)
                td.Fields.Append fl
             Next iFields
    
             db.TableDefs.Append td
    
             'Now that the database has fields, add some records through
             'a recordset
             Set rs1 = db.OpenRecordset("Table1", dbOpenTable)
             For iRecords = 1 To 5  'For each row
                rs1.AddNew          'Add a new record
    
                For iFields = 1 To 4            'For each field in the record
                   rs1("Field " & CStr(iFields)) = CStr(iFields)  'Add a number
                Next iFields
    
             rs1.Update
             Next iRecords
    
             'Close both the recordset and database
             rs1.Close
             db.Close
    
             'Populate the DBGrid control with the contents of the Recordset
             Set db = OpenDatabase("C:\test.mdb")
             Set rs1 = db.OpenRecordset("Select * from Table1")
             Set Data1.Recordset = rs1
    
             Command1.Visible = False
             Command2.Visible = True
             Command4.Visible = True
          End Sub
    
          Private Sub Command2_Click()
             'The Update Database button. Clicking this button saves the
    
contents
         'of the text box to the database. Since the contents of the
recordset
         'is being modified, the contents are saved to the database after
         'executing the Update method.

         Data1.Recordset.Edit
         Data1.Recordset.Fields(igColumn) = Text1.Text
         Data1.Recordset.Update
      End Sub

      Private Sub Command3_Click()
         'The Update DBGrid button. Clicking this button executes the
         'UpdateControls method on the Data control to demonstrate that
         'changing the cell in a bound DBGrid control does not save
         'the new information to the database. To save these changes,
         'the underlying recordset from the Data control must be modified.

         Data1.UpdateControls
      End Sub

      Private Sub Command4_Click()
         'The Add New Record button. Clicking this button adds new records
         'to the recordset. Use the following code to add a new record to
         'the DBGrid control.

         'Set DBGrid and Data Control Properties to
         'allow new records to be added
         DBGrid1.AllowAddNew = True
         Data1.EOFAction = vbAddNew
         Data1.Recordset.MoveLast
         Data1.Recordset.MoveNext
         DBGrid1.Row = DBGrid1.VisibleRows - 1
         Data1.Recordset.AddNew
         For iFields = 1 To 4                'For each field in the record
            'Add the contents of the text box
            Data1.Recordset("Field " & CStr(iFields)) = Text1.Text
         Next iFields
         Data1.Recordset.Update

      End Sub

      Private Sub DBGrid1_Change()
         Command3.Visible = True
      End Sub

      Private Sub DBGrid1_MouseUp(Button As Integer, Shift As Integer, _
                                  X As Single, Y As Single)
         Command2.Visible = True
         igColumn = DBGrid1.ColContaining(X)
         igRow = DBGrid1.RowContaining(Y)
         vargBookmark = DBGrid1.RowBookmark(igRow)

         Text1.Text = DBGrid1.Columns(igColumn).CellValue(vargBookmark)

      End Sub

      Private Sub Form_Load()
         Command1.Visible = False
         Command2.Visible = False
         Command3.Visible = False
         Command4.Visible = False
         Command1.Caption = "Create Database"
         Command2.Caption = "Update Database"
         Command3.Caption = "Update DBGrid"
         Command4.Caption = "Add New Record"

         'If the database does not exist, show the
         'Create Database button
         If Dir("C:\test.mdb") = "" Then
            Command1.Visible = True
         Else
            'Open existing database
            Set db = OpenDatabase("C:\test.mdb")
            Set rs1 = db.OpenRecordset("Select * from Table1")
            Set Data1.Recordset = rs1
            Command4.Visible = True
         End If

      End Sub

      Private Sub Text1_Change()
         Command2.Visible = True
      End Sub

Running the Sample Application

  1. On the Run menu, click Start, or press F5.

  2. Click on a cell on the DBGrid control and change the contents of that cell. Click the Update DBGrid button. Note that your changes disappear from the DBGrid. Clicking on a blank area on the DBGrid control gives error 6148 - Invalid row number.

  3. Click on a cell. Note that the contents of the cell appear on the TextBox. Change the contents of the TextBox and then click the Update Recordset button. Note that your changes now appear on the DBGrid control.

  4. Click the Add New Records button to add a new record to the DBGrid
control. The contents of the text box will be added as a new record.


Additional reference words: 4.00 vb4win vb4all
KBCategory: kbusage
KBSubcategory: PrgCtrlsCus PrgOther




THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 6, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.