Query parameters defined by variable instead of form (1 Viewer)

[accessaspire219]

Hi,
I want to design a database that will pickup a windows username and store it to a variable. Later on I want to reference to this variable as the parameter in a parameter query. In other words, in the parameter queries I have built so far I have always had the user enter the parameter in a form and then referenced the form in the parameter query to get the parameter. What I am looking to do is to reference a variable instead of a form in the parameter query.
Any idea on how this could be done?
Thanks!

 

[pbaldy]

I typically store it in a hidden textbox on a form that remains open, and get it from there. If you want to store it in a VBA variable, you'd create a public function that returned the value of that variable, and call that from the criteria.

 

[boblarson]

to use variables in a query you must wrap them in a function. So, for example, if you have a global variable for the user name:

mstrUser

you need to use a function

Public Function RetUser() As String
   RetUser = mstrUser
End Function

to get it in the query - or criteria:

RetUser()

 

[boblarson]

I typically store it in a hidden textbox on a form that remains open, and get it from there. If you want to store it in a VBA variable, you'd create a public function that returned the value of that variable, and call that from the criteria.

I typed too slow on that one

 

[accessaspire219]

Thanks Pbaldy and boblarson. where should I define the function? I defined it in the form and put criteria as RetUser() in the query, this came up with a undefined function error when I tried to run the query. What am I doing wrong here?

 

[pbaldy]

To be run from a query, it should be a public function in a standard module (not behind a form).

 

[accessaspire219]

What is a standard module? Can I just add a new module and put the function there?

 

[pbaldy]

Yes, that would be a standard module.

 

[accessaspire219]

Ok, I made a new module and put the function in there. The function I have is

 Option Compare Database
Public Function GetUser()
GetUser = varName
End Function

Now do I just write GetUser() in the query criteria?

 

[pbaldy]

Yes; have you tried it?

 

[accessaspire219]

yeah, it did not work.....the query result was blank.

 

[pbaldy]

Have you set a breakpoint or message box to ensure the function is returning what you expect? You obviously need to have populated the variable at some point prior to this.

 

[accessaspire219]

any idea how I could do that? VBA is not my forte!

 

[accessaspire219]

Also, any idea on I can write a "Sub" so that it runs when the database is opened? For example, in the following code,

Private Sub Form_Load()
Dim strUser As String
Set WshNetwork = VBA.CreateObject("WScript.Network")
strUser = WshNetwork.UserName
[loginid] = strUser
Dim varName As String
varName = DLookup("SNAME", "WINDOWS_USERNAME_NAME", "[WINDOWSUSER]='" & Me.loginid.Value & "'")
[loginname] = varName
End Sub

the sub runs when the form loads. How can I write this sub so that it runs everytime the database is opened?

 

[pbaldy]

Either load that form on startup (Tools/Startup) or use an autoexec macro to open it.