Current event not getting fired on subform

[Alex]

I have a form with a subform The form is unbound and it displays a single
record in a table. The subform is in listview mode and I use it to aid in
navigating the records of the main form. On the Current event of the subform I
have the following code.

Private Sub Form_Current()

Me.Parent.RecordSource = "SELECT * FROM T_ProviderTypes WHERE
T_ProviderTypes.ProviderTypeID = " & ProviderTypeID

End Sub

(While the main form and the subform display data that comes from the same
underlying table, the subform uses a query for its record source).

In my main form I use the following code for record deletion:

Private Sub CommandDelete_Click()

If MsgBox("Are you sure you want to delete the record?", vbYesNo) = vbYes
Then
DoRecordDeletion
End If

End Sub

Private Sub DoRecordDeletion()

Dim TheID As Integer
Dim varBookmark As String
Dim rsClone As DAO.Recordset

varBookmark = Me.F_ProviderTypesSubForm.Form.Recordset.Bookmark
Set rsClone = Me.F_ProviderTypesSubForm.Form.RecordsetClone
rsClone.Bookmark = varBookmark
rsClone.MovePrevious
If rsClone.BOF Then
rsClone.MoveFirst
End If
TheID = rsClone.Fields!ProviderTypeID
Me.F_ProviderTypesSubForm.Form.Recordset.Delete
Me.F_ProviderTypesSubForm.Form.Recordset.Requery
Me.F_ProviderTypesSubForm.Form.Recordset.FindFirst ("ProviderTypeID = " &
TheID)
Set rsClone = Nothing

End Sub

I'm finding that the Current event of the subform is not getting fired when I
reposition the record pointer in the subform via its recordset's FindFirst
method (at the end of the DoRecordDeletion routine), and this is causing a
problem as I depend on the Current event to keep both forms synchronized. Does
anybody know why the Current event of the subform is not getting fired?

 

[Allen Browne]

Alex, this looks like a really convoluted way to do this.

You could simplify the whole process down if you used just a main form and
list box.
Initialize the RecordSource of the main form so it has no records:
SELECT * FROM T_ProviderTypes WHERE (False);

In the AfterUpdate event of the list box, assign the record to the form:
Me.RecordSource = "SELECT * FROM T_ProviderTypes WHERE
T_ProviderTypes.ProviderTypeID = " & Me.List0 & ";"

Use the AfterDelConfirm event of the form to Requery the list box.

You're done.

 

[Alex]

Hello Allen,

I tried your suggestion of using a listbox. The record navigation works
correctly, but I still have a problem with record deletion. I tried two
different ways of deleting the record, but I can't get the form's
AfterDelConfirm event to fire with either one:

Private Sub DoRecordDeletion1()

Dim rs As DAO.Recordset

Set rs = Me.Recordset
rs.Delete
Set rs = Nothing

End Sub

I also tried using DoCmd.RunCommand acCmdDeleteRecord, but doing it this way I
got a "No current record" error which I had to trap.

Private Sub DoRecordDeletion2()

On Error GoTo Err_RecordDeletion2

DoCmd.RunCommand acCmdDeleteRecord

Exit_RecordDeletion2:
Exit Sub

Err_RecordDeletion2:

If Err.Number <> 3021 Then ' No current record
MsgBox Err.Description
End If
Resume Exit_RecordDeletion2

End Sub

 

[Alex]

I found the problem with the second version of the code I posted (the code that
uses DoCmd.RunCommand acCmdDeleteRecord). The event was getting fired, but I
was incorrectly doing the requery for the form as opposed to the listbox.