Solved VBA Function (1 Viewer)

[Teri Bridges]

Please can you add a Debug.Print SQL line after setting the SQL, and post the output from the Immediate Window (Ctrl+G) here after you try and run the function.

Also, you can just try changing:

' ...
    CountEvents = rst.Fields(0)
' ...

to:

' ...
    CountEvents = rst.Fields("Event Count")
' ...

I am going to leave the code as is. It is working. I thank you for all your advice but I don't want to go and bother you by creating errors. I value your time more than wasting it.

 

[MajP]

Not sure it's still relevant but looking at your screen shot I noted ListModule_tbl.Module. Module is a reserved word and should not be used as a field name.

To add to that.
Here is the list

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

"Reserved words" are words and symbols that have a specific meaning to Microsoft Access. If you use a reserved word or symbol to name a field in a desktop database or web app table, Access warns you that the word is reserved and that you might encounter errors when referring to the field.

You might also encounter errors if you use a reserved word to name a control, an object, or a variable. The error messages you receive don't necessarily tell you that a reserved word is the cause of the problem. As a result, it can be difficult to identify what needs to be changed. For example, Access might display a message similar to the following:

The wizard was unable to preview your report, possibly because a table needed by your report is exclusively locked.

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

Note: It is not practical to provide a list of all reserved words, such as built-in function names or user-defined names. If you set a reference to a type library, an object library, or an ActiveX control, that library's reserved words are also reserved words in your database.

Bottom line if I am using a name that could likely be used by Access for VBA I either try to be more specific
DueDate not Date()
FieldName not Field
Double_Sided not Double
or I just put a The before it
TheColumn not Column

 

[The_Doc_Man]

Looking at the SQL in #15 for "CntAllEvents" ... you have no dynamic variables, no concatenation, and no form-based or other obvious external references. If you made that particular SQL statement into a named query, you could do away ENTIRELY with your function and instead just run a DCOUNT of the named query. Can't do that for the cases that involve some type of input parameter, but the case I named doesn't have any.

 

[ComputerVersteher]

I would like advice on whether I have written the code to best practice.

Try rubberduckvba, its Code Inspections will give you advice.

Also read their blog (you may start with What’s Wrong With VBA? or OOP Battleship, but read them all)!