Error on removing last record from subform

[Guest]

I have built a form with multiple subforms that the user can change which
subform a record appears. Using the following code for each subform,in the
afterUpdate event of the combo box ,the user can both enter a new record,
edit and existing record or change the subform the record appears in. I have
set the tab order so the combo box gets focus last in the current record. The
error occurs when I remove the last record from the subform. How do I avoid
an error in this situation.

Me.Requery
DoCmd.RunCommand acCmdRecordsGoToNew
Forms!Form1.[qryPerpPurchasesW subform].Requery
Forms!Form1.[qryPerpOtherAdj subForm].Requery

 

[Allen Browne]

What is the goal here? Do you have 3 subforms all drawing records from the
same table, so that when you insert/edit/delete in one, you need to bring
the other 2 up to date as well?

If that's the idea, you could use the events of the form rather than the
combo. Then if the user moved to a different record by clicking somewhere
else, the code still works.

There is no need to requery Me, since that subform is already up to date, so
just requery the form in the other subforms on the parent form:
Private Sub Form_AfterUpdate()
Me.Parent![qryPerpPurchasesW subform].Form.Requery
Me.Parent![qryPerpOtherAdj subForm].Form.Requery
End Sub

You probably want to do that in the form's AfterDelConfirm event as well:
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call Form_AfterUpdate
End If
End Sub

More importantly, if you have so many fields that they must be spread across
3 subforms, the design might be unnormalized. Not necessarily, but could be
worth considering.