Using a variable in a query or filter?

Access 2003, all coding in VB.

I'm sure this is very easy, but...

I have a form that uses some simple SQL in the Record Source line to
populate a few fields. Nothing very interesting there.

I have a DLL that returns the login name of the user running my Access
application. This seems to work fine, and I place it into a variable called
"loggedInUser"

What I would like to do is filter the recordset on the form to only those
records that match the value in the variable. For instance...

SELECT * FROM tblOrders WHERE userId = loggedInUser

I assume there is some simple syntax for this, like @loggedInUser@ or
whatever, but I don't have any examples of it.

What's the key here? And should I put the query into the Filter field
instead? If so, what is the syntax there?

JET cannot access the VBA Variable directly since JET doesn't know about
VBA. However, you can use a UDF "wrapper" function and JET will recognise
it as a function and pass it back to VBA for processing / retrieving.

The wrapper function can be as simple as:

(assuming that LogInUser

Public fnGetLogInUser() As {Data type you use}
fnGetLogInUser = LoggedInUser
End Function

and in your SQL, you use:

SELECT * FROM tblOrders WHERE userId = fnGetLogInUser()

--
HTH
Van T. Dinh
MVP (Access)




"Maury Markowitz" Maury wrote in
message ...
Access 2003, all coding in VB.

I'm sure this is very easy, but...

I have a form that uses some simple SQL in the Record Source line to
populate a few fields. Nothing very interesting there.

I have a DLL that returns the login name of the user running my Access
application. This seems to work fine, and I place it into a variable
called
"loggedInUser"

What I would like to do is filter the recordset on the form to only those
records that match the value in the variable. For instance...

SELECT * FROM tblOrders WHERE userId = loggedInUser

I assume there is some simple syntax for this, like @loggedInUser@ or
whatever, but I don't have any examples of it.

What's the key here? And should I put the query into the Filter field
instead? If so, what is the syntax there?

"Van T. Dinh" wrote:

Public fnGetLogInUser() As {Data type you use}
fnGetLogInUser = LoggedInUser
End Function
SELECT * FROM tblOrders WHERE userId = fnGetLogInUser()

I tried this, but I get the error "fnGetLogInUser is not a recognized
function name". Does this ring any bells?

Are you running code in Access / VBA or in standa-alone Visual Basic?

If in Access / VBA, it definitely works.

If you are running in stand-alone Visual Basic, it won't work. You will
have to resolve the Parameter / modify the SQL String to have the explicit
value of the LoggedInUser in the SQL String.

--
HTH
Van T. Dinh
MVP (Access)




"Maury Markowitz" wrote in
message ...
"Van T. Dinh" wrote:

Public fnGetLogInUser() As {Data type you use}
fnGetLogInUser = LoggedInUser
End Function
SELECT * FROM tblOrders WHERE userId = fnGetLogInUser()

I tried this, but I get the error "fnGetLogInUser is not a recognized
function name". Does this ring any bells?

Hi
Comment inline
"Van T. Dinh" wrote in message
...
JET cannot access the VBA Variable directly since JET doesn't know about
VBA. However, you can use a UDF "wrapper" function and JET will recognise
it as a function and pass it back to VBA for processing / retrieving.

The wrapper function can be as simple as:

(assuming that LogInUser

Public fnGetLogInUser() As {Data type you use}
fnGetLogInUser = LoggedInUser
End Function

and in your SQL, you use:

SELECT * FROM tblOrders WHERE userId = fnGetLogInUser()

depending where you are - in the Open / Load event
Me.ControlSource = "SELECT * FROM tblOrders WHERE userId = """ & _
fnGetLogInUser() & """"
whether double or single apostrophes also depends where you are.

HTH
Marc


--
HTH
Van T. Dinh
MVP (Access)




"Maury Markowitz" Maury wrote in
message ...
Access 2003, all coding in VB.

I'm sure this is very easy, but...

I have a form that uses some simple SQL in the Record Source line to
populate a few fields. Nothing very interesting there.

I have a DLL that returns the login name of the user running my Access
application. This seems to work fine, and I place it into a variable
called
"loggedInUser"

What I would like to do is filter the recordset on the form to only
those
records that match the value in the variable. For instance...

SELECT * FROM tblOrders WHERE userId = loggedInUser

I assume there is some simple syntax for this, like @loggedInUser@ or
whatever, but I don't have any examples of it.

What's the key here? And should I put the query into the Filter field
instead? If so, what is the syntax there?

"Van T. Dinh" wrote:
Are you running code in Access / VBA or in standa-alone Visual Basic?

Access / VBA.

If in Access / VBA, it definitely works.

Here's what I did. I added this code to the form's VB...

Public Function fnGetLogInUser() As String
fnGetLogInUser = loggedInUser
End Function

Then I added this code to the form's Record Source binding...

SELECT * FROM tblOrders WHERE (lastModifiedTimestamp = convert(varchar(10),
getdate(), 101)) AND (enteredById = fnGetLogInUser())

I seem to get the error in this binding, it says "function not found".
Perhaps I need to place it somewhere else?

Maury

Ok, I managed to get this to work. It appears the variable will work fine in
the Filter field, but not in the Record Source. I don't know why this is, but
the system is working now it seems. Another one of those VB mysteries I
suppose.

"Maury Markowitz" wrote in
message ...
"Van T. Dinh" wrote:
Are you running code in Access / VBA or in standa-alone Visual Basic?

Access / VBA.

If in Access / VBA, it definitely works.

Here's what I did. I added this code to the form's VB...

Public Function fnGetLogInUser() As String
fnGetLogInUser = loggedInUser
End Function

Then I added this code to the form's Record Source binding...

SELECT * FROM tblOrders WHERE (lastModifiedTimestamp =
convert(varchar(10),
getdate(), 101)) AND (enteredById = fnGetLogInUser())

I seem to get the error in this binding, it says "function not found".
Perhaps I need to place it somewhere else?

Try putting the function in a module. not as part of the code associated
with a form.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

It looks to me that your SQL String follows the T-SQL syntax (Convert() /
GetDate()) so it is likely you are using MS-SQL Server (or MSDE) Back-End
rather than JET Back-End (which is generally assumed in these newsgroups).

In this case the Access VBA UDF won't work because the SQL Server doesn't
know about VBA or the UDF.

In you later post, you mentioned that the Filter works. This is entirely
reasonable since the Filter is handled locally, i.e. Access and the UDF is
recognised and resolved correctly.

If you want to experiment, try the following MSKB article:

http://support.microsoft.com/?id=278400

--
HTH
Van T. Dinh
MVP (Access)




"Maury Markowitz" wrote in
message ...
"Van T. Dinh" wrote:
Are you running code in Access / VBA or in standa-alone Visual Basic?

Access / VBA.

If in Access / VBA, it definitely works.

Here's what I did. I added this code to the form's VB...

Public Function fnGetLogInUser() As String
fnGetLogInUser = loggedInUser
End Function

Then I added this code to the form's Record Source binding...

SELECT * FROM tblOrders WHERE (lastModifiedTimestamp =
convert(varchar(10),
getdate(), 101)) AND (enteredById = fnGetLogInUser())

I seem to get the error in this binding, it says "function not found".
Perhaps I need to place it somewhere else?

Maury