SQL statement execution error

[George Papadopoulos]

Hello, Access community

I have written the code below :

Dim strSelect As String
Dim intRecordCount As Integer

If Not IsNull(Me.Kwdikos_episkeyhs) Then
strSelect = "Select Count(*) From ANTALLAKTIKA WHERE Kwdikos_episkeyhs
=" & Me.Kwdikos_episkeyhs
intRecordCount = DoCmd.RunSQL(strSelect)
Else
MsgBox ("Error message!")
End If

The code fails with a compile error message. Is this the recommended
approach to get the number of records in table ANTALLAKTIKA under the
condition
above?

thx, in advance

George Papadopoulos
Electronic Engineer

 

[Gerald Stanley]

DoCmd.RunSQL should not be used with SELECT queries as it
does not return any values. To achieve your goal you
should use either a DAO or ADO recordset. This is an
example of how it could be done in ADO

Dim comm as ADODB.Command
Dim rs As ADODB.Recordset
Dim strSelect As String
Dim intRecordCount As Integer

Set comm = New ADODB.Command
Set comm.ActiveConnection = CurrentProject.Connection

If Not IsNull(Me.Kwdikos_episkeyhs) Then
strSelect = "Select Count(*) AS RecCount From
ANTALLAKTIKA WHERE Kwdikos_episkeyhs
=" & Me.Kwdikos_episkeyhs
comm.CommandText = strSelect
Set rs = comm.Execute
intRecordCount = rs!RecCount
Else
MsgBox ("Error message!")
End If

The above is untested air-code. DAO code will be similar
but refer to the Help files for more information.

Hope This Helps
Gerald Stanley MCSD

 

[Nikos Yannacopoulos]

George,

The DAO Alternative would be something like:
Dim rst as DAO.Recordset
Dim strSelect As String
Dim intRecordCount As Integer

If Not IsNull(Me.Kwdikos_episkeyhs) Then
strSelect = "Select * From ANTALLAKTIKA WHERE Kwdikos_episkeyhs =" &
Me.Kwdikos_episkeyhs
set rst = CurrentDB.OpenRecordset(strSelect)
On Error GoTo No_Rec
rst.MoveLast
On Error GoTo 0
intRecordCount = rst.Recordcount
GoTo Continue_Sub
Else
MsgBox ("No Kwdikos_episkeyhs Selected")
GoTo Continue_Sub
End If

No_Rec:
On Error GoTo 0
intRecordCount = 0

Continue_Sub:
....

HTH,
Nikos

 

[George Papadopoulos]

thx, to everyone for your help! One thing though, I noticed somewhere in the
code, the statement On Error GoTo 0. I could find no 0 label in the code.
Where does the execution flow go after that statement?

 

[Nikos Yannacopoulos]

George,

0 is not a label, it's just resetting error handling to default.

Nikos

 

[Treebeard]

George,

I think you can use this also,

=DCount("*", "[ANTALLAKTIKA]", "[Kwdikos_episkeyhs] = " &
Me.Kwdikos_episkeyhs )

 

[George Papadopoulos]

nice. thx, Nikos!

George,

0 is not a label, it's just resetting error handling to default.

Nikos