using a Query's data in a form field (1 Viewer)

Enginerd · Jun 5, 2017

Hi,

I am creating a form for a database user to have the ability to go in and set up a new user profile for the database. What I mean is that I will have a table that will have a bunch of names as well as a username and password that corresponds to each of those names that a person will go in and using a form, add themselves to those records so that they can access the secured areas of the database. What I want to happen on this form is the person will enter their first and last name. After they enter their last name and hit tab, the username field will auto-update to their first initial and last name in all lower case letters. For example, if I have a user whose name is John Smith, after tabbing off of the last name field, the username field will update to read jsmith.

I know that to do this I will need a query that will read the first and last name fields, take the data and submit it to the requirements of 1) all lowercase in username, 2) first initial from first name, and 3) entire last name, and then a way for the form to read this query and use it as the source for the corresponding spot in the data table that the form is made for. I have 3 questions related to this: 1) what type of query to use, 2) how to set it up, and 3) how to call the query as the source for the corresponding field in the table. I know I most likely will need an expression somewhere that says LCase(Left([UserFirstName],1) + [UserLastName]) but that is all I understand right now.

The_Doc_Man · Jun 5, 2017

OK, can I offer an alternative viewpoint here? I might answer your question in passing, but what you described might not be the best possible approach.

Do your workstations themselves require a login? Either via a domain login or because you have shared workstations open to the world but that require a local login? If so, you have the option to "trust" the workstation by asking it the value of the user's login name, which will be Environ("Username"). In that circumstance, your user has already entered a username and password. If you believe that process to be trustworthy, you can just accept the name and go forward. The reason I push this idea is that if you are REALLY asking for a password, remember that you have to store a password - and if someone gets into that database they can see other usernames and passwords that can already get into the database. Which means that 'identity theft' can occur (on a small-scale basis, of course). And also means that you would be unable to know who was using your database because logins could no longer be trusted.

Even if you encrypt a password, you have to be able to decrypt it, and someone of an unscrupulous nature could "copy" the code to an empty database to review it. That act would expose your passwords anyway. I'm not saying there is NO solution to this, but if you have a trusted login method already in use, don't re-invent that particular wheel.

In my last job before I retired, I enabled a person to log in to the database in order to create the account, BUT... security wonks said I had to create the account in a minimum state. Once the account was created, I had it send me a message that a new account had been created and needed to be authorized.

The details of getting the information I wanted were that if a user logged in, I checked the Users table vs. the username from the aforementioned Environ function. If it was there, I could look up anything I wanted from the record. If NOT, I took the information by asking a series of questions using the InputBox function. I could validate the format of the answers at that time.

When I had all I needed, I built a message string saying, "Your account will be associated with '<username>' and will show you as '<first name> <middle name/initial> <last name>'. We can contact you at '<phone extension or number>'. Your e-mail is '<e-mailname@domain>'. Is this correct?" Of course, it was formatted as nicely as possible, with line breaks and indentation. It was a yes/no message box, so if the new user clicked YES then I created a new record but left the account disabled. At the same time, the database sent me (and two other admins) an e-mail asking for screening. If the user clicked no, I offered them a chance to re-enter the data and try again, or to go away and do nothing. In the latter case, I didn't even have to update any tables because they said "Do nothing" so... I did nothing.

The issue of allowing a person to self-register for access to secured areas of the database is not generally a good idea, security-wise. I would advise a re-think of that facet of the operation.

You might want to also search this forum for "protecting a database" and "security a database" as topics for reading. The SEARCH function is in the thin blue ribbon at the top of each page, third option from the right.

Ranman256 · Jun 5, 2017

I would only keep the username table, and not bother storing passwords.
Instead, use the Window login authentication. They already have a password to login to windows/network, so let Windows do the work:

If user is not in the tUser table, they cant get into the db.

Code:

SUB btnLogin_Click()
Dim sUser As String, sPass As String, sDom As String

sUser = txtUser
sPass = txtPass
sDom = "CoDomain"

if sUser = Dlookup("userid","tUsers","[userid]='" & sUser & "'") then
If WindowsLogin(sUser, sPass, sDom) Then
   mbSafe = True
   DoCmd.OpenForm "frmMainMenu"
   DoCmd.OpenForm "frmLogin"
   DoCmd.Close
Else
   MsgBox "LOGIN INCORRECT", vbCritical, "Bad userid or password"
End If
end if
end sub

'-----------------
Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
'-----------------

        'Authenticates user and password entered with Active Directory.

        On Error GoTo IncorrectPassword
        
        Dim oADsObject, oADsNamespace As Object
        Dim strADsPath As String
        
        strADsPath = "WinNT://" & strDomain
        Set oADsObject = GetObject(strADsPath)
        Set oADsNamespace = GetObject("WinNT:")
        Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
        
        WindowsLogin = True    'ACCESS GRANTED
        
ExitSub:
        Exit Function
        
IncorrectPassword:
        WindowsLogin = False   'ACCESS DENIED
        Resume ExitSub
End Function

First the db is on the network, so users already have to login to windows.
In the db, I have a tUsers table, with userID, Name, and Level
USERID, FIRSTN, LASTN, LEVEL
bob12, bob smith, M
pam4, pam jones,""
xman, charles, xavier, F

when user opens the db, the main menu form will open and grab the userID.
Then lookup that persons rights in order to enable/disable controls.

Code:

public gvUserID 

sub form_load()
dim vLevel

   gvUserID =  Environ("Username")      'get userID,visible in all forms

'get level from user table
   vLevel = Dlookup("[Level]","tUsers","[userID]='" & gvUserID & "'")

'now, enable/disable items on form
   select case vLevel
         case "A"  'admin 
             'all is enabled

         case "U"  'normal user
             txtBox1.enabled = false
             txtManager.enabled = false

         case "M"  'manager
             txtBox1.enabled = false
   end select
end sub

Enginerd · Jun 5, 2017

Hi,

let me see if I can clarify what is going on here. The computer that this will be run on is password protected, however each user of the computer uses the same password to log in regardless of who they are so just telling the database that the user is trustworthy because they logged into the computer will not work. The database needs to be set up with a username and password for EACH user so that they are only able to access the records that pertain specifically to them and not to any records that pertain to other users of the database. The reason that both usernames and passwords are stored is because the database will need to check and make sure that both the username and password for the person trying to log on match and so that every record they try to access is specific to them and not other users. I already have this code. All I'm trying to do with this is set up a query or lines of code that specify that the username will always be the first initial and last name of every database user. As for the issue of security with the user setting up their own username and password, the username criteria will be the same for every user and the user will only have access to their login information during and after setup. The only user who will have access to all user information is an administrator. I hope this clears up what I'm doing and why it needs to be done this way.

using a Query's data in a form field (1 Viewer)

Enginerd

Registered User.

The_Doc_Man

Immoderate Moderator

Ranman256

Well-known member

Enginerd

Registered User.

Similar threads

Users who are viewing this thread