Hello all, so here's an example of what I'm doing. I have a sequence of nested tables with "deletion requests" to allow database admins to process deletions. Anyway, I don't want the admins to erroneously delete all related children if it wasn't the intention of the deletion requestor, so I decided to add in a handy message that indicates how many children records (if any) are tied to the record being deleted.
The issue I am experiencing stems from the fact that DAO will always return a row, even if the query is empty. I have tested this using the access SQL builder with the same query and it comes back empty when it should, whereas the DAO will always return 1 row given the same data. Here is an example of the query:
And here it is again in Access SQL format:
Now, if there is 1 child record it will return 1 as the RecordSetVar.RecordCount total. However, it will also do this if the query is empty (no child records). If there are more than 1 child records, it works as intended...so it's only the instance where the query should be empty that is the problem here for me.
Also, I can't for the life of me figure out how to actually determine whether the row is null, as I'm assuming that this will be the deciding factor (if recordcount = 1 And thisrowvalue = "" then blah). I've tried using the GetRows() function to return the values to a variant, then use something like the following to extract the information:
But...obviously it doesn't work for me...Any ideas on a workaround?
The issue I am experiencing stems from the fact that DAO will always return a row, even if the query is empty. I have tested this using the access SQL builder with the same query and it comes back empty when it should, whereas the DAO will always return 1 row given the same data. Here is an example of the query:
Code:
SQL = "SELECT * FROM (" & _
"SELECT tbl_CustEvidenceIDQuery.EvidenceID, '' AS LogID " & _
"FROM tbl_CustEvidenceIDQuery " & _
"WHERE (tbl_CustEvidenceIDQuery.CustodianID = " & Me.CustodianID.Value & _
" AND tbl_CustEvidenceIDQuery.CustodianID is not null)" & _
" UNION " & _
"SELECT '' AS EvidenceID, tbl_CustSafeInventoryIDQuery.LogID " & _
"FROM tbl_CustSafeInventoryIDQuery " & _
"WHERE (tbl_CustSafeInventoryIDQuery.CustodianID = " & Me.CustodianID.Value & _
" AND tbl_CustSafeInventoryIDQuery.LogID is not null) " & ")"
Code:
SELECT * FROM (
SELECT tbl_CustEvidenceIDQuery.EvidenceID, '' AS LogID
FROM tbl_CustEvidenceIDQuery
WHERE (tbl_CustEvidenceIDQuery.CustodianID = [Enter ID]
AND tbl_CustEvidenceIDQuery.EvidenceID is not null)
UNION
SELECT '' AS EvidenceID, tbl_CustSafeInventoryIDQuery.LogID
FROM tbl_CustSafeInventoryIDQuery
WHERE (tbl_CustSafeInventoryIDQuery.CustodianID = [Enter ID]
AND tbl_CustSafeInventoryIDQuery.LogID is not null)
);Also, I can't for the life of me figure out how to actually determine whether the row is null, as I'm assuming that this will be the deciding factor (if recordcount = 1 And thisrowvalue = "" then blah). I've tried using the GetRows() function to return the values to a variant, then use something like the following to extract the information:
Code:
tmp = RecordSetVar(0,0) '1st row, 1st col
if(tmp = "") then blah
