Delete Current Record With Password

[smtazulislam]

Hello,
I have a DELETE button in the continuous form which name fromEmployeeVacationSubform. Today someone operate my DB and delete some important data. So, I want If user try to delete data then form required user password (Which password he access this database) for confirmed.
Here I try to One SQL and Without SQL.

Private Sub cmdDelete_Click()
Dim StrSQL As String
If MsgBox("Are you sure you want to DELETE " & Me.VacationID & "?", vbCritical + vbYesNo, "Delete") = vbYes Then
    StrSQL = "DELETE * FROM tblEmployeeVacation WHERE VacationID = " & Me.VacationID
    CurrentDb.Execute StrSQL
Else
    DoCmd.RunCommand acCmdUndo
End If
    Me.Requery
    
'    If MsgBox("Are you sure you want to DELETE? ", vbCritical + vbYesNo, "Delete") = vbYes Then
'        DoCmd.RunCommand acCmdDeleteRecord
'    Else
'        DoCmd.RunCommand acCmdUndo
'    End If
End Sub

Password form name is frmDeleteDataPass.
Advance thanks and appreciate for help....

 

[Isaac]

Look into using InputBox - no additional form needed.

 

[smtazulislam]

Look into using InputBox - no additional form needed.

Okay, Thank for support.
InputBox(Message, Title, Default, 1500, 1500)
InputBox (prompt:=strMsg, title:="Password Required", xpos:=1500, ypos:=1500)

Edit/
Code:

    Dim StrInput As String
    Dim StrMsg As String
    Dim StrUser As String
    
    StrMsg = "Enter Delete Password"
    
    StrInput = InputBox(Prompt:=StrMsg, Title:="Password Required", xpos:=2000, ypos:=2000)
    
    If StrInput = "123" Then
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    Exit Sub

This code work...
I need SQL from user table tblUsers put StrInput = "SQL "
My user SQL like :

StrUser = "SELECT * FROM tblusers WHERE [LoginName] = """ & Me![UserName] & """" & " AND [Password] = """ & Me![Password] & """"

So, if I call Input Box no need to use UserName. Right?
can correction SQL please.
But I feel better if from to operate it.

 

[Isaac]

This code work...

Great! Glad to hear you got that part working.

So, if I call Input Box no need to use UserName. Right?
can correction SQL please.
But I feel better if from to operate it.

Ok I apologize but I had a little trouble understanding.
Do you mean that, in order to evaluate whether the user-typed password is "correct", you need to look up the password that corresponds to that user in a table? I guess I'm confused on how the "user" figures into the whole thing.

Your original code structure looked good to me. I would recommend the Currentdb.Execute (sql) method, I avoid ever using "menu" options in VBA...too risky (context-dependent). Kind of in the same "family" of risks as SendKeys is.

 

[smtazulislam]

the user-typed password is "correct", you need to look up the password that corresponds to that user in a table?

Yes, I want to user password required. exp. A member login this DB and this password gave when he want to delete data.

I would recommend the Currentdb.Execute (sql) method, I avoid ever using "menu" options in VBA...too risky (context-dependent).

So you recommend like....

    Dim StrSQL As String
    Dim StrInput As String
    Dim StrMsg As String
    Dim StrUser As String
    
    StrUser = "SELECT * FROM tblusers WHERE [Password] = """ & Me![Password] & """"

    StrMsg = "Enter Delete Password"
    
    StrInput = InputBox(Prompt:=StrMsg, Title:="Password Required", xpos:=2000, ypos:=2000)
    
    If StrInput = "StrUser" Then
    StrSQL = "DELETE * FROM tblEmployeeVacation WHERE VacationID = " & Me.VacationID
    CurrentDb.Execute StrSQL
    Else
    DoCmd.RunCommand acCmdUndo
    End If
    Me.Requery
    Exit Sub

??? : StrUser can string with double quotation Mark ( " ")

 

[Isaac]

So the password he should give for permission to Delete the data, it's the exact same password he already gave when logging in?
And you store this value on control Me.Password? (form textbox) ?
And you want your code to test IF: Is the password provided in the inputbox, the same as the password stored in Me.Password textbox? Is that right?

But when I see your strUser code, then I am leaning toward a Dlookup of some kind, but still can't tell precisely what's required. I can't tell exactly what you're needing to look up in table.

Can you explain everything in simple terms - in sequence - with no suggested code? I think the suggested code is confusing me more.

 

[smtazulislam]

So the password he should give for permission to Delete the data, it's the exact same password he already gave when logging in?

YES.

And you store this value on control Me.Password? (form textbox) ?
And you want your code to test IF: Is the password provided in the inputbox, the same as the password stored in Me.Password textbox? Is that right?

I put before code, I had change this

 StrUser = "SELECT * FROM tblusers WHERE [Password] = """ & StrInput & """"

Can you explain everything in simple terms - in sequence - with no suggested code? I think the suggested code is confusing me more.

Okay this is I try

On Error GoTo Err_cmdDelete_Click
    Dim LogonAttempts As Integer
    Dim StrUser As String
    Dim StrMsg As String
    Dim StrInput As String
    Dim StrSQL As String
       
    
    For LogonAttempts = 1 To 5
    StrUser = "SELECT * FROM tblusers WHERE [Password] = """ & StrInput & """"
    StrMsg = "Enter Delete Password"
    StrInput = InputBox(Prompt:=StrMsg, Title:="Password Required", xpos:=2000, ypos:=2000)
    
    If StrInput = "StrUser" Then
        StrSQL = "DELETE * FROM tblEmployeeVacation WHERE VacationID = " & Me.VacationID
        CurrentDb.Execute StrSQL
    Exit Sub
    Else
    'Warring Message
        Dim Msg, Style, Title, Response
            Msg = "Password Incorrect"
            Style = vbOKOnly + vbExclamation
            Title = "Password error"
            Response = MsgBox(Msg, Style, Title)
    DoCmd.RunCommand acCmdUndo                  'The record is undo if password incorrect
    End If
    
    Next LogonAttempts
    DoCmd.Quit
    
Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

Now give error "UNDO isnot available now"

I try to put

    If StrInput = "StrUser" Then
        StrSQL = "DELETE * FROM tblEmployeeVacation WHERE VacationID = " & Me.VacationID
        CurrentDb.Execute StrSQL
    ElseIf    'Why here gave error
    DoCmd.RunCommand acCmdUndo                  'The record is undo if password incorrect
    Exit Sub
    Else     
    'Warring Message
        Dim Msg, Style, Title, Response
            Msg = "Password Incorrect"
            Style = vbOKOnly + vbExclamation
            Title = "Password error"
            Response = MsgBox(Msg, Style, Title)
    
    End If

Now I have two problems....
Undo & Elseif Error

 

[Isaac]

I'm sorry, what I meant was, please explain how everything works/how you want it to work in simple terms WITHOUT code.

like:

- user logs into database
-they enter this
-this data is stored in table
-when he does the delete, i need to check a) check this, b) check this, and c) check this

 

[smtazulislam]

-this data is stored in table
-when he does the delete, i need to check a) check this, b) check this, and c) check this

Oh hu! I see, so I need to create a stored table for deleted data. and after that restored from here if needs.

 

[Isaac]

Oh hu! I see, so I need to create a stored table for deleted data. and after that restored from here if needs.

you misunderstood me . I was suggesting the way that you might explain to me what your requirements are, since currently I'm still having trouble understanding what your requirements are. I am requesting that you walk me through them by explaining them in simple terms ,without any code, from start to finish. then I will do my best to help you if I understand the requirements. Thank you.

 

[smtazulislam]

you misunderstood me . I was suggesting the way that you might explain to me what your requirements are, since currently I'm still having trouble understanding what your requirements are. I am requesting that you walk me through them by explaining them in simple terms ,without any code, from start to finish. then I will do my best to help you if I understand the requirements. Thank you.

I have a HR database and they're have two users, 1. Day Shift and 2. Night Shift.
If any user want to DELETING data then FORM will required password (Login Password) and if Password isn't correct then he can't delete data.
Because, second user delete some data yesterday but he said, he dont deleted. So, I thinking that if I put password in the delete button then data record will be safety. That all dear.

 

[Isaac]

Ok, and what's the approved password going to be? Will it be hard coded in the VBA code, or will the password be looked up from a table ?
Meaning, how does the code know the password is correct?

 

[smtazulislam]

Ok, and what's the approved password going to be? Will it be hard coded in the VBA code, or will the password be looked up from a table ?
Meaning, how does the code know the password is correct?

Thanks finally you understand my motive. What is the solution ? What can I do ?

 

[Isaac]

I was asking you a question by that.
I'm sorry, but it seems I've been unable to understand your requirement properly. If someone else sees this thread and wants to be a saint and jump in, feel free. Alternately, if you want to begin again at the beginning in your native language, I can try to use Google translate to understand it, but only if you thoroughly explain everything you need from start to finish in great detail. Thanks.

 

[Gasman]

Perhaps do not allow deletion, but just mark as deleted instead.?
Then no need to copy to a deleted table.?

 

[smtazulislam]

I was asking you a question by that.
I'm sorry, but it seems I've been unable to understand your requirement properly. If someone else sees this thread and wants to be a saint and jump in, feel free. Alternately, if you want to begin again at the beginning in your native language, I can try to use Google translate to understand it, but only if you thoroughly explain everything you need from start to finish in great detail. Thanks.

I apologize, maybe I'm not enough sentence mentioning failed to write on this thread.
mesg, #1 I wanting one thing then I change it and try to Dlookup. Because, last time I guess how password looked up correct or Incorrect. So, I am very sorry..

 

[smtazulislam]

Then no need to copy to a deleted table.?

Thanks for reply,
Okay. If a user delete date maybe by mistake. So, I want to deletion data restoring, How to I restore it.

 

[Gasman]

Thanks for reply,
Okay. If a user delete date maybe by mistake. So, I want to deletion data restoring, How to I restore it.

If it is deleted, then it is deleted.

I am suggesting to have a field to mark the record as 'deleted' and then filter those records out that have that flag set.?

The experts here normally say do not delete, but mark the record in some way and then hide those records from normal processing.

THEN, if you have to 'restore' that record back, just clear that flag. That can be via a special form that only the admin can use.?

 

[smtazulislam]

If it is deleted, then it is deleted.

I am suggesting to have a field to mark the record as 'deleted' and then filter those records out that have that flag set.?

The experts here normally say do not delete, but mark the record in some way and then hide those records from normal processing.

THEN, if you have to 'restore' that record back, just clear that flag. That can be via a special form that only the admin can use.?

Thanks for give in good idea. I try it later.
Can you check mesg #7 why code is not work ? ...

User table name is tblUsers field name "LoginName & Password"
I put : StrUser = "SELECT * FROM tblusers WHERE [Password] = """ & StrInput & """"
I enter correct password but form message me "Incorrect Password"
Where is the problem I dont know .

Form:
Main from Name is frmEmployeeVacation
SubForm name is frmEmployeeVacationSubfrom
Delete button in the subform.

 

[Gasman]

So strUser is a string variable which contains a Select statement and you are comparing the password entered to a string that has the value of "struser"?

Have you tried entering a password of StrUser ?

Plus your are using strUser before you have even asked for it?

Sit back, take a breath and look and understand the code you have written.

Computers are very stupid. I might be able to work out what you are trying to do, but a computer just follows your instructions, line by line.

Also start uing F8 in the debug window and watch the flow of the code AND inspect the contents of the variables.