TipsDBGrid

Private Sub cmdButton_Click()
    ' Reverse field order -- DBGrid will
    ' continue to display Au_ID first
    
    datCtl.RecordSource = "SELECT Author, Au_ID FROM Authors;"
    datCtl.Refresh
    
    ' If you set the RecordSource to this
    ' (note capitalization)...
    '
    '    "SELECT Author, au_ID FROM Authors;"
    '
    ' the DBGrid won't display any data in
    ' the second column
End Sub

'The workaround is to 'reset' the DBGrid by pointing it at a recordset that shares no
'common field names with the current recordset. For example, change the above
'cmdButton_Click event to the following:
Private Sub cmdButton_Click()
    Dim SQL As String
    
    ' Reset DBGrid by creating empty recordset
    ' with unique field names
    
    SQL = "SELECT Author AS [Unique Field Name] FROM Authors WHERE False;"
    datCtl.RecordSource = SQL
    datCtl.Refresh
    
    ' DBGrid will now display fields as listed
    datCtl.RecordSource = "SELECT Author, Au_ID FROM Authors;"
    datCtl.Refresh
End Sub

'Trapping DBGrid Runtime Errors
'DBGrid is designed to display an error message, not only in the event of a runtime error,
'but also under certain 'normal' conditions, such as when the Cancel parameter is set to
'True in the BeforeDelete or BeforeUpdate events. You can trap such errors and display
'your own message (or nothing at all) by adding code to the undocumented
'DBGrid_Error event:
Private Sub DBGrid_Error(ByVal DataError As Integer, Response As Integer)
    ' The action was cancelled...
    If DataError = 16389 Then
        ' Display a confirmation message,
        ' make a noise, or do nothing here
        Response = vbDataErrContinue
    End If
End Sub

'Note: The Error event is fully documented in an updated help file,
'available at Apex' ftp site.
 The documentation for DBGrid states that the control's Text 
and Value properties allow you to read or set the contents 
of a cell. This would seem to indicate that you could use 
these properties to update a field in a recordset to which 
the grid is bound. Unfortunately, that is not the case. The 
following code generates Run-time Error 438: Object doesn at 
support this property or method: 
This doesn t work:

DBGrid.Columns(0).Text = "Give me ambiguity, "
' Neither does this:

DBGrid.Columns(0).Value = "or give me something else."
'The workaround is to update the data control as recordset directly.

'The change will be reflected in the bound grid automatically:

Dim sText As String
sText = "f u cn rd ths, u cn gt a gd jb n cmptr prgrmmng."
datCtl.Recordset.Edit
datCtl.Recordset.Fields(0) = sText
datCtl.Recordset.Update
'Note: The Text and Value properties work as expected in the True DBGrid

'upgrade described above.

'DBGrid Refreshes Incorrectly When New RecordSource

'Shares Field Names With Previous One

'If you change a data control's RecordSource at runtime to a table or query that shares

'field names with the previous RecordSource, a bound 'DBGrid will not refresh correctly

'the common field names remain in their original positions rather than appearing in the

'order specified in the 'new table or query. If the common field names in the new

'RecordSource are capitalized differently than in the original, the DBGrid won't show

'any data in that column. To duplicate the problem, create a form containing a data

'control (datCtl), a DBGrid, and a command button ('cmdButton). Set the DBGrid's

'DataSource property to the name of the data control. Add the following code:

Private Sub Form_Load()
datCtl.DatabaseName = "biblio.mdb"
datCtl.RecordSource = "SELECT Au_ID, Author FROM Authors;"
End Sub

Private Sub cmdButton_Click()
' Reverse field order -- DBGrid will

' continue to display Au_ID first

datCtl.RecordSource = "SELECT Author, Au_ID FROM Authors;"
datCtl.Refresh

' If you set the RecordSource to this

' (note capitalization)...

'

' "SELECT Author, au_ID FROM Authors;"

'

' the DBGrid won't display any data in

' the second column

End Sub

'The workaround is to 'reset' the DBGrid by pointing it at a recordset that shares no

'common field names with the current recordset. For example, change the above

'cmdButton_Click event to the following:

Private Sub cmdButton_Click()
Dim SQL As String

' Reset DBGrid by creating empty recordset

' with unique field names

SQL = "SELECT Author AS [Unique Field Name] FROM Authors WHERE False;"
datCtl.RecordSource = SQL
datCtl.Refresh

' DBGrid will now display fields as listed

datCtl.RecordSource = "SELECT Author, Au_ID FROM Authors;"
datCtl.Refresh
End Sub

'Trapping DBGrid Runtime Errors

'DBGrid is designed to display an error message, not only in the event of a runtime error,

'but also under certain 'normal' conditions, such as when the Cancel parameter is set to

'True in the BeforeDelete or BeforeUpdate events. You can trap such errors and display

'your own message (or nothing at all) by adding code to the undocumented

'DBGrid_Error event:

Private Sub DBGrid_Error(ByVal DataError As Integer, Response As Integer)
' The action was cancelled...

If DataError = 1e'89 Then
' Display a confirmation message,

' make a noise, or do nothing here

Response = vbDataErrContinue
End If
End Sub

'Note: The Error event is fully documented in an updated help file,

'available at Apex' ftp site.

The documentation for DBGrid states that the control's Text
and Value properties allow you to read or set the contents
of a cell. This would seem to indicate that you could use
these properties to update a field in a recordset to which
the grid is bound. Unfortunately, that is not the case. The
following code generates Run-time Error 438: Object doesn at
support this property or method:
This doesn t work: