IF null THEN

[kidrobot]

'I'm not too good with the VB language but I know IF then statements. I need an IF then statement for my SQL code. I want to give a popup box that says "There is no record"

So I'd imagine it would look something like this...

docmd.runsql

IF NULL
then
(VB OK?)there is no record
some type of exit?
if not
continue code
end if

 

[chergh]

You want something like this:

dim rs as recordset

set rs = currentdb.openrecordset(strSQL)

if rs.count = 0 then
msgbox("There are no records")
end if

 

[geekay]

Dim strSQL As String, MyCount As Long
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset: Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tablename.* FROM tablename"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyCount = rs.RecordCount
rs.Close
If MyCount <> 0 Then

'Write codes you need

End If

 

[kidrobot]

Can I do this if my I use Docmd.Runsql "DELETE ...." ? I am not using Recordset.

 

[KeithG]

I would save your SQL as a query then use vba and open up the QueryDef object and call the execute method. Now you can use the recordseffected property to determine the number of rows effected by the query.

 

[boblarson]

And depending on what your SQL statement pulls you could even use DCount to check to see if there's anything to pull out to start with, before even running a query.

 

[kidrobot]

Sorry, but I also need it for this " Me.lstReports.Requery " ... sometimes there are NO records when you click the submit button so it'll do nothing. I need a warning for this too!

 

[Moniker]

If Me.lstReports.ListCount < 1 Then
'No records code
Else
'Perform the submit code
End If

 

[kidrobot]

If Me.lstReports.ListCount < 1 Then
'No records code
Else
'Perform the submit code
End If

sweet worked like a charm