Solved Using recordset match to set TempVars

[mistyinca1970]

OK, I have a login form that uses recordset to validate the associated password entered into the login form.

I have been using this information entered to capture TempVars that will be used throughout the many forms in this DB. Up until now, it set the useraccess of the person who logged in and uses their email address via TempVars in any send email functions.

I'm going to be expanding the use of this database to multiple divisions in my org. And I only want certain records in various queries to be available to users who login with that respective division. So division is a column in the main table (tblContracts), and I am using it as a criteria in the query that is the data for one of the main forms.

Currently, I'm accomplishing this by having a combo box on the login form so the user can select his/her division. But that is really not the way I want to go. I would prefer it to find the corresponding division associated with that user and set it as a TempVars...then that TempVars will be the criteria in my query for the data associated with the form.

Anyway, here's my login form's login button on click event:

Private Sub btnLogin_Click()
    Dim rs As DAO.Recordset
    Dim db As Database
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryLoginAccess", dbOpenSnapshot, dbReadOnly)
       
    rs.FindFirst "Username='" & Me.txtUserName & "'"
   
    If rs.NoMatch = True Then
        Me.lblIncorrectLogin.Visible = True
        Me.txtUserName.SetFocus
        Exit Sub
    End If
    Me.lblIncorrectLogin.Visible = False
   
    
    If rs!Password <> Me.txtPassword Then
        Me.lblIncorrectLogin.Visible = True
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    Me.lblIncorrectLogin.Visible = False
   
    TempVars("UserType") = rs!UserAccess_ID.Value
    TempVars("EmailAddress") = Me.txtUserName.Value
    TempVars("UserDivision") = Me.cboDivision.Value
           
    If rs!UserAccess_ID = 1 Then
        Dim prop As Property
        On Error GoTo SetProperty
        Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
       
        CurrentDb.Properties.Append prop
    End If
   
SetProperty:
    DoCmd.OpenForm "frmUpcomingContracts"
    DoCmd.Close acForm, Me.Name

   
End Sub

And here is the recordset (qryLoginAccess) being called in this one:

SELECT tblStaffDivision.ID, tblStaffDivision.Email AS Username, ********* AS [Password], tblStaffDivision.UserAccess_ID, *********** AS Password2, tblStaffDivision.Division
FROM tblStaffDivision
ORDER BY tblStaffDivision.Staff;

(********** concatenated password removed here for security reasons)

So I want it to find the division associated with the email address of the user that logs in and set the TempVars off that. I don't know how to write this. I can't necessarily copy the password validation process as it is asking to see if the password entered matches the password associated with the username entered (email address). How do I tell it to find the division and set that TempVars?
Thank you,

EDIT:

Shoot! Why is it when I type it out and ask for help, I then see the answer? I think this is it here:

    TempVars("UserType") = rs!UserAccess_ID.Value

So I would use

 TempVars("UserDivision")=rs!StaffDivision.Division

Gonna go try it out.

 

[NauticalGent]

Enjoying the conversation with yourself??

So which one on you is going to report back the results?

 

[Minty]

If you brought the division in with the employee detail in the login form in the combo (hidden if necessary) it would be readily available from that, without needing to look it up anywhere?

 

[isladogs]

Whilst you're having fun testing your idea, one very important side issue to consider .

Storing user passwords in a database is risky. If these are accessed by anyone, you will be in breach of GDPR.
Not even the network admin should know users passwords.
If you really MUST do this, then make sure the saved passwords are strongly encrypted.
Better still, use Active Directory to check credentials.

 

[mistyinca1970]

Enjoying the conversation with yourself??

So which one on you is going to report back the results?

I apologize. I often end up typing it all out, and then figure it out before I ever post the thread, in which case then I don't ever post it. In this case, I had already let the cat out of the bag. I'll see if I am able to delete the thread.

 

[mistyinca1970]

Whilst you're having fun testing your idea, one very important side issue to consider .

Storing user passwords in a database is risky. If these are accessed by anyone, you will be in breach of GDPR.
Not even the network admin should know users passwords.
If you really MUST do this, then make sure the saved passwords are strongly encrypted.
Better still, use Active Directory to check credentials.

Right now it is a concatenated algorithmic password; not something the user chooses himself. Once I am done testing all this, I will move to something the user is able to reset. Baby steps. (This is a learn as I go project!)

 

[mistyinca1970]

If you brought the division in with the employee detail in the login form in the combo (hidden if necessary) it would be readily available from that, without needing to look it up anywhere?

The previous scenario was setting the tempvars from the combo, but I want to restrict the access to the division associated with the user. I don't want the user to select a division and then have access to that division's records if that's not the division they wanted. This also gives me the option to allow certain users a "*" division that will give access to all records.

 

[NauticalGent]

I'll see if I am able to delete the thread.

Perish the thought...

 

[theDBguy]

Hi. Have you tried using DLookup()?

 

[mistyinca1970]

Hi. Have you tried using DLookup()?

I have not. Don't know how that one works. However, I am happy to report that I did successfully add the line of code I needed to accomplish this! I was wrong in my first post. I ended up adding:

TempVars ("UserDivision") = rs!Division.Value

Works as intended!

Thanks!

 

[isladogs]

@mistyinca1970
1. You should never delete a thread which has answers.
2. I'm not sure exactly what you mean by a 'concatenated, algorithmic password. However, whether its that or one that users set/reset themselves isn't relevant to the point I was making.
NEVER save passwords in a format that is readable by humans. Encrypt them using 128-bit encryption (or better).

 

[theDBguy]

I have not. Don't know how that one works. However, I am happy to report that I did successfully add the line of code I needed to accomplish this! I was wrong in my first post. I ended up adding:

TempVars ("UserDivision") = rs!Division.Value

Works as intended!

Thanks!

Congratulations! Glad to hear you got it sorted out. Cheers!