Still in the mode of thinking it's a timing issue, so let's add a Stop
action to your code right after you create the QueryDef. When the code
stops at that point, type the DCount expression in the Immediate window:
?DCount("*", "qryTempRptQry")
Do you get an error? or do you get a correct record count? If you don't
get an error, then it likely is a timing issue and the query def likely
hasn't been created yet so that the DCount function can use it.
What if you were to always keep the qryTempRptQry query in the database,
and have your code change its SQL property instead of creating it:
Private Function CreateQry()
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Dim qryName As String
If Me!optDevsel = 1 Then
'All devices
strSQL = "SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.Address, Customers.City,Customers.State, Customers.[Zip Code],
tblFireAlarmDevices.Zone, tblFireAlarmDevices.[Model No],
tblFireAlarmDevices.Descrip, tblFireAlarmDevices.Quantity,
tblFireAlarmDevices.Location, [Test Date] AS Expr1, [Comments] AS Expr2
FROM
tblFireAlarmDevices LEFT JOIN Customers ON tblFireAlarmDevices.[Customer
ID]
= Customers.[Customer ID] ORDER BY tblFireAlarmDevices.Zone DESC ,
tblFireAlarmDevices.Location DESC;"
Else
'If Me!optDevsel = 2 Then
'Smoke Detectors only
strSQL = "SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.Address, Customers.City, Customers.State, Customers.[Zip Code],
tblFireAlarmDevices.Zone, tblFireAlarmDevices.[Model No],
tblFireAlarmDevices.Descrip, tblFireAlarmDevices.Quantity,
tblFireAlarmDevices.Location, [Test Date] AS Expr1, [Comments] AS Expr2
FROM
tblFireAlarmDevices LEFT JOIN Customers ON tblFireAlarmDevices.[Customer
ID]
= Customers.[Customer ID] WHERE (((tblFireAlarmDevices.Description) =
'Smoke
Detector')) ORDER BY tblFireAlarmDevices.Zone DESC ,
tblFireAlarmDevices.Location DESC;"
End If
Set dbs = CurrentDb()
Set qdf = dbs.QueryDef("qryTempRptQry")
qdf.SQL = strSQL
RC = DCount("*", "qryTempRptQry")
By the way, do you Dim RC anywhere in your code?
--
Ken Snell
<MS ACCESS MVP>
Aaron said:
The result SQL runs perfect in a query, As I tested way back.
I replaced "Description" with "Descrip" days back when I was told
Description was a reserved word.
Ken Snell (MVP) said:
I see "tblFireAlarmDevices.Descrip" in the string. That appears to be
incomplete?
--
Ken Snell
<MS ACCESS MVP>
Ken
The result of using Debug,Print strSQL in the immediate window was:
SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.Address, Customers.City,Customers.State, Customers.[Zip
Code],
tblFireAlarmDevices.Zone, tblFireAlarmDevices.[Model No],
tblFireAlarmDevices.Descrip, tblFireAlarmDevices.Quantity,
tblFireAlarmDevices.Location, [Test Date] AS Expr1, [Comments] AS Expr2
FROM tblFireAlarmDevices LEFT JOIN Customers ON
tblFireAlarmDevices.[Customer ID] = Customers.[Customer ID] ORDER BY
tblFireAlarmDevices.Zone DESC , tblFireAlarmDevices.Location DESC;
Didn't try the second one, the errors are the same regardless of which
is
used.
You can use * as the wildcard character. The intent is to not name a
field with * in the name.
I didn't see anything obvious in the code, so let's add this code line
after you set the strSQL in each branch of the If...Then block:
Debug.Print strSQL
Let's see what string the code is actually building.
--
Ken Snell
<MS ACCESS MVP>
Ok...
I eliminated the reserved word Description. Added DeEvents. Still
getting error.
So I'm not supposed to use the (special) character "*" ? What other
wild-card symbol should be used?
You're using a reserved word (Description) as a field name in your
tblFireAlarmDevices table. I'm guessing that, because you haven't
delimited it with [ ] characters, Jet may be confused about whether
you want a property or the field name.
See these Knowledge Base articles for more information about
reserved
words and characters that should not be used:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
See this site for code that allows you to validate your names as not
being
VBA keywords:
basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
Additionally, you may need to give ACCESS a bit of time to save the
query before you use it in the DCount function. You might try
inserting a DoEvents code line right after you create the querydef
(before you run the DCount function).
--
Ken Snell
<MS ACCESS MVP>
< snipped >
