Unbound, form textbox populated

[WiReLaD]

Hiya,

I know I'm going to kick myself after I find out how to do this!!

Need a little help - I'm trying to populate a form textbox which is unbound.

I have table with a list of peoples login names and a query filtering these names once a user logs in. If the GetUserName() matches one of the names in the table then user is authorised. this part seems to be working fine. User not on the GetUserName() list the filter says blank ie Not Authorized.

Where I need help - how to take this confirmed user name and place it into a textbox on a form?

Things you might need to know.

Table name: [tblUserNames] with [IngEmpID] & [UserName]

Query Name: [qryUserNames] with a Criteria GetUserName()

Hope this help, hope you can help!
Thanks


Mike

[June7]

What do you mean by 'query filtering these names once a user logs in'? You have a login form bound to this query? If login form is bound to the query then the user info is available to the form.

Show query statement, post code, provide database for analysis. Follow instructions at bottom of my post.

[WiReLaD]

Thanks for the reply.

I don't have a login form, user doesn't need to login - I'm running the GetUserName in a module called userNames
see below...


Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long

Const NoError = 0 'The Function call was successful

Function GetUserName() As String

Dim lUserName As String
Const lpnLength As Integer = 255
Dim status As Integer
Dim lpName

' Assign the buffer size constant to lpUserName.
lUserName = Space$(lpnLength + 1)

' Get the log-on name of the person using product.
status = WNetGetUser(lpName, lUserName, lpnLength)

' See whether error occurred.
If status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lUserName = Left$(lUserName, InStr(lUserName, Chr(0)) - 1)

Else
' An error occurred.
MsgBox "Unable to get the name."
End
End If

GetUserName = lUserName

End Function

This looks at the current windows user name. In the table UserNames, In this table I have listed expected windows user names, names that I would expect to use the database. The query, qryUserNames is just a means of being able to filter and match the user name that's currently logged in.

SELECT tblUserNames.IngEmpID, tblUserNames.UserName
FROM tblUserNames
WHERE (((tblUserNames.UserName)=GetUserName()));

Table is called tblUserNames: In here I have listed a username as Mike. When I open the database, the first thing that runs is getusername

This bit works OK - If the user logged in windows with Mike, and Mike is in the usernames table, then Mike shows up in the filter query. If Mike doesn't appear
in the usernames table, then the query is returned blank.

All that I'm trying to do - Once the query returns a true username, I want to insert it into an unbound textbox on me.form. If the text box is returned blank then I'll restrict some functions.

Maybe I'm over complicating things....?

Thanks again for you help - Hope you can understand what it is I'm trying to do (Gulp!)

Mike

[June7]

I do something similar. I do have a login form but it is only presented to user if the user is new and therefore need a new record in Users table. The login form is default when db opens. The login form is bound to another table called Updates. This code saves computer name into Users table so I know who and where users are. User initials are passed to the Menu form with the OpenArgs argument of OpenForm method. Guess I could bind the Menu form to Users table and filter to the user record, think I thought about doing that but decided not to. Code in Menu form Open event sets textbox to the OpenArgs value. Menu form never closes so the initials are always available for other procedures. Code behind Menu form nulls the computer name field when user quits.

This is code behind the Login form.

Private Sub Form_Load()Dim Shell
If Me.tbxVersion <> Me.lblVersion.Caption Then
    If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
        'Because administrator opens the master development copy, only run this for non-administrator users
        'Check for updates to the program on start up
        'If values don't match then there is a later version
        Set Shell = CreateObject("WScript.Shell")
        Shell.Run CurrentProject.Path & "\Update.vbs"
        Dim WAIT As Double
        WAIT = Timer
        While Timer < WAIT + 3
            DoEvents
        Wend
        Set Shell = Nothing
        Application.Quit
    End If
Else
    Me.tbxVersion.Visible = False
    UserLogin
End If
End Sub

Private Sub tbxUser_AfterUpdate()
If Me.tbxUser Like "[A-Z][A-Z][A-Z]" Or Me.tbxUser Like "[A-Z][A-Z]" Then
    CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials, Permissions) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "', 'staff')"
    Call UserLogin
Else
    MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
End If
End Sub

Private Sub UserLogin()
Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & Environ("UserName") & "'")
If Not IsNull(Me.tbxUser) Then
    CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
    DoCmd.OpenForm "Menu", acNormal, , , , acWindowNormal, Me.tbxUser
    DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

Here is code for the VBScript file that is called by the above procedure.

Const OverwriteExisting = TRUE
Set objFSO = CreateObject("Scripting.FileSystemObject")
'following will copy Access file
objFSO.CopyFile "\\dotatufs02\CRM\Lab\Database\Program\Install\MaterialsDatabase.accdb", "c:\", OverwriteExisting
'following will now open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""c:\MaterialsDatabase.accdb"""

[WiReLaD]

WOW! I just want to up-date a textbox on a form!!

[June7]

Sorry if I threw too much at you. It was easier than trying to figure out your code. You don't show that SQL in the VBA procedure so I presume it is a saved Access query object and the question is how to get that query result into the code? You could do a DLookup on that query or open a recordset or just bind a form to that query.

You are doing a little more than just 'updating a textbox on form'. You want to grab the user's name from the network and authenticate their user status then populate a textbox. My sample code does that and also deals with the user if they are not in the user's table - the login form remains open for them to enter initials. For my requirements, I assume that if they could log into our network and they are working on a machine that already has the frontend installed, then they are valid users. If not in Users table then probably a new employee and need a new record. This way they don't need an administrator (me) to be bothered with entering a new user record. We do this just to make sure their initials are consistent because initials are used to track who does what with record at stages of processing. If I had a concern about duplicate initials, I would probably use autonumber field as unique ID instead of initials.

If you aren't interested in the code that checks for the db version and calls VBScript to update user copy, then remove that part.

[WiReLaD]

Moderator, Sorry if I seemed shocked on the last reply - I was shocked... I copied your code into my DB and all hell broke loose - hehe..I'd spent nearly 4hrs de-coding. It was harder do de-code your code and manage what was happening and change your code / table & field names, it seemed to have taken me ages and yet still couldn't get the answer I wanted. I now appreciate maybe its not that easy to do.

I have a friend coming around who know a little about Access / VB / Networks etc... I'll deffo re-visit your system code and sure we'll crack it together.(gulp!)

Sorry about my last response - I'll let you know how I get on and will work your code.

Thanks again
Mike

[June7]

If you want to stick with your original procedure you can probably use a DLookup to pull the user name from the query. I only offered code to present some ideas for you to consider, such as the intrinsic VBA Environ function which is much simpler than the custom functions you have, and method to deal with user not already in the table, and even an example of a DLookup. Didn't mean to make life harder for you.