VBA Code and Queries

[aseeri]

Hello everyone,

Here's my problem: I have a table called ContactInformation with a field called CreatedBy. I then used the wizard to create a form called ContactInformation. Now, using the VB Editor, the value of CreatedBy gets automatically filled by the CurrentUser, like this:

CreatedBy.Value = CurrentUser

This causes the value of CreatedBy to become 'Admin' or the username of the user logged into the database.

Then, I have a Query called ContactInformation Query. I wanted this Query to only return records created by the CurrentUser. In other words, only display records in which the username matches the value in the CreatedBy field.

As this is dynamic and the username changes depending on who logs into the database, I'm not sure how this could be done. I also can't seem to find a way to enter VB code into a query.

Anyone??

[Rabbit]

Create a public function in a non-form module whose sole purpose is to return the CurrentUser. You can then call that function from SQL.

[aseeri]

Create a public function in a non-form module whose sole purpose is to return the CurrentUser. You can then call that function from SQL.

Thanks for the reply...

OK, so now I have a function that returns the CurrentUser.. But how do I call it in SQL from the Query?

I am using Access 2003 and I created the Query using the wizard. But I can't seem to find where the SQL query actually goes so I can call that function...

[Rabbit]

I'm not sure what you mean.. you call the function like you call any other function.

Module1

Option Compare Database
Option Explicit
 
Public Function ReturnUser() As String
   ReturnUser = CurrentUser
End Function
 

Query1

SELECT *
FROM Table1
WHERE UserName = ReturnUser();