Using List box with VB6 Data Grid control

[Carl]

I'm using a detached ADO Recordset to bind the grid. I use a list box to pick
items off the list for data to put in several columnsof the grid. The code
works fine when there's more than 1 record and I'm adding a new record but
if there isn't a record and I try to add one I have problems. After I click
the item from the list box and try to put the item back in the cell I get
a Current row is unavailbel error. The datagrid1.row =-1 because it's in
addnew mode. If I change the row to 1 or 0 the focus get's set to another
control instead of back to the grid. Any ideas?
thanks,
Carl

[Michael]

"Carl" <cjsadlier@hotmail.com> wrote:
>
>I'm using a detached ADO Recordset to bind the grid. I use a list box to
pick
>items off the list for data to put in several columnsof the grid. The code
>works fine when there's more than 1 record and I'm adding a new record but
>if there isn't a record and I try to add one I have problems. After I click
>the item from the list box and try to put the item back in the cell I get
>a Current row is unavailbel error. The datagrid1.row =-1 because it's in
>addnew mode. If I change the row to 1 or 0 the focus get's set to another
>control instead of back to the grid. Any ideas?
>thanks,
>Carl


Maybe try using the filter property on the grid's disconnected recordset.
Paste the following into a form with a combo box 'combo1' on top and a oledb
datagrid 'datagrid1' beneath.

Michael

Option Explicit

Private Sub Combo1_Click()
Dim rs As ADODB.Recordset
'set the grid's datasource to rs
'reapply filter then set it back to the grid
'without reconnecting to the database
Set rs = DataGrid1.DataSource
rs.Filter = "Author = '" & Combo1.Text & "'"
Set DataGrid1.DataSource = rs
Set rs = Nothing
End Sub

Private Sub Form_Load()
Dim rsList As New ADODB.Recordset
Dim rsGrid As New ADODB.Recordset
Dim cn As New ADODB.Connection
Const SQL_GRID As String = "SELECT Titles.Title, Titles.ISBN, Authors.Author,
" _
& "Titles.[Year Published], Publishers.[Company Name] " _
& "FROM Publishers INNER JOIN (Authors INNER JOIN ([title author] " _
& "INNER JOIN Titles ON [title author].ISBN = Titles.ISBN) ON " _
& "Authors.Au_ID = [title author].Au_ID) ON Publishers.PubID = Titles.PubID
" _
& "ORDER BY Titles.Title"

'set the cursors to clientside
rsList.CursorLocation = adUseClient
rsGrid.CursorLocation = adUseClient

'open the datasource
cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;"
_
& "Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"
If cn.State = adStateOpen Then
'get the recordsets
rsList.Open "Select Author From Authors order by Author", cn, _
adOpenDynamic, adLockBatchOptimistic, adCmdText
rsGrid.Open SQL_GRID, cn, adOpenDynamic, adLockBatchOptimistic, adCmdText
'disconnect the recordsets
Set rsList.ActiveConnection = Nothing
Set rsGrid.ActiveConnection = Nothing

cn.Close
End If
Set cn = Nothing

Set DataGrid1.DataSource = rsGrid

Combo1.Text = ""
'populate the combo
Do Until rsList.EOF
Combo1.AddItem rsList("Author")
rsList.MoveNext
Loop
'set the recordsets to nothing
Set rsList = Nothing
Set rsGrid = Nothing


End Sub

Private Sub Form_Resize()
DataGrid1.Height = Me.ScaleHeight - DataGrid1.Top - 60
DataGrid1.Width = Me.ScaleWidth - 120
End Sub