Problem with code to delete a record

[law]

Hello,

I have got a problem with a command button on my form. The problem is when you click the delete button I want it to bring up a login form as only administrators can delete records, so this works fine but once you have entered you user name & password into the box & click ok it doesnt delete the record & it isnt giving me any error messages.

Can anyone help I have put the code below:

Private sub cmdOK_click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim gt As String

If Nz(txtUserName) = "" Then
MsgBox "You must enter a user name", vbInformation, "No Name Entered"
txtUserName.SetFocus
Exit Sub
End If

If Nz(txtUserPassword) = "" Then
MsgBox "You must enter a password", vbInformation, "No Password Entered"
txtUserName.SetFocus
Exit Sub
End If


strSQL = "SELECT [userPassword],[userPriviliges] FROM [userDetails] WHERE [userName]='" & txtUserName & "';"

Set rst = CurrentDb.CreateQueryDef("", strSQL).OpenRecordset

If Not rst.EOF Then


If rst("UserPassword") = txtUserPassword Then

If rst("UserPassword") = txtUserPassword Then

Select Case rst("userPriviliges")
Case "Normal"

MsgBox "Invalid user name or password entered, only administrators can delete staff records", vbExclamation, "Invalid UserName"

txtUserName.SetFocus

Case "Super User"

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

End Select
Else


MsgBox "Password incorrect. Please try again", vbExclamation, "Password Incorrect"
txtUserPassword.SetFocus
End If
Else

MsgBox "Invalid user name given. please try again", vbExclamation, "Invalid User Name"
txtUserName.SetFocus
End If

rst.Close
Set rst = Nothing

intAttempts = intAttempts + 1
If intAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


DoCmd.Close acForm, "formLoginDeleteStaffDetails", acSaveNo

If intAttempts & gt = 3 Then DoCmd.Quit acQuitSaveNone
End If
End Sub

Thanks!

 

[ghudson]

I do not have time to play with your code but your problem has to be with the placement of your End If's. Place a 'test' message box after each If command to test the results. You also need to get rid of the DoCmd.DoMenuItem commands since they are outdated and not convertable to a new version of access. Check out the DoCmd.RunCommand options.

HTH

 

[law]

problem with code

I have taken your advice & removed the DoMenuItem parts out of this code & I have replaced it with the command.

DoCmd.RunCommand acCmdDeleteRecord

now I am getting the error message saying "The command or action acCmdDeleteRecord isn't available now"

anyone help please?

 

[ghudson]

You might have to select the record. Here is some old code I used to use an input box to verify if the user is allowed to delete records. Can you manually delete records? Does your record set allow the deletion of records?

Private Sub bDeleteRecord_Click()
On Error GoTo Err_bDeleteRecord_Click
    
    Dim strInput As String
    Dim strMsg As String
    
    Beep
    strMsg = "Please key the Delete Password to allow the deletion of the current record."
    strInput = InputBox(Prompt:=strMsg, Title:="Delete Password")
    If strInput = "SpecialPassword" Then
        Beep
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
        Refresh
    Else
        Beep
        MsgBox "Incorrect Password!" & Chr(13) & Chr(13) & "You are not allowed to delete any table records." & Chr(13) & Chr(13) & "Please contact your database administrator for help.", vbCritical, "Invalid Password"
        Exit Sub
    End If
    
Exit_bDeleteRecord_Click:
    Exit Sub
    
Err_bDeleteRecord_Click:
    MsgBox Err.Description
    Resume Exit_bDeleteRecord_Click
        
End Sub

HTH

 

[law]

problem with code

Thanks very much for your help it is much appreciated. I have changed my code in the relevant to what you suggested but it is still giving the same error message as before.

Any more ideas?

The new code is below:

Private Sub cmdformStaffDetailsDeleteRecord_Click()

Dim rst As DAO.Recordset
Dim strSQL As String
Dim gt As String

If Nz(txtUserName) = "" Then
MsgBox "You must enter a user name", vbInformation, "No Name Entered"
txtUserName.SetFocus
Exit Sub
End If

If Nz(txtUserPassword) = "" Then
MsgBox "You must enter a password", vbInformation, "No Password Entered"
txtUserName.SetFocus
Exit Sub
End If

strSQL = "SELECT [userPassword],[userPriviliges] FROM [userDetails] WHERE [userName]='" & txtUserName & "';"

Set rst = CurrentDb.CreateQueryDef("", strSQL).OpenRecordset

If Not rst.EOF Then

If rst("UserPassword") = txtUserPassword Then

If rst("UserPassword") = txtUserPassword Then


Select Case rst("userPriviliges")
Case "Normal"

MsgBox "Invalid user name or password entered, only administrators can delete staff records", vbExclamation, "Invalid UserName"

txtUserName.SetFocus

Case "Super User"

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Refresh

MsgBox "You have removed the member of staff from the database", vbInformation, "Record Deleted"
End Select
Else

MsgBox "Password incorrect. Please try again", vbExclamation, "Password Incorrect"
txtUserPassword.SetFocus
End If
Else

MsgBox "Invalid user name given. please try again", vbExclamation, "Invalid User Name"
txtUserName.SetFocus
End If


rst.Close
Set rst = Nothing

intAttempts = intAttempts + 1
If intAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If

DoCmd.Close acForm, "formLoginDeleteStaffDetails", acSaveNo

If intAttempts & gt = 3 Then DoCmd.Quit acQuitSaveNone
End If
End Sub

 

[ghudson]

Forget your id and password code for now. Test it without that stuff and see if you can actually delete records. Can you manually delete records? Does your record set allow the deletion of records?

 

[law]

problem with code

I have now, just tried to run the code below in the same click event with nothing else in their, and it is still returning the same error message as before all that is in the click event at the minute is the code below.
Regarding Recordsets I aren't the best at I dont thoroughly understand them as I havent really looked into them that much.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Refresh

MsgBox "You have removed the member of staff from the database", vbInformation, "Record Deleted"

any ideas?

 

[ghudson]

The "source" of your form is not allowing you to delete the records. And [or] your form is set up to not allow "deletions". There is a form property to disable form additions/deletions etc. Check to ensure that is set properly. My guess is that the data source of your form is not allowing you to delete the records. Are you using a query that is linking to multiple tables and/or queries. You need to take a step back and see if [how] you can MANUALLY delete one record. Once you determine if you can do that then go back and try to do it with VBA.

HTH