Passing Recordsets

[bladey08]

Hi
I'm having a problem with my program, basically I am trying to query two Access tables contained in the same database to display various results but I cant work out once the user as input a day (from tblbookings) and a room capacity (from tblrooms) how to display them both together. Here is the program:

Sub testsearchAccessdata()
Dim cn1 As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim Exvisual As Workbook
Dim row As Integer
Dim sht As String
 
 
Set Exvisual = GetObject("C:\Assignment\av.xls")
 
With cn1
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString = "Data Source=C:\Assignment\roomdata.mdb;Persist Security Info=False"
  .Open
End With
 
 
With rs1
      .ActiveConnection = cn1
      .Source = "select * from tblrooms where capacity <= " & Cells(5, 2) & " "
      .Open
 
 End With
 
row = 8
While Not rs1.EOF
      Cells(row, 1) = rs1!RoomCode
      Cells(row, 2) = rs1![Campus]
      Cells(row, 3) = rs1![capacity]
      row = row + 1
      rs1.MoveNext
 
 
With cn2
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString = "Data Source=C:\Assignment\roomdata.mdb;Persist Security Info=False"
  .Open
 End With
 
With rs2
      .ActiveConnection = cn2
      .Source = "select * from tblbookings where [roomcode] = '" & Cells(row, 1) & "'"
      .Open
End With
 
 
While Not rs2.EOF
'      Cells(row, 4) = rs2![activity name]
'      Cells(row, 5) = rs2![Description]
'     Cells(row, 6) = rs2!Campus
       Cells(row, 4) = rs2![Day]
      row = row + 1
      rs2.MoveNext
Wend
Set rs2 = Nothing
Set cn2 = Nothing
 
 
Wend
 
Set rs1 = Nothing
Set cn1 = Nothing
 
 
End Sub

[mafaisal]

Hello
I think both of table contain roomcode then change the query to Following way

With rs1
      .ActiveConnection = cn1
      .Source = "select a.*,b.* from tblrooms a, tblbookings b where a.capacity <= " & Cells(5, 2) & " And a.RoomCode = b.RoomCode  "
      .Open
 
 End With
 
 

Then There is no need of sec query

Try

Faisal