Running a subsequent SQL Query

[bwgreen]

Hi,

I`m having an issue. I`m writing VBA code behind a form that used SQL SELECTS on various tables - the results of one query determine the next. I`m using the ADODB object library:

strSQL = "SELECT * FROM Table1 WHERE Field1 LIKE " & value1
objRecordSet.Open (strSQL)

Then later:

objRecordSet.Close
strSQL = "SELECT * FROM Table2 WHERE Field1 LIKE " & value2
objRecordSet.Open (strSQL)

But, when I access objRecordSet.Fields.Item(x).Value it shows the results of the first SQL statement, not the new one. I'm trying to find a way to reset the object - is there a way to do this?

Thanks!

 

[bwgreen]

Found a solution that seems to work - before running the next SQL query reset the ADODB object:

Set objRecordSet = New ADODB.RecordSet
objRecordSet.ActiveConnection=CurrentProject.Connection

It may not be the most elegant way to do things, but it appears to work!