Need help with the missing piece of this!

[Chris C]

I have the code below to run a query that matches two text boxes based on
input in one of the two. I'm not throwing any errors here, but I'm not
getting the results I'm expecting. To me, it looks like the query itself
isn't actually executing, but you can't use the QueryDef.Execute command here
[or so the application told me].

In a nutshell, I'm shooting a message box with a success message to the user
if the query returns anything other than 0 records, and if 0, a fail.
Everytime I click my button, it returns nothing, but I know the record match
exists already.

Any ideas?

Private Sub Command19_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb

sqlStr = "SELECT count(*) As Total from tblError where "
sqlStr = sqlStr & "tblError.err_taskID = '" &
Forms!frmMatch.Controls!err_taskID & "' AND "
sqlStr = sqlStr & "tblError.error_details LIKE '%" &
Forms!frmMatch.Controls!mchText & "%'"

Set qdf = dbs.CreateQueryDef("", sqlStr)

If (qdf.RecordsAffected <> 0) Then
MsgBox ("Your text matches this error! Returned: " &
qdf.RecordsAffected & " rows")
Else
MsgBox ("Your text does not match this error. Returned: " &
qdf.RecordsAffected & " rows")
End If

qdf.Close


End Sub

 

[Rick Brandt]

I have the code below to run a query that matches two text boxes
based on input in one of the two. I'm not throwing any errors here,
but I'm not getting the results I'm expecting. To me, it looks like
the query itself isn't actually executing,

That is correct. You are never running it with your current code. You just
define it.

but you can't use the
QueryDef.Execute command here [or so the application told me].

You don't need a queryDef at all. Just build a SQL string and use that to
open a Recordset. You could even just use DCount() here if you want to.

 

[Chris C]

I looked through the DCount function, but I'm not sure how to work it in
here. Alternatively, could you provide an example of running the SQL string
and retrieving the number of rows?

Everything I'm looking at is using the QueryDef object.

I have the code below to run a query that matches two text boxes
based on input in one of the two. I'm not throwing any errors here,
but I'm not getting the results I'm expecting. To me, it looks like
the query itself isn't actually executing,

That is correct. You are never running it with your current code. You just
define it.

but you can't use the
QueryDef.Execute command here [or so the application told me].

You don't need a queryDef at all. Just build a SQL string and use that to
open a Recordset. You could even just use DCount() here if you want to.

 

[Rick Brandt]

I looked through the DCount function, but I'm not sure how to work it
in here. Alternatively, could you provide an example of running the
SQL string and retrieving the number of rows?

Everything I'm looking at is using the QueryDef object.

Example using Recordset...

Private Sub Command19_Click()

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
sqlStr = "SELECT count(*) As Total " & _
"FROM tblError " & _
"WHERE tblError.err_taskID = '" & _
Forms!frmMatch.Controls!err_taskID & "' " & _
"AND tblError.error_details LIKE '%" & _
Forms!frmMatch.Controls!mchText & "%'"
Set rs = dbs.OpenRecordSet(sqlStr, dbOpenSnapshot)

If rs!Total <> 0 Then
rs.MoveLast
MsgBox ("Your text matches this error! Returned: " & rs!Total & " rows")
Else
MsgBox ("Your text does not match this error. Returned: 0 rows")
End If

rs.Close

End Sub

Example using DCount...

Dim cnt as Long

cnt = DCount("*", "tblError", tblError.err_taskID = '" & _
Forms!frmMatch.Controls!err_taskID & "' " & _
"AND tblError.error_details LIKE '%" & _
Forms!frmMatch.Controls!mchText & "%'")
If cnt <> 0 Then
rs.MoveLast
MsgBox ("Your text matches this error! Returned: " & cnt & " rows")
Else
MsgBox ("Your text does not match this error. Returned: 0 rows")
End If

Neither of the above was tested, but you shoudl get the idea.

 

[Rick Brandt]

There is actually no need for the MoveLast below. I meant to delete that.

 

[Chris C]

This is what finally got it going:

' Dim dbs As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Set dbs = CurrentDb 'set the db


sqlStr = "SELECT tblError.err_taskID " & _
"FROM tblError " & _
"WHERE (tblError.err_taskID = '" & _
Forms!frmMatch.Controls!err_taskID & "' " & _
"AND tblError.error_details LIKE '%" & _
Forms!frmMatch.Controls!mchText & "%')"
rs.Open sqlStr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.RecordCount <> 0 Then
MsgBox ("Your text matches this error! Returned: " & rs.RecordCount
& " rows")
Else
MsgBox ("Your text does not match this error. Returned: 0 rows")
End If

rs.Close

Thanks for the help!