Results 1 to 10 of 10
  1. #1
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44

    Different data querying from different passwords

    Here the enigma. I have:



    1) Query: Table A (employees personal data) joins Table B (employees work contract data including a personal password to access their personal form) on employee_id. The query has the TableB.Password field mandatory. If the employee fills it with his password, he's led to his personal data.

    2) Form A with the employees name list: side to every name there's a button leading to personal form through an "Open form" macro connected on the employee_Ids. Pushing the button, the query automatically produces an inputbox for password. If it fits, the Form B appears with employee personal data.

    BUGS:
    - if the employee makes a typo in inserting his password, Form B opens up anyway with empty fields (and employee screams: "where the hell are my data gone???", or worse he duplicates data inserting them again)
    - if the employee presses OK or Enter, same happens as above: Form B opens up with empty fields (same employee screams...).

    NEEDS:
    1) removing the mandatory password field in the query and
    2) a VBA code for Form B "on open" instance triggering:
    a) an input box asking for password;
    b) a comparison between the digit and the content of query column "Password", allowing access, if it fits, to the related form with the employee data related to the inserted password;
    c) a msgbox warning if the password is wrong or the inputbox is empty, proposing a retry with getting back to a) if yes, closing the form if no.

    THOUGHTS: I tried to mix different solutions I found online, but with no success. I guess VBA code should pivot on the query as recordset and trigger the access to the form only when the comparison digit/"Password" column can identify a data line. I found a code where something similar was set using the query as SQL source for VBA recordset but (damn...) I can't retrieve it anywhere. The "best" I've got so far is this:

    Code:
    Private Sub Form_Open(Cancel As Integer)
    
    Dim Psw As String
    Dim warning As String
    
    Request:
    Psw = InputBox("Insert password", "Warning!")
    If Psw = Me.Password Then
    DoCmd.OpenForm ("pers_employee")
    ElseIf Psw <> Me.Password Or IsNull(Password) Then
    warning = MsgBox("Wrong!" & Chr(13) & Chr(10) & "Try again?", vbCritical + vbYesNo, "Error") = vbYes
    GoTo Request
    Else
    Exit Sub
    End If
    End Sub
    I know it isn't enough and it doesn't work (underlined some probable mistakes)... That's why I am asking :-)

    Thank you in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    What if you eliminate the password thing,and instead capture their userID when the db opens,
    then only allow them to see their data based on the ID?

  3. #3
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by ranman256 View Post
    What if you eliminate the password thing,and instead capture their userID when the db opens,
    then only allow them to see their data based on the ID?
    I considred this solution, but knowing my people I know the presence of a user/password system to access the entire database would be something they won't appreciate: they use the Db from time to time during the day, don't keep it open all the time. Making mandatory an access password to the whole Db would be a loss of time and annoying for them, as the only reserved part is the one I mentioned about their personal data...

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with ranman256. It is probably a longer term, more maintainable solution.
    Here are a couple of video tutorials by Steve Bishop that lead you through the activities and code involved.

    A login form
    Managing user access to data

    Good luck with your project.

  5. #5
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    What I think ranman was trying to allude to was to capture the user's Window's ID and then only let them have access to those aspects of the database they need.

    I recommend the
    fOSUserName function using the API.

  6. #6
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by dashiellx View Post
    What I think ranman was trying to allude to was to capture the user's Window's ID and then only let them have access to those aspects of the database they need.

    I recommend the
    fOSUserName function using the API.
    Can you explain me what are you meaning exactly with "capture the user's Window's ID"?
    Thanks.

  7. #7
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Quote Originally Posted by davideitaly View Post
    Can you explain me what are you meaning exactly with "capture the user's Window's ID"?
    Thanks.
    Each person when they log into their PC has a "windows" or network ID. The function I linked to will allow you to return the user's windows ID and then you can use that to filter results, determine security levels, etc...


    For me. When I run the code, it returns "whammett" which is my windows/network ID.

  8. #8
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by dashiellx View Post
    Each person when they log into their PC has a "windows" or network ID. The function I linked to will allow you to return the user's windows ID and then you can use that to filter results, determine security levels, etc...


    For me. When I run the code, it returns "whammett" which is my windows/network ID.

    Thank you all. Very useful!

  9. #9
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    For those who may be interested in a case similar to mine: multipassword to access a single form (not the entire Db) showing different query results depending on the password/user, this is how I solved.

    Starting point: this very useful tutorial suggested by Orange (thanx!) here above.

    I adapted case and code to my need (see thread's opening message) and now I have:

    * FORM A (continuous) containing list with

    - employee name
    - employee surname
    - employee Id (hidden)
    - button calling FORM B on same employee Id


    * FORM B (pop-up) containing

    - inputbox for password
    - employee Id (hidden)
    - button leading to the employee personal form

    FORM B has been made following part of the instruction of the video mentioned above, from which I kept only the code related to the password case, changing the error warnings from textbox to msgbox (either for wrong password or null field) and directing the opening of the employee personal form on the basis of his Id. Here the code (in caps general indications):

    Code:
    Private Sub LoginBtn_Click()
    Dim rs As Recordset
    
    
        Set rs = CurrentDb.OpenRecordset("QUERY_NAME")
     
        rs.FindFirst "PASSWORD_COLUMN_NAME='" & Me.txtPassword & "'"
        
        If rs!PASSWORD_COLUMN_NAME <> Me.txtPassword Then
        MsgBox "Wrong password. Retry", vbCritical + vbOKOnly, "Warning!"
        Me.txtPassword.SetFocus
        Exit Sub
        ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please insert password", vbCritical + vbOKOnly, "Warning!"
        Me.txtPassword.SetFocus
        Exit Sub
        End If
        DoCmd.OpenForm "TARGET_FORM_NAME", acNormal, "", "ID=" & Me!ID
        DoCmd.Close acForm, Me.Name
    End Sub

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you have it resolved and that the Steve Bishop tutorial was helpful.
    He has several instructional tutorials.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-18-2015, 09:24 AM
  2. querying data Grouped BY WEEK
    By scorpion99 in forum Queries
    Replies: 2
    Last Post: 12-16-2013, 02:31 PM
  3. Replies: 1
    Last Post: 04-04-2013, 11:40 PM
  4. Querying All Data When Form Field Is Blank
    By jre1229 in forum Queries
    Replies: 25
    Last Post: 08-22-2012, 11:32 AM
  5. Querying Treaty Data
    By ohthesilhouettes in forum Queries
    Replies: 1
    Last Post: 06-15-2011, 11:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums