Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 952
  • Last Modified:

Compile Error in query expression in Access 2010

I put an internal messaging system in my access database so the users can send me (Admin) a message if need something. When I click on my frm_ReceiveMail, it gives me the Compile Error in query expression '(((TBL_Message.To)=CurrentUser()) AND ((TBL_Message.DateReceived) Is Null)'

I have a query that has To and DateReceived. The critera for the To field is CurrentUser() and DateReceived criteria is Is Null.

Does anyone know what this error is with a solution?
0
lauriecking0623
Asked:
lauriecking0623
  • 2
  • 2
1 Solution
 
Kelvin SparksCommented:
I suspect this is the CurrentUser() bit. This always used to related to the logged in user when Access had security. The concept of users has now gone - unless you still use mdb's and mdw's.

You would need code to pickup the windows username and put that in its place.


Kelvin
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
CurrentUser() is still a valid function call.  Are you building this SQL statement in code?  If not, then you most likely have broken references.  Open a module, bring up the debug window and type:

? CurrentUser()

and hit return.  You should get admin back.  If you get an error, then it's references for sure.

 If you are building this select in code, then your syntax needs to be different.

Jim.
0
 
lauriecking0623Author Commented:
@kelvinsparks,

How would I do this code to pick up the windows username? I am still trying to learn how to call these references in VBA?

Laurie
0
 
lauriecking0623Author Commented:
@JDettman, this worked. I do not get this error. I get a new error; however, I am going post this as a new question. Thank you for your assistance.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Here's the code for getting the user name (or station name).

You'll need to add the PtrSafe for the 64 bit.

Jim.

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now