Admin Type Record Undelete

[abelingaw]

http://www.daniweb.com/forums/thread341763.html

Regards to that, i also wanted the system not to be able to delete Administrator Accounts

via my code also posted there.

Here's the code again:

Private Sub cmdDelete_Click()

 ' On Error GoTo err
    
    Dim intYN
    
    intYN = MsgBox("You are about to delete a record." & vbCrLf & _
        "If you click Yes, you won't be able to undo this delete operation." & _
        vbCrLf & vbCrLf & _
        "Are you sure you want to delete this record?", vbExclamation + vbYesNo, "Confirm Delete")
        
    If intYN = vbNo Then Exit Sub
    
    DBLink.Execute "Delete From Users Where ID = " & lvwUser.SelectedItem.Text
 
    DBLink.Close
    
    Call Form_Load
    
    txtName.Text = ""
    txtPass.Text = ""
    txtConfirm.Text = ""
   
    MsgBox "The Record has been deleted.", vbInformation

    
    Exit Sub
    
Err:
    MsgBox Err.Description, vbCritical

End Sub

My Database field is Users and has 4 fields.
UserType, ID, UserName and Password respectively.

If the user tries to delete an account where the UserType is Administrator, it wont be deleted, else if the UserType is User, it can be.

Sorry, I'm really not good in SQL. :$

[jhai_salvador]

If you don't know much about sql, then try the other way around like storing record from a variable.

Dim UserType as string 

UserType = rs!UserType 
if UserType <> "Admin" Then
   rs.Delete
End If

Heres a code of the delete using sql.

DBLink.Execute "Delete From Users Where ID = " & lvwUser.SelectedItem.Text & " AND UserType = 'Administrator'"

DBLink.Execute "Delete From Users Where ID = " & lvwUser.SelectedItem.Text & " AND UserType = 'User'"

[ChrisPadgham]

If you are using the SQL, you may like to investigate using the RecordsAffected property to determine whether the row was deleted and inform the user accordingly.

[abelingaw]

It does work but it wont delete even the User type accounts.

Plus, i got an error saying Operation not allowed whent the object is Open Here's the modified code:

Private Sub cmdDelete_Click()

Dim Class As String
Dim intYN
    
    intYN = MsgBox("You are about to delete a record." & vbCrLf & _
        "If you click Yes, you won't be able to undo this delete operation." & _
        vbCrLf & vbCrLf & _
        "Are you sure you want to delete this record?", vbExclamation + vbYesNo, "Confirm Delete")
        
    If intYN = vbNo Then Exit Sub
    
    Con "Database.mdb"
    
    RecSet.Open "Select * From Users", DBLink, adOpenKeyset, adLockOptimistic
    
    Class = RecSet.Fields("UserType").Value
    If Class = "Administrator" Then
        MsgBox "Operation not allowed. Administrator accounts cannot be deleted.", vbCritical, "Error"
        Exit Sub
    Else
        RecSet.Delete
    End If
        
        
        'DBLink.Execute "Delete From Users Where ID = " & lvwUser.SelectedItem.Text
    DBLink.Close
    
    txtName.Text = ""
    txtPass.Text = ""
    txtConfirm.Text = ""
   
    MsgBox "The Record has been deleted.", vbInformation
    
    Form_Load
    
End Sub

[jhai_salvador]

Why not use my err function in the code snippet? so it will be easy to locate the error line.

Make sure that your recordset is closed before you open it again.

if RecSet.State = AdStateOpen Then
RecSet.Close
End If

[AndreRet]

On which line does the error occur? The sql statement that Jhai has given is fine, so where do we find the error?

[abelingaw]

If i try to delete an administrator type, the err message appears and yes, it does not delete the record.

But the same happens to a user type account.

And the Operation not allowed when the object is Open error appears when i click my listview control again to select a record.

Here's my code for the listview:

Con "Database.mdb"
    With RecSet
        .Open "Select * From Users where ID = " & lvwUser.SelectedItem.Text, DBLink, adOpenKeyset, adLockOptimistic
            If .EOF = False Then
                txtName.Text = .Fields("UserName")
                txtPass.Text = .Fields("PassWord")
                txtConfirm.Text = .Fields("PassWord")
                cboPriv.Text = .Fields("UserType")
            End If
        .Close
    End With
    DBLink.Close

[AndreRet]

Before you make a call to the database again, close the recordset.

Recset.Close

Con "Database.mdb"
    With RecSet

You can not make another call to it whilst is has another recordset in memory.:)

[jhai_salvador]

Yes, just like my 2nd Post. :D

[AndreRet]

We all have to learn the HARD way, ouch!;)

[masterfact18]

can someone translate the code into a adodc with datagrid..because i'm not very familiar with listview, besides i only use adodc with datagrid... I'm hoping that someone will translate it to me, because i can't translate it myself..thanks...

[abelingaw]

@masterfact18

Sorry bro, I'm not familiar with datagrid, i prefer listview.

(Kinda Hijacking my thread)

[AndreRet]

@Masterfact18, you need to open your own thread with the question. You can not use someone else's post for that.

@Abe, has your question been answered? If so, you know what to do, thanks.:)