How to prevent accidental deletion of record in subform

[Rodney Roe]

Hello, I'm using Access 2013

I have an unbound sub form that I'm populating with a SQL query in VBA by creating a querydef and passing it to the sub form source control. The sub form is shown in datasheet view. I had a user accidentally delete a record through the sub form, and I want to make sure that doesn't happen again. I tried setting the allow deletions property to "no" but that property doesn't show up in the sub form properties. It does show up in the main form but if I set that to "no" they can still delete a record through the sub form even though the main form is locked. I don't want to lock the sub form because people still need to edit values in the sub form, but I want to eliminate the chance of deleting any records with it.

Is there something I'm overlooking? I've tried searching the internet and have not had any luck finding anything.

Any help would be appreciated.
Thanks,
Rodney

[NeoPa]

Each form has an AllowDeletions property. If you set that to True for the form that you're using as a Sub-form then it should disallow deletions while allowing edits.

Certain properties get reset by other actions, like setting the RecordSource property and by opening the form with particular parameters, so check the value of the property once the form is running. If it gets itself reset then add some code to set it to True when it's run.

[Rodney Roe]

NeoPa thank you for the quick response.

Shouldn't I set AllowDeletions to False if I don't want them to delete fields? Also, I have a combo box selector that changes the RecordSource from table to table, I added a chunk of code to set the Allow Deletions property to false every time the RecordSourse changes, but to no avail if I select a record in the subform and press delete it get's deleted.

I've tried setting the AllowDeletions in the form to true, and false, but it didn't make a difference.

Not sure if this makes any difference but as for properties this is what I've got on the main form, and none of these properties show up on the sub form.

Data Entry - set to yes (I only want them do add new data in the main form.)
Allow Additions - set to yes
Allow Deletions - set to no
Allow Edits - set to yes
Allow Filters - set to no

Thanks,
Rodney

[zmbd]

This is part of my code used to setup forms.
In this case, a supervisor for a related table to allow editing of a record; however, I don't want additions nor deletions directly to the child records as these need to be recorded/validated for auditing reasons:

Case "zusermodesupervisor"
        With Me
            .AllowAdditions = False
[iCODE]            .AllowDeletions = False[/iCODE]
            .AllowEdits = True
            .Tag = 1
            .z_ctrl_lbl_usermessage.Caption = "To add records use the controls below. You may Edit current records."
        End With

[Rodney Roe]

ok, maybe I just need to show what I've got code wise

Private Sub cboGetTables_AfterUpdate()

    Dim strQuery As String
    Dim qry As QueryDef
    
    'check if MasterQ exist if it does delete it
    For Each qry In CurrentDb.QueryDefs
        If qry.Name = "MasterQ" Then CurrentDb.QueryDefs.Delete "MasterQ"
    Next
    
    'choose query depending on user perfered sort order
    If Me.chbSortDesc Then
        strQuery = "SELECT T.PartNum, T.PrevPartNum, T.Description, T.Initials, T.DateIssued, T.DescLen, T.ECN, T.EngPrefVend, T.EngPrefVendNum, T.EngPrefMfg, T.EngPrefMfgNum FROM [" & Me.cboGetTables.Column(0) & "] as T WHERE (((t.PartNum) Like IIf([Forms]![Item Draw]![txtfPartNum] <> """", [Forms]![Item Draw]![txtfPartNum], ""*"")) And ((t.Description) Like IIf([Forms]![Item Draw]![fDesc] <> """", [Forms]![Item Draw]![fDesc], ""*"")) And ((t.Initials) Like IIf([Forms]![Item Draw]![txtInitials] <> """", [Forms]![Item Draw]![txtInitials], ""*"")) And ((t.DateIssued) >= IIf([Forms]![Item Draw]![DateIssued1] <> """", [Forms]![Item Draw]![DateIssued1], #1/1/1980#))) ORDER BY T.[" & Left(Me.cboGetTables.Column(0), 2) & "PartID]DESC;"
    Else
        strQuery = "SELECT T.PartNum, T.PrevPartNum, T.Description, T.Initials, T.DateIssued, T.DescLen, T.ECN, T.EngPrefVend, T.EngPrefVendNum, T.EngPrefMfg, T.EngPrefMfgNum FROM [" & Me.cboGetTables.Column(0) & "] as T WHERE (((t.PartNum) Like IIf([Forms]![Item Draw]![txtfPartNum] <> """", [Forms]![Item Draw]![txtfPartNum], ""*"")) And ((t.Description) Like IIf([Forms]![Item Draw]![fDesc] <> """", [Forms]![Item Draw]![fDesc], ""*"")) And ((t.Initials) Like IIf([Forms]![Item Draw]![txtInitials] <> """", [Forms]![Item Draw]![txtInitials], ""*"")) And ((t.DateIssued) >= IIf([Forms]![Item Draw]![DateIssued1] <> """", [Forms]![Item Draw]![DateIssued1], #1/1/1980#))) ORDER BY T.[" & Left(Me.cboGetTables.Column(0), 2) & "PartID]Asc;"
    End If
    
    CurrentDb.CreateQueryDef "MasterQ", strQuery
    
    'chage form record source to match what was selected in the combo box
    On Error Resume Next
    Forms![Item Draw].RecordSource = Me.cboGetTables.Column(0)
    If Err Then
        MsgBox "Sorry, that table doesn't exist in the database yet.", vbOKOnly, "Error, Table not found"
        Exit Sub
    End If
    
    'set new query as source object in Subform, and prevend deletion of records
    Me.Item_Draw_subform.SourceObject = "Query.MasterQ"
    Me.AllowDeletions = False
    
End Sub

This is what I've got coded when the query changes in the sub form. Even though I've got the allow deletions set to false I can still delete records in the sub form if I select the left side and press delete.

Thanks, guys for you help.

[zmbd]

it appears that this code is on the parent form?

As NeoPa said in his post, this appears to be an issue with the subform itself.
+ Open your subform directly in design view
+ What is the property in the subform set to when you do this?

[jforbes]

You could try this for Line 30:

    Me.Item_Draw_subform.Form.AllowDeletions = False

It sets the AllowDeletions on the SubForm instead of the Main Form.

[Rodney Roe]

Sweet, jforbes that worked!!!! :-)

Thanks NeoPa and zmbd I did open the subform in design view and made sure that the allow deletion property was set to "no", but I was still able to delete a record.

[NeoPa]

Hi Rodney.

You're quite right. I should have said to set it to False rather than True. That said, it is the form you use as a subform that you want to set it for. Not the main form nor the SubForm object itself.

I suspect that the fix to your code would be to replace lines #28 through #30 with :

    'set new query as source object in Subform, and prevent deletion of records
    With Me.Item_Draw_subform
        .SourceObject = "Query.MasterQ"
        .AllowDeletions = False
    End With

[Rodney Roe]

That helps, I was just confused because the subform object didn't have the property. so I didn't think to physically change the subform property because I didn't think it existed.

Thanks again all,

[zmbd]

Instead of setting that in the Parent form I set the values directly in the form I am using as the subform. Typically in the on_open event; thus, the "me" holds true whenever the form is opened - ether directly or as subform.