Using queries in if statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to find a way of determining if a table contains records in one
that are not in another from code. I have written the following unmatched
query that gives the number of records that are unmatched.

SELECT Count(tbl_Costs_Temp.ProductID) AS CountOfProductID
FROM tbl_Costs_Temp LEFT JOIN tbl_Product_HDR ON tbl_Costs_Temp.ProductID =
tbl_Product_HDR.ProductID
GROUP BY tbl_Product_HDR.ProductID
HAVING (((tbl_Product_HDR.ProductID) Is Null));


How do I refer to this in code without opening the query? The test would be
if True then the number of records in the query should be greater than 0.
This is a start of what I have in mind....

Am i approaching this the right way?

Bruce

eg
If myQuery >0 then
msgbox("Use have new Products")
End
Else
'do something'
End If
 
Hi Allen,

I will work through your example. I have had trouble understanding
recordsets in general but now see I need to get through this to achieve some
of the things I am aiming at. You has given expert advise to me on a number
of occasion and it is of great help.

What text books to you recommend I study? Something that helps to
understand the technical concepts..

Bruce
 
A recordset is just like a query that occurs only in memory (not shown on
screen), so you can look at the results in code.

Because you already have your query statement ready, it's very easy:

Function HowMany() As Long
Dim db As DAO.Recordst
Dim strSQL As String

'Open a recordset based on the query statement.
strSQL = "SELECT Count(tbl_Costs_Temp.ProductID) AS ...
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)

'Check that we got a record.
If rs.RecordCount > 0 Then
'Set the return value of the function to the value of the field in
this record.
HowMany = Nz(rs!CountOfProductID, 0)
End If
rs.Close
Set rs = Nothing
End Function


There are heaps of books out there. One that will take you a good distance
without assuming you already know heaps is:
Title: Microsoft Office Access 2003
Author: John L Viescas
Publisher: Microsoft Press
 
Back
Top