Displaying subdata in a datagrid from access 2000 database 1

[Oliver2003]

Hi, I'm trying trying to display subdata in a datagrid. e.g in access I have the tables Authors and Books (a one to many relationship which is linked by the field AuthorID)
What I would like to do is display on the same form, the author details using text boxes (I have this part working) and then the books they have written in a datagrid or similar control.

at the moment I just have:

rsRecordSet.Source = "Select * From Authors"

as the record source, do I need to define this again for the Books table? How do you get the datagrid to display only related items?

Thanks in advance for any help or pointers

Oliver

 

[CCLINT]

Try something like this:

Private WithEvents rsMain As ADODB.Recordset
Private rsBooks As ADODB.Recordset

Private Sub rsADO_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
   
   If rsBooks = Nothing Then Set rsBooks = New ADODB.Recordset
   With rsBooks
       If .Status = adStatusOpen Then
           If rsMain.Collect(&quot;AuthorID&quot;) <> rsBooks.Collect(&quot;AuthorID&quot;) Then
               .Close
               .CursorType = adUseClient
               .Open &quot;SELECT * FROM Books WHERE AuthorID=&quot; & rsMain.Collect(&quot;AuthorID&quot;)
            End If
       End If
   End With
   Set DataGrid1.Recordset = rsBooks
End Sub

 

[Oliver2003]

CCLINT, thanks for the example - I have the middle part of the code working (.open ...) but when I include:

If rsBooks = Nothing Then Set rsBooks = New ADODB.Recordset

I get the error Invalid use of object and the word Nothing is highlighted - Should this be set to something else?

Again thanks for the example its really helped.

 

[CCLINT]

oops:

If rsBooks Is Nothing Then Set rsBooks = New ADODB.Recordset

 

[Oliver2003]

CCLINT, that works now but when I include the line:

If .Status = "adStatusOpen" Then (added quotes - is this right)

I get the error 'operation is not allowed when the object is closed'

does this mean that rsBooks is not open? What would fix this?

I really appreciate your help and bearing with me.

 

[CCLINT]

Oops. Guess I type too fast (I did this pretty much out of my head - sorry)

If .State = adStateOpen Then


>>(added quotes - is this right)
NO!