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:

[CODE=vb]Sub testsearchAcces sdata()
Dim cn1 As New ADODB.Connectio n
Dim cn2 As New ADODB.Connectio n
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:\A ssignment\av.xl s")

With cn1
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = "Data Source=C:\Assig nment\roomdata. mdb;Persist Security Info=False"
.Open
End With


With rs1
.ActiveConnecti on = 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"
.ConnectionStri ng = "Data Source=C:\Assig nment\roomdata. mdb;Persist Security Info=False"
.Open
End With

With rs2
.ActiveConnecti on = 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[/CODE]

[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