Call string SQL in VBA 1

[dodgyone]

What I am trying to do is call the first query string (MyQuery1) that has been created within the second query string (MyQuery2). So as shown below in MyQuery2 I want to use the previous recordsets results (rst2) in the SQL string rather than a named table or query. Is this or something similar possible?

Many thanks...

Dim db As dao.Database
Dim rst1 As dao.Recordset
Dim rst2 As dao.Recordset
Dim MyQuery1 As String
Dim MyQuery2 As String

Set db = CurrentDb()

MyQuery1 = "SELECT sf_booking.FirstName, one.Booking_ID, one.First, one.Last " & _
"FROM sf_booking INNER JOIN one ON sf_booking.BookingID = one.Booking_ID " & _
"WHERE (((sf_booking.FirstName)='" & Me.txtName.Value & "'));"
Set rst1 = db.OpenRecordset(MyQuery1)

MyQuery2 = "Select * from " & rst1 & " Where First = 'Bill'"
Set rst2 = db.OpenRecordset(MyQuery2)
rst2.MoveFirst

 

[PHV]

MyQuery2 = "Select * from (" & rst1 & ") A Where A.First = 'Bill'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodgyone]

Thanks for the help PHV but I get a type mismatch error on rst1 now. Not sure what to do with it!

Cheers...

 

[PHV]

Sorry for the typo:
MyQuery2 = "Select * from (" & MyQuery1 & ") A Where A.First = 'Bill'"

You have to get rid of the semi-colon in MyQuery1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodgyone]

Thanks PHV that worked. And it was such a simple solution as well. The simple ones are always the hardest to find!