Check for existing EmployeeId in the table and then update password in same table

[aditijangir]

Dear Experts,

I am stuck with pretty good error, which I understand but unable to cope up with.

What I am expecting is : I have a form to provide OTP to 1st time users. The user have to select the Employee Name from the "Combo Box" which is fetched from the Table "AddUser". Now All the validation for checking is perfectly fine. But the twist is; I want that the value entered in the password textbox should be inserted in the table "AddUser" by comparing the Employee Id in it.
Note: Initially the value in password field is null.


The Details are as below:
Table 1:- AddUser(Id{Auto no},EmployeeId,Password,SOEID...etc)

Table 2:- tblUserquery(EmployeeId,Password,SOEID)

There is a form 'GetPassword'

Code:

Option Compare Database
 
Private Sub CboUser_AfterUpdate()
Me.txtLogInPassword = DLookup("Password", "tblUserqry", "[EmployeeId]=" & Me![CboUser])
Me.UserName = DLookup("EmployeeId", "tblUserqry", "[EmployeeId]=" & Me![CboUser])
Me.txtSoeid = DLookup("SOEID", "tblUserqry", "[EmployeeId]=" & Me![CboUser])
 
If IsNull(Me.txtLogInPassword) And Me.txtSoeid = txtUserSoeid Then
    Me.txtNewPassword.Enabled = True
    Me.txtConfirmnewpassword.Enabled = True
 
Else
    MsgBox "Applicable only for New Users"
    Me.txtNewPassword.Enabled = False
    Me.txtConfirmnewpassword.Enabled = False
End If
 
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.txtNewPassword.Enabled = False
    Me.txtConfirmnewpassword.Enabled = False
End Sub

Private Sub submit_Click()
Dim Pwd As String
'Dim User As String
 
    If Me.txtNewPassword = Me.txtConfirmnewpassword Then
        'DoCmd.RunCommand acCmdSaveRecord
 
        Pwd = Me.txtConfirmnewpassword
        'User = Me![CboUser]             'Assigning value of current user to the var
        MsgBox "Congrats! Password Changed Successfully"
        'DoCmd.close acForm, "GetPassword", acSaveYes
        CurrentDb.Execute "INSERT INTO tblUserqry ([Password]) VALUES ('" & Pwd & "') WHERE tblUserqry.EmployeeId='" & User & "';", dbFailOnError
        'CurrentDb.Execute "UPDATE tblUserqry SET Password='& Pwd &' WHERE tblUserqry.EmployeeId='" & User & "';", dbFailOnError
 
        'CurrentDb.Execute "UPDATE tblUserqry where SET Password='& Pwd &' WHERE tblUserqry.EmployeeId= & Me![CboUser] ;", dbFailOnError
 
 
    Else
        MsgBox " Your Password doesnot match, try again!", 48, " Password Error "
    Me!txtNewPassword = ""
    Me!txtConfirmnewpassword = ""
    Me!txtNewPassword.SetFocus
 
    End If
End Sub

Now the I have tried lots of queries and ways to get it work, but it is not helping out.

I will be glad to hear answer from you.

Thanks in advance.

Looking forward for your replies.

[zmbd]

Simple update query will do this or you can get very fancy...

One problem I see with your code is the constant use of currentdb each use creates a new pointer to the database...

Now I use record sets a lot... so as an example

Dim zDB as DAO.Database
Dim zRS as DAO.Recordset
Dim zSQL as string
'
zSQL = "some sqlstring here"
 
'
'See here is where I set the pointer to the current
'database only once
Set zDB = CurrentDB
'
'if you are using recordsets...
Set zRS = zDB.OpenRecordset(zSQL,(option to how to open))
'
'
'here's how with your execute command
zSQL = "some sqlstring here"
zDB.Execute zSQL
'
zSQL = "some sqlstring here"
zDB.Execute zSQL
'
zSQL = "some sqlstring here"
zDB.Execute zSQL
'
zRS.Close
if not zRS is Nothing then Set zRS = Nothing
if not zDB is Nothing then Set zDB = Nothing
 

Notice the zSQL string... build your SQL Execute strings first, then place them in the function (like I've done in the code above to open a recordset, no difference except the syntax of the string).

Most likely your string is not resolving correctly

if you build the string first then you can insert a debug print for troubleshooting
debug.print "Your criteria = " & strSQL
- press <ctrl><g> to open the immediate window
- you can now cut and paste this information for review!

(Database.Execute Method (DAO) (the examples here also show using this method)

Also go to Microsoft Access / VBA Insights Sitemap and search page for SHA2 or MD5 as one really shouldn't store the passwords as plantext

[Rabbit]

I second Z's recommendation that you should not store passwords in plain text. It's extremely bad security practice. Just look at all the stories in the news recently about data breaches and leaked passwords, you don't want to end up there with poor security practices.

In the first block of code, on line 4, you shouldn't be loading the password into a form control. That let's anyone see everyone else's password.

In the first block of code, on line 5, you don't need to do a lookup on the employee id, you already have it in the combo box.

[aditijangir]

First of all I would like to thank zmbd and rabbit for your valuable time. I am glad that somebody is there to guide me.

Now I will look into what zmbd suggested me to.

Also, I am aware about the MD5 Algo. But, If I am not allowing users to go beyond the form view, will they be able to view my control values (as they are invisible)?. Actually, I was not knowing the way to implement those algo's to secure my highly sensitive data's. Thanks zmbd :)

Thanks a lot to highlight the flaw in line 5, I have corrected it @rabbit. :)

[aditijangir]

Hi zmbd, thanks a lot for your reply.

Thanks for making me understand the outcome of CurrentDB , as was not knowing that before.

I have a query regarding the code you provided( a lame question I know) , what I need to input in Set zRS = zDB.OpenRecordset(zSQL,(option to how to open)).

Also, Why you have mentioned zSQL = "some sqlstring here" so many times in the code. Please guide. I want to learn.

Thanks in advance.

[zmbd]

aditijangir
Thanks for making me understand the outcome of CurrentDB , as was not knowing that before.

Learned that here myself so I am very happy to pass that information along to the next person. :)

I need to input in Set zRS = zDB.OpenRecordset(zSQL,(option to how to open)).

This would be only if you are going to use the recordset method. I most likely shouldn't have included the line and apologize for confusing the issue.
By way of explanation, once the pointer to the database is set, using the Database.OpenRecordset Method (DAO) one can use either a stored query or build an SQL string to pull records. In the link above, the examples show building the SQL string within the function...

Set rest = dbs.OpenRecordset(_ 
    "SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 " & _
    "AND Date()-30 ORDER BY LastVisitDate DESC")
 

where I advise

zSQL ="SELECT * " & _
   "FROM Customers " & _
   "WHERE LastVisitDate " & _
      "BETWEEN Date()-60" & _
       "AND" & _
         "Date()-30 " & _
   "ORDER BY LastVisitDate DESC"
Set rest = dbs.OpenRecordset( zSQL, dbOpenDynazet )
 

Also, Why you have mentioned zSQL = "some sqlstring here"

The intention was to indicate that you could have different Action-SQL strings INSERT INTO [table_name]..., UPDATE [table_name]..., DELETE * FROM [table_name]..., etc... at each point in the code by simply building different strings using the same string variable and the same execute statement.

Hopefully that will answer your questions, if not, please feel free to start a new thread. I'm also sending you a copy of my boilerplate of tutorials and tools that I've found useful over time and should help answer many of your other questions... please check your Bytes.com inbox.

-z

[aditijangir]

@zmbd

I have used the recordset in my code but it throughing error "Invalid Operation" in Set zRS=zDB.OpenRecordset(zSQL,...) line.

My code looks like this:(Please provide feedback)

Dim zRS As DAO.Recordset
Dim zSQL As String
 '
 zSQL = "UPDATE AddUser SET [Password]='" & Pwd & "' WHERE AddUser.EmployeeId='" & User & "';"
 
 '
 'See here is where I set the pointer to the current
 'database only once
 Set zDB = CurrentDb
 '
 'if you are using recordsets...
 Set zRS = zDB.OpenRecordset(zSQL, dbOpenSnapshot) '------dont know what to do Set zRS = zDB.OpenRecordset(zSQL, (Option how to open))
 '
 '
 'here's how with your execute command
 zSQL = "UPDATE AddUser SET [Password]='" & Pwd & "' WHERE AddUser.EmployeeId='" & User & "';"
 zDB.Execute zSQL
 '
' zSQL = "some sqlstring here"
' zDB.Execute zSQL
 '
' zSQL = "some sqlstring here"
' zDB.Execute zSQL
 '
 zRS.close
 If Not zRS Is Nothing Then Set zRS = Nothing
 If Not zDB Is Nothing Then Set zDB = Nothing
 
 

Sounds great!

I must say you are really good in explaining. I admire that :)

I just have one query which I recently posted before seeing your reply.
Set zRS = zDB.OpenRecordset(zSQL, dbOpenSnapshot)
the system is throwing beautiful error stating that "Invalid Operation" . Can you please explain me the reason for that.

Thanks a lot for sending me the copy of boiler plate.. :)

[zmbd]

The Openrecordset method requires that one use a "Select" type query/sql or a table. Action queries such as "Update" are used with the execute method.

Hence, the error occurs in Line 12 in post#7 because the SQL in Line 4 is an action query not a select query or table. Now, assuming Line 4 resolves correctly, then you can use that with the execute statement at line 17 (or the others)


Once again my apologies for confusing the issue.

The execute method is very useful for single entry updates such as you are doing here, and some other actions, as it handles all of the recordset related code with only minimal effort...

[Rabbit]

But, If I am not allowing users to go beyond the form view, will they be able to view my control values (as they are invisible)?.

You are forgetting they can just look at the table data. They don't need to go through the form. There's nothing you can do to completely prevent them from going directly to the table.

If you're making the control invisible, then there's no need to load it into the control in the first place. You can just hold the value in a variable or retrieve it when needed.

[aditijangir]

Thank you zmdb. :)

@Rabbit: I am implementing MD5 for securing the password in my application :D

But 1 question ( just out of excitement ) : If I am splitting the DB and securing it with Password. Is it still possible that my users can see the table ?

[Rabbit]

Yes it is. Because the table has to be linked to the database. And for it to link correctly, the password must be supplied. So they can still see the table data.

If you have a need for real security, Access is not a good choice. There's nothing you can do that can't be undone by a user. But if you have no choice but to use Access, hashing the password is a good step forward. Other steps that you might want to take to make it harder are:

• Encrypting or hashing the username
• Using a salt in the hash / encryption

[aditijangir]

Ohk.

I thought that splitting the data and encrypting the Password will help me out. But the thing is the users who are going to use this application will have minimal knowledge . so, this should not be the point of concern.

Also, I in a big doubt about the admin login. I have created the user login(multi level user login) successfully. But the trouble how to give rights to admin so that , the table ,reports, ribbon will be hidden for rest of the users and the admin can still be able to use that.

And I have encrypted my database, but to my surprise the whole application is locked and now I am not able to remove that password. As to circulate the application, it will be difficult for rest of the users.

{Also, I have read somewhere to user workgroup :P
which I created, but (lol) don't know how to add users in it.}

Please provide your input it would be of great help

[Rabbit]

This thread is starting to get off topic. The original question has been answered. And in an attempt to keep this from spiraling out of control, you should break your multitude of questions into separate threads.