CODEWITHSUNDEEP
ms-accessif-statementvba
Jcmoney1010
Jcmoney1010

Reputation: 922

Issue with an if-statement

I'm trying to use a macro to check and see if there are records left within a table, and if there are, then display a message, letting me know there is still data in the table. My set up looks like this
enter image description here

Currently, I'm getting this error message:
enter image description here

I get this error message regardless of whether I use Is Not Null or Not IsNull. Is there any suggestions as to what I'm doing wrong?

Upvotes: 0

Views: 73

Answers (1)

James
James

Reputation: 26

Macros is one of the, if not the, most annoying topics in Access. It illustrates beautifully one of the most galactically stupid aspects of Access: it cheerfully allows you to point to and select a table by name and a field name to boot giving you the false hope that it actually knows what you are talking about then turns around and tells you it has no idea what you are asking for. Brilliant. Not only does the "red headed step child" that is Access get mismatched shoes, neither of them fit!

I honestly thought this would be easy to answer. But to add insult to injury, Microsoft has documented this poorly conceived "feature" about as well as they have implemented it.

I'm not sure I understand exactly what you are trying to do but I think you simply want to know if a table is empty. If so, my suggestion: use a function. For example:

Function IsTableEmpty(aTableName As String) As Boolean
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("select count(*) from [" & aTableName & "];", dbOpenForwardOnly, dbReadOnly)
    IsTableEmpty = rst.Fields(0) = 0
    rst.Close
    Set rst = Nothing
End Function

If you insist on using a macro you will be astonished to find that Access can find your function and use it with no difficulty. You can use this with any table (query for that matter).

Sadly, if you are doing web applications with Access you are going to have to come to grips with whatever (lame) functionality macros provide because VBA will not be available to you.

HTH. Good luck.

PS - I would happily eat my words if someone would point me to the documentation that explains how this works and why such a situation exists!

Upvotes: 1

Related Questions