How to test a query "if no results" then...

  • Thread starter Thread starter ThriftyFinanceGirl
  • Start date Start date
T

ThriftyFinanceGirl

I need to run a test query after a user updates a form to determine the next
step in the process, but I'm having trouble with Recordsets. (The bane of my
existence) All I want to know is whether or not the query returns any
records (recordcount 1 or more) then I can move on from there.

I can open and run action querys through code just fine, but I don't know
how to grab a piece of information from a simple select query. :-(
 
Thought I would post my code that isn't working...

Dim db As Database
Dim Rst As Recordset
Dim NumRecords As Long
Dim SQL As String

Set db = CurrentDb()
SQL = "SELECT Count(TaxRatesBases.TaxID) AS NumRecord" & _
"FROM (Location LEFT JOIN TaxRatesBases ON Location.BusUnit =
TaxRatesBases.BusUnit) LEFT JOIN LocationObjects ON TaxRatesBases.TaxID =
LocationObjects.TaxID" & _
"GROUP BY TaxRatesBases.BusUnit, LocationObjects.ObjectAcctNo" & _
"HAVING
(((TaxRatesBases.BusUnit)=[Forms]![frmlocation]![BusUnit]) AND
((LocationObjects.ObjectAcctNo) Is Null));"

Set Rst = db.OpenRecordset("SQL")
If Rst.RecordCount = 1 Then

'notify the user
If MsgBox("We need to create LocationObjects for your New Tax Rate!
Continue?", vbYesNo) = vbYes Then
DoCmd.OpenQuery "qryMakeTempTblTax"
DoCmd.OpenQuery "PushNewTaxLocObj"
DoCmd.DeleteObject acTable, "TempAddNewTaxLocObj"

MsgBox "LocationObjects Created..."

'move to the LocationObjects Tab
Forms!frmlocation.tabObjects.SetFocus
Else
MsgBox "Your Tax will not be available for use until you create
the matching LocationObjects"
End If
End If
 
ThriftyFinanceGirl said:
I need to run a test query after a user updates a form to determine the
next
step in the process, but I'm having trouble with Recordsets. (The bane of
my
existence) All I want to know is whether or not the query returns any
records (recordcount 1 or more) then I can move on from there.

I can open and run action querys through code just fine, but I don't know
how to grab a piece of information from a simple select query. :-(
Click to expand...


Is it a stored query? If so, you can use DLookup or DCount to find out if
it returns any records. For example:

If IsNull(DLookup("SomeRequiredField", "YourQueryName") Then
' There are no records.
Else
' There is at least one record.
End If

Or:

If DCount("*", "YourQueryName") = 0 Then
' There are no records.
Else
' There is at least one record.
End If

Using DLookup, as in the first example above, will be more efficient, but
you must specify the name of a field that will not be null in any returned
record.

If it's not a stored query, and you can't reformulate it as a DLookup, then
you can open a recordset on the SQL statement:

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT ..." ' your SQL statement here

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
If .EOF Then
' There are no records.
Else
' There is at least one record.
End If
.Close
End With
Set rs = Nothing
 
Thanks Dirk!

I didn't think about DLookup.... I works fine.

However, I wish I could get my Recordset to work. My code is almost exactly
like yours however, after I define my SQL the recordset won't set to it.
Recordsets are just not liking me at the moment!

Again thanks!
 
The problem may be with the SQL statement rather than the Recordset itself
(this was certainly common for me when I started with recordsets). If you
post the SQL you are trying someone can take a look at it.

But, working with recordsets is generally considered a last resort. Usually
there's a better way to do it, via through query or a function like DLookup
(see Allen Browne's ELookup http://allenbrowne.com/ser-42.html as a handy
replacement for DLookup, with the reasons given in his article).

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
ThriftyFinanceGirl said:
Thought I would post my code that isn't working...

Dim db As Database
Dim Rst As Recordset
Dim NumRecords As Long
Dim SQL As String

Set db = CurrentDb()
SQL = "SELECT Count(TaxRatesBases.TaxID) AS NumRecord" & _
"FROM (Location LEFT JOIN TaxRatesBases ON Location.BusUnit =
TaxRatesBases.BusUnit) LEFT JOIN LocationObjects ON TaxRatesBases.TaxID =
LocationObjects.TaxID" & _
"GROUP BY TaxRatesBases.BusUnit, LocationObjects.ObjectAcctNo"
& _
"HAVING
(((TaxRatesBases.BusUnit)=[Forms]![frmlocation]![BusUnit]) AND
((LocationObjects.ObjectAcctNo) Is Null));"
Click to expand...


The way you're building the SQL string, there are no spaces between various
words where they need to be. Also, when you use DAO, references to form
controls -- such as your [Forms]![frmlocation]![BusUnit] -- aren't resolved
automatically. You have to fill them in as you build the SQL string.
Set Rst = db.OpenRecordset("SQL")
Click to expand...

Also, you're passing the literal string "SQL" to OpenRecordset, rather than
the variable you named SQL.

Try this:

SQL = _
"SELECT Count(TaxRatesBases.TaxID) AS NumRecord " & _
"FROM (Location LEFT JOIN TaxRatesBases " & _
"ON Location.BusUnit = TaxRatesBases.BusUnit) " & _
"LEFT JOIN LocationObjects " & _
"ON TaxRatesBases.TaxID = LocationObjects.TaxID " & _
"GROUP BY TaxRatesBases.BusUnit, LocationObjects.ObjectAcctNo " & _
"HAVING (((TaxRatesBases.BusUnit)='" & _
[Forms]![frmlocation]![BusUnit] & _
"') AND ((LocationObjects.ObjectAcctNo) Is Null));"


Set Rst = db.OpenRecordset(SQL)

I'm not vouching for the validity of the SQL statement, but the above should
correct the syntax errors. It assumes that BusUnit is a text field. If
not, change the last three lines to:

"HAVING (((TaxRatesBases.BusUnit)=" & _
[Forms]![frmlocation]![BusUnit] & _
") AND ((LocationObjects.ObjectAcctNo) Is Null));"
 
Dirk Goldgar said:
Try this:

SQL = _
"SELECT Count(TaxRatesBases.TaxID) AS NumRecord " & _
"FROM (Location LEFT JOIN TaxRatesBases " & _
"ON Location.BusUnit = TaxRatesBases.BusUnit) " & _
"LEFT JOIN LocationObjects " & _
"ON TaxRatesBases.TaxID = LocationObjects.TaxID " & _
"GROUP BY TaxRatesBases.BusUnit, LocationObjects.ObjectAcctNo " & _
"HAVING (((TaxRatesBases.BusUnit)='" & _
[Forms]![frmlocation]![BusUnit] & _
"') AND ((LocationObjects.ObjectAcctNo) Is Null));"


Set Rst = db.OpenRecordset(SQL)

I'm not vouching for the validity of the SQL statement, but the above
should correct the syntax errors. It assumes that BusUnit is a text
field. If not, change the last three lines to:

"HAVING (((TaxRatesBases.BusUnit)=" & _
[Forms]![frmlocation]![BusUnit] & _
") AND ((LocationObjects.ObjectAcctNo) Is Null));"
Click to expand...


Now that I look at this a bit more, I see that you are trying to check the
RecordCount property of the resulting recordset. But if that's what you
want to do, I'm not at all sure that you should be using a totals query
(with GROUP BY and the Count() function) at all. Wouldn't this be better?

SQL = _
"SELECT TaxRatesBases.TaxID " & _
"FROM (Location LEFT JOIN TaxRatesBases " & _
"ON Location.BusUnit = TaxRatesBases.BusUnit) " & _
"LEFT JOIN LocationObjects " & _
"ON TaxRatesBases.TaxID = LocationObjects.TaxID " & _
"WHERE (((TaxRatesBases.BusUnit)='" & _
[Forms]![frmlocation]![BusUnit] & _
"') AND ((LocationObjects.ObjectAcctNo) Is Null));"
 
Back
Top