Stop user deleting all records

Robbie

Registered User.
Local time
Today, 23:28
Joined
Feb 10, 2010
Messages
17
Hi! Can anybody help please?

I have a form frmCompany and a subform fsubCompanyRole in an ms access front end. They are bound by tables tblCompany and tblCompanyRole and linked by lngCompanyId in an ms access backend.

I want to stop users deleting all records from fsubCompanyRole for any given company. The logic being that every company has at least one role, i.e; Customer, supplier etc...

I have tried using the BeforeDelConfirm event and canceling the delete if the number of records in tblCompanyRole is 0 for the given company. This won't work because the records I am deleting are not yet deleted when the event fires so there cannot be 0 records!

I then tried using AfterDelConfirm but the records are gone by the time I can check the number of records and I cannot get them back!!

Does anybody know what the best way of approaching this is?
 
Don't you think you should be checking when there is 1 record instead?

Also my advice would be to disable the delete command button when the user goes to that record and it meets the above criteria, and enable it if it doesn't.
 
Hi vbaInet, thanks for replying.

That sounds good but the only problem is that the subform is displayed as a datasheet, which I should have mentioned before! Therefore the user can delete more than one record at a time.

I would prefer to display the form as a datasheet but if it is the only way I can change it to a continuous form and create a delete button...
 
I now understand your predicament.

Have a look at this (Before Delete Confirm event and On Delete event):

Code:
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    DoCmd.SetWarnings True
End Sub

Private Sub Form_Delete(Cancel As Integer)
    If MsgBox("delete?", vbYesNo, "Confirm") = vbYes Then
    
    Else
        Cancel = True
        DoCmd.SetWarnings False
    End If
End Sub
The On Delete event occurs before the Before Delete Confirm event so you can cancel it there. Setting the Warnings to False (in On Delete event) will ensure that the message box asking if the user wants to delete the record doesn't appear. You must set it back to True in the Before Delete Confirm event.

You can even move the Warnings code in the On Delete event outside the IF statement if you never want that message box appearing during a delete. That's up to you.

I hope that helps`
 
I still don't see how I can tell if no records will be left after the delete is executed...

If I look to see how many records are left in the if statement of the on delete event, it will still get the amount of records before the deletions, same as if I do it in the before update statement.

Do you know if there is some way to tell how many records are being deleted? It seems as if there should be because in the warning access tells you that you are deleting X amount of records, I just can't figure out how to get X through VBA! Do you know if this is possible?

Otherwise I think I will just go with your original advise and do it in a continuous form and create a button to delete records. Seems like the easiest way to do it.
 
Otherwise I think I will just go with your original advise and do it in a continuous form and create a button to delete records. Seems like the easiest way to do it.

I think that would be my way to go. :)
 
just set the form to allow deletes = false

job done

=================

what does a role represent? can a company have more than 1 role?
 
If you were to follow gemma-the-husky's advice remember to turn on AllowDeletes.

You can count the selected using Me.SelHeight
 
just set the form to allow deletes = false

job done

That will keep them from deleting ANY of them - they will not be able to do anything with single records which is what I believe the OP wanted.
 
That will keep them from deleting ANY of them - they will not be able to do anything with single records which is what I believe the OP wanted.

I was struggling to see what a role was/represented. I see that the OP wants it be customer/supplier etc. I dont see why you should not be able to delete the role, in that case.

Maybe the only person to delete should be either

a) the original creator or
b) a special designated admin user.

that shouldnt be too hard to arrange.
 
but if you have a group with privilege users modify ,is possible for this group open table and delete value..
 
but if you have privilege users modify is possible open table and delete value..
Which is why you provide a form for data entry and enforce the necessary security measures to disallow entry to the backend for non developers.
 
Which is why you provide a form for data entry and enforce the necessary security measures to disallow entry to the backend for non developers.

AMEN to that! People should NOT be able to get to the tables. Only the developer should have direct access for developing.
 
yes but in a fron end users possible open table and clear all
 
Re-read the title of the thread and what is being asked patratrac.
 
and how is possible set up so they can't get to the tables...?
 
In your situation, from what means can users access the tables?
 
You go to Startup Options and uncheck the USE ACCESS SPECIAL KEYS so that they can't use F11 to get to the database window, you make sure the Show Database Window checkbox is unchecked and you use the Disable Key Bypass code to keep them from being able to hold shift down when they open the database.
 
I still don't see how I can tell if no records will be left after the delete is executed...

If I look to see how many records are left in the if statement of the on delete event, it will still get the amount of records before the deletions, same as if I do it in the before update statement.

Do you know if there is some way to tell how many records are being deleted? It seems as if there should be because in the warning access tells you that you are deleting X amount of records, I just can't figure out how to get X through VBA! Do you know if this is possible?

Otherwise I think I will just go with your original advise and do it in a continuous form and create a button to delete records. Seems like the easiest way to do it.
The following code on your own delete button on a continuous form will display a message box to users telling them how many records are about to be deleted. Replace the names with those of your own Tables etc

Private Sub Cmd11_Click()
On Error GoTo Err_Cmd11_Click
Dim rs As Recordset
Dim Msg, Style, Title, Response, MyString
Set rs = Me.RecordsetClone
With rs
.FindFirst "[fldAudID] = " & txtAuditID.Value ' find the current in recordset
.MoveNext 'move to the next record
If Not .EOF Then
Msg = "If you delete this record the following " & " " & .RecordCount - .AbsolutePosition & " " & "records will also be deleted are you sure you wish to continue?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Multiple Record Deletion!"
Response = MsgBox(Msg, Style, Title) ' if not at end of file
If Response = vbNo Then
Exit Sub
Else: Dim stDocName As String

DoCmd.RunSQL "DELETE tbAccAudits.fldAudId, tbAccAudits.fldAuditDate, tbAccAudits.fldAuditAmnt, tbAccAudits.AccountTypeID, tbAccAudits.CdID FROM tbAccAudits WHERE (((tbAccAudits.fldAudId)>=[Forms]![frmPerAudits]![txtAuditID]) AND ((tbAccAudits.AccountTypeID)=[Forms]![frmPerAudits]![AccountTypeID]));"

DoCmd.Close
End If

Else
Msg = "You are about to delete this record are you sure you wish to continue?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Record Deletion"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Exit Sub
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tbAccAudits.fldAudId, tbAccAudits.fldAuditDate, tbAccAudits.fldAuditAmnt, tbAccAudits.AccountTypeID, tbAccAudits.CdID FROM tbAccAudits WHERE (((tbAccAudits.fldAudId)>=[Forms]![frmPerAudits]![txtAuditID]) AND ((tbAccAudits.AccountTypeID)=[Forms]![frmPerAudits]![AccountTypeID]));"
DoCmd.SetWarnings True
DoCmd.Close
End If
End If
 

Users who are viewing this thread

Back
Top Bottom