How to update a subform after performing a DELETE query?

Question

I have a form, myForm, that includes a subform, mySubform. The records in mySubform have a many to one relationship with the record source of the myForm, as expected, and There is a combobox in mySubform, myCombo, whose values is linked to one of the columns of the record source of mySubform.

I have been having difficulty to delete a record in mySubform, by erasing the current value in myCombo. I have put the code below under the OnChange event of myCombo (after trying many different alternatives).

Please not that I have simplified the delete query and the actual one works fine in the back-end (SQL Server). However, after performing the delete operation, I get this error when the execution reaches Me.Requery:

Error 3162 You tried to assign a Null value to a variable that is not a Variant data type.

Also, after having the degugger skip the Me.Requery and going back to the form, the deleted record shows "#DELETED" in every combobox and textbox for that particular record, and mySubform is not any good beyond that point.

I have looked up very many resources and tried different statements instead of Me.Requery, but none has worked so far. Help is much appreciated.

Private Sub myCombo_Change()
If IsNull(Me.myCombo.Text) Or Me.myCombo.Text = vbNullString Then
    Dim strSql As String
    strSql = "DELETE FROM mySubformRecordSource WHERE PrimaryKeyColumn= " & theCurrentPrimaryKeyValueForTheValueIn_MyCombo
    CurrentDb.Execute strSql
    Me.Requery        
End If
End Sub

Can you list all of the other events on that form that runs code? Such as AfterUpdate, OnCurrent, etc. — Newd, Jul 15 '15 at 14:24
I have commented out all the code for all the other events (OnUpdate was the only one having code). So when I run this, the OnChange is pretty much the only event that has any code running. — Kambiz, Jul 15 '15 at 14:52
What is `theCurrentPrimaryKeyValueForTheValueIn_MyCombo`? Is it meant to be a control, a function? Is it different to `Me.myCombo`? — Matt Hall, Jul 15 '15 at 16:25
Also, if you want to delete a particular record in your subform, why not use a button with that delete procedure and do away with the combo box and its OnChange event altogether? — Matt Hall, Jul 15 '15 at 16:31
Sorry guys, I was a bit sick for a couple of days. So here's the answers: Newd: There area a couple of statements related to the comboboxes in myForm itself (in the Load event), but nothing related to mySubform. — Kambiz, Jul 18 '15 at 01:18
Matt Hall: theCurrentPrimaryKeyValueforTheValueIn_Mycombo is just my description of what the primary key would be for the current row in the subform. I did not include the original code for the sake of simplicity. The other way that I could demonstrate it is as: strSql = "DELETE FROM mySubformRecordSource WHERE PrimaryKeyColumn= " & [PrimaryKeyColumn] — Kambiz, Jul 18 '15 at 01:18
Matt Hall: Your second question: My end users will be modifying the content of the comboboxes. They might just try deleting stuff, and I dont want the program to crash when they do so, instead, I want the code do what they are intending, which is deleting a row. — Kambiz, Jul 18 '15 at 01:18

rfb · Answer 1 · 2015-07-20T15:43:14.433

Not entering on the choices of used cursors etc I think that the solution could be simply "requery" the parent form called by the code of the subform.

But i also have found similar situation in past projects that i made (sorry not beeing exaustive, i'm not using VBA anymore) and the requery command was done on subform but from the main form code, something like this:

If local_procedures.deleteContratto(anagrafica_id_par, servizio_id_par) = OK Then
    Me.subfrm_contratti_frm_anagrafica.Requery
    ...

In this case you could delegate parent form to call delete procedure and update logic (.Requery) of the child form, by mean of an event raised from subForm listen on parent for example.

For the sake of completeness i started hinting about cursors because i think it's the underlying reason for these kind of sync failures between recordsets, the parent form one and the child form one. There are many advices about using dbSeeChanges with db.Execute on SQL Server backend and the resulting default cursors.

UPDATE

Here's a code snippet where a custom event is raised from child to be caught from parent that is delegated to execute code regarding the child.

This pattern doesn't break the parent-child/one-to-many logic, assuming parent has more decision power than child.

' in mySubform
' assuming theCurrentPrimaryKeyValueForTheValueIn_MyCombo is a Long ID
Public Event DeleteRequest(record_ID As Long)

Private Sub myCombo_Change()
    If IsNull(Me.myCombo.Text) Or Me.myCombo.Text = vbNullString Then
        RaiseEvent DeleteRequest(theCurrentPrimaryKeyValueForTheValueIn_MyCombo)   
    End If
End Sub

' in myForm
Private WithEvents frmMySubform As Form__mySubform
' you could have more instances of mySubform concerning different issues or subrecords and manage each one correctly ...
Private WithEvents frmMySubform2nd As Form__mySubform

Private Sub frmMySubform_DeleteRequest(record_ID As Long)

    Dim strSql As String
    strSql = "DELETE FROM mySubformRecordSource WHERE PrimaryKeyColumn= " & record_ID
    CurrentDb.Execute strSql

    With Me.frmMySubform.Form
        .Requery
        ' ...
    end With 

End Sub

Private Sub frmMySubform2nd_DeleteRequest(record_ID As Long)

    Dim strSql As String
    strSql = "DELETE FROM my2ndSubformRecordSource WHERE PrimaryKeyColumn= " & record_ID
    CurrentDb.Execute strSql

    With Me.frmMySubform2nd.Form
        .Requery
        ' ...
    end With    

End Sub

I actually created a public function in the parent form to be called from the subform On_Change event, and got the same error (3162) when that function was called. — Kambiz, Jul 18 '15 at 02:46
It's not the correct way because you are imposing synchronous execution an the case is similar to the original where you manage only the requery from subForm. You should raise a custom event from child caught from parent that executes your code. This is the best pattern in my opinion because you are not breaking the parent-child/one-to-many logic, assuming parent has more decision power than child. I can give you code snippet if you need it. — rfb, Jul 19 '15 at 07:46
Try also calling `requery` of subForm from parentForm. It should be ok — rfb, Jul 19 '15 at 07:48
Would appreciate if you give me the code snippet. Even though my form and subform work right now (with the me.dirty explained above), your solution sounds more elegant, and curious to learn about it. — Kambiz, Jul 19 '15 at 16:30
@Kambiz The pattern that i suggested deals with the potential source of "notification conflict", when parent is informed on a change of child, the deletion of a record that, actually, is correctly treated by parent showing "#DELETED". There are no conflicts indeed but only a difficulty to manage user interface updates. In the suggested pattern the notification is managed manually, not regarding recordset changes, that is postponed. There is only one observer, removing timing and conflict issues. The child form could, even, have read only setting on his recordsource. — rfb, Jul 24 '15 at 07:24

score 1 · Answer 2 · edited May 23 '17 at 12:14

I solved the problem by putting Me.Dirty = False after reading this discussion: Editing Record issues in Access / SQL (Write Conflict)

Private Sub myCombo_Change()
If IsNull(Me.myCombo.Text) Or Me.myCombo.Text = vbNullString Then
    Me.Dirty = False
    Dim strSql As String
    strSql = "DELETE FROM mySubformRecordSource WHERE PrimaryKeyColumn= " & theCurrentPrimaryKeyValueForTheValueIn_MyCombo
    CurrentDb.Execute strSql
    Me.Requery        
End If
End Sub

The form and subform work flawlessly now, but I still dont quite understand the complete logic behind the solution. It has something to do with Me.Dirty=False saving the changes to the content of a non-nullable (or nullable, both work fine) column before programmatically delete the entire record. How exactly though, I dont know and will appreciate input.

The workaround issues change notifications regarding two observers that arise on child recordset change. The two observers of the notification are child form and parent form. The `Me.Dirty` workaround switchs off the notification of child recordset change and postpone the user interface updates with the manual `Me.Requery` call. See also new comment on my answer. — rfb, Jul 24 '15 at 07:19

How to update a subform after performing a DELETE query?

2 Answers2

UPDATE