how to test if row from one recordset field value appears in another recordset

[pb21]

Hi

I have two recordsets one based on a query:

'query to get recordset of booked products per student
StrSelect = "SELECT STUDENT.StudentID, COURSEBK.[COURSE-DSName]"
StrFrom = "FROM STUDENT INNER JOIN COURSEBK ON STUDENT.StudentID = COURSEBK.[STUDENT-DSN]"
StrWhere = "WHERE (((STUDENT.StudentID)=" & StrStudentId & "));"
StrQuery = StrSelect & StrFrom & StrWhere

Set RstBookedCheck = db.OpenRecordset(StrQuery)

and a second based on a products table:

'a recordset of a 'new' products table
Set RstProductCheck = db.OpenRecordset("TblProduct")

I need to check if the booked product code is a new product or old product so my idea was to go through the first recordset line by line and check the product code against the entries in the new product table.

if any of the booked items appear in there then i know they have new products against the student otherwise the student is booked with old products (that dont appear in the product table) by mutual exclusion.

so i started in this vein:
' make sure something in the recordset
If Not RstBookedCheck.BOF And Not RstBookedCheck.EOF Then

' items in recordset

With RstBookedCheck
' go through each row of first recordset until eof
Do While Not .EOF
get the product code from first row
strCriteria = "[product code]='" & .Fields(2).Value & "'"
'now check if that product appears in the product table
With RstProductCheck
.FindFirst strCriteria

End With

.MoveNext
Loop

End With


Else
' no items in course booking table

End If

'******************************

I was not sure how to test if the findfirst was true so i can set the searchflag to true if a product was found or false if not and not sure either if i got the loops correct.



regards in advance for help.
Peter

 

[KeithG]

There is a much easier way to do this. You don't even need to use VBA. Make a third and use the first two queries as the recordsets and make a left join from query1 to query 2 on product code. To bring back only new products you would put is null in the criteria row of the product code in query 2

 

[pb21]

I will try that Keithg thank you i had not thought of that
regards

 

[pb21]

I have used the suggested method and produced the folloowing:

SELECT QryBookedCourses.StudentID, QryBookedCourses.[COURSE-DSName], QryNewProducts.[Product Code]
FROM QryBookedCourses LEFT JOIN QryNewProducts ON QryBookedCourses.[COURSE-DSName] = QryNewProducts.[Product Code]
WHERE (((QryNewProducts.[Product Code]) Is Not Null));

it works fine. I do have to do this in vba as i want to use the result from this ie is record count from this query >0 so i can decide which form to show.

how do i do this in vba on the fly? i would prefer not to have to create queries in the grid. Just to create this all dynamically.

i know how to creat each query separately on the fly but not how to combine them as recordsets.

regards

 

[KeithG]

Why do you have to create on the fly? Isn't the sql statement going to be the same each time? Just create the query in the query grid and use vba to opne it.

Dim myRec as Recordset

set myRec=Currentdb.openrecordset("NameOfQuery")

 

[pb21]

no the id of the student is grabbed from a form in vba and then vba will create a rowsource string dependent on the result. The database is awash with queries from an earlier developer and i wanted to try and keep them down.

but as you say i might be going about this the wrong way.

 

[KeithG]

You could create a query def object and a parameter object and set the parameter object with the value from the form and then run the query to see if it brings back any records.

 

[pb21]

Thank you I will go with your suggestion, thank you for your advice it is much appreciated.
kind regards
Peter