SQL string error message

[RipperT]

Why does the following code generate this error:



"runtime error 3078 the microsoft Jet database engine cannot find the input
table or query 'SELECT tblLockAllocations..."



If the sql string is set in the code such that Access can use it to generate
the error message, why can't Jet 'find' it? Am I using CreateQueryDef
correctly?

Many thanks, Rip



Private Sub Form_Open(Cancel As Integer)



Dim db As DAO.Database

Dim qry As QueryDef

Dim strSQL As String

Set db = CurrentDb

Set qry = db.CreateQueryDef



strSQL = "SELECT tblLockAllocations.Out FROM tblLockAllocations WHERE
(((tblLockAllocations.Wing) = 'A') "

strSQL = strSQL & "And ((tblLockAllocations.InmateId) <> '') And
((tblLockAllocations.HousingUnit) = 'E'))"



qry.SQL = strSQL



With Me

If Not IsNull(.OpenArgs) Or .OpenArgs <> "" Then

Select Case .OpenArgs

Case "HUEAWing"

..lblIn.Caption = DCount("Lock", qry.SQL, "Out=False")

..lblOut.Caption = DCount("Lock", qry.SQL, "Out=True")

End Select

End If

End With



End Sub

 

[Douglas J. Steele]

You cannot use DCount on a SQL statement. You either have to store the SQL
as a query, and use DCount on the saved query, or else write your SQL to do
the counts for you, and use it to open a recordset.