Networked Database - Detecting User ID 1

[rod1968]

Hi,

I have a networked database used by 30-40 people and I want to make it detect who is using the database when they log-in. I have the following code that returns the userID in a message box:-

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function UserID()
Dim Fred As String * 255
Dim UserID
GetUserName Fred, 255
UserID = Fred
MsgBox UserID
End Function

I want to use the value "UserID" to automatically select a user name from a droplist. I have tried calling the function from the "Start" form, that contains the droplist of users and it highlights the correct user in the list but unless I then select from the list manually, it doesn't seem to recognise the UserID.

I hope this is clear, I fear it's not but any help on this would be very much appreciated. Thanks

 

[kathryn]

When you say that "it doesn't seem to recognise the UserID" do you mean that in other places in the application?

If I understand you, you are using the GetUserName API to get the user's login ID from Windows. Do you then get the users' full names from a table that contains the login IDs? Or are you just using the login IDs?

Kathryn

 

[rod1968]

Hi Kathryn,

Thank you for responding.

I mean that function works, telling me the "UserID" from Windows (it does not get the full name) but the "Fullname" and "UserID" are related in "User" table. I use this table to provide me with the droplist, which displays the "Fullname" and "UserID" (the UserID is the bound column).

If I run the function on opening the "Start" form it does detect that I am the user and puts my "hightlighted" name in the Combo Box and if I drop the Combo Box list, my name and userID is also highlighted. This is fine.

I have an "Enter" button, on the "Start" form, which opens a "Summary" form, based on a query that compares the value in the "Start" form Combo Box with the a "UserID" field of a table called "Items". If I click on "Enter", the query does not find any matching entries. If I select my name from the droplist the query does find matching entries.

It's as if the value in the Combo Box is only valid if I select from my droplist. I want it to automatically pick up the UserID, from the API call, and simply allow me to click "Enter" to proceed.

I'm sure there is a simple answer but I guess I'm too simple to see it I hope I have explained this reasonably clearly.

Rod

 

[kathryn]

Have you looked at assigning the UserID to a global variable? That would give you access to the value everywhere.

I can't figure out what would be causing the behavior you describe. To make sure I understand, if open the Start form, your user name is highlighted correctly. But if you press the Enter button on the form WITHOUT manually choosing your user name from the combo box, your name is not "available" to the next form. You have to manually select your name and then click Enter.

Can you post the code behind the Enter button? Kathryn

 

[TrevJones]

I am not sure exactly what you need to know but is the currentuser() function of any use to you?

 

[rod1968]

Hi Kathryn,

I am something of a novice with VBA .. a complete novice actually

I'm not sure how to declare the UserID as a global variable? I assume it is done in the the module?

I can see how this may well be the answer to my problem.

Rod

 

[kathryn]

OK, you need to use a module is not behind a form or report. At the top of all the code, if any, of the module, type

Public strUserID as String

Then modify your code:

Function UserID()
Dim Fred As String * 255
Dim UserID
GetUserName Fred, 255
UserID = Fred
strUserID = UserID
End Function


Now go to the second form and see if you can access the strUserID from there. Maybe add

MsgBox strUserID

to the form's OnOpen event.
Kathryn

 

[rod1968]

Hi Kathyrn,
I'm still struggling with this. I am also very aware that I'm taking up your time and that my main problem is lack of VBA knowledge.

I now think I am not understanding how to "call" the function properly. I will reproduce here exactly what my UserID module says:-

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'
Public strUserID As String

Function UserID()
'Create a new variable callled Fred as a String
Dim Fred As String * 255
'Create a return variable called temp
Dim temp
'Make the API call to get the UserName
GetUserName Fred, 255
'Assign GuardianID to the value of Fred
GuardianID = Fred
'Display a messagebox with the value
strUserID = GuardianID
End Function

How do I call this function so that the value of GuardianID becomes the contents of my Combobox [GuardianID] ?

I have been using this code in a Private Sub as an event procedure behind the On Current of the Start form and this has been hightlighting the correct name in my droplist. But it doesn't become the value of ComboBox [GuardianID]

Maybe I need to call the function from the control source of the ComboBox [GuardianID], I'm not sure?

 

[kathryn]

OK try this. I am not an expert in scoping variables, so I'm not positive this will work.

Move all your code:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'
Public strUserID As String

Function UserID()
'Create a new variable callled Fred as a String
Dim Fred As String * 255
'Create a return variable called temp
Dim temp
'Make the API call to get the UserName
GetUserName Fred, 255
'Assign GuardianID to the value of Fred
GuardianID = Fred
'Display a messagebox with the value
strUserID = GuardianID
End Function

to a new module under the modules tab. Don't use a module behind a form.

Your code that refers to UserID() will still work, and I THINK that moving the Public strUserID to a public module will make it truly global.

Give that a try and then try what I suggested earlier:

Now go to the second form and see if you can access the strUserID from there. Add the following line:

MsgBox strUserID

to the second form's OnOpen event.


Kathryn

 

[rod1968]

Hi Kathryn,

That works and I can now see my userID in the MsgBox on the second form.

However, what I really want is to have the userID, obtained from the API call, as the default value for my Combobox on the Start form but also allowing me to select any other userID from the droplist if I choose it.

This is where I am having a problem now.

Thank you for helping me this far.

 

[kathryn]

OK, does the combobox not allow you to select? If it does, then you need to add code to the combobox, maybe the afterUpdate event to assign the new value to your global strUserID.

Does that sound like what you want to do? Kathryn

 

[rod1968]

Yes it does allow me to select from a list of 30 possible users, but I want it to detect the "logged on" user and for the value of Forms!StartForm!GuardianID combobox to be strUserID.

In most cases this is how the users will be using the software, to look up items belonging to themselves but I need the combobox to allow them the option to look at other users items. I hope this is perhaps a bit clearer.

What code do I need to put in the AfterUpdate event?

Thank you for your help, I'm most grateful to you

 

[kathryn]

the code would be something like:

strUserID = me!YourComboBoxName.Value

Give that a try and see if it does what you want.
Kathryn