me.undo parent if child is empty

[Angi]

Only programmers could look at that subject and know what it means! <g>

I have a form (QuoteMain) with a subform (subQuoteDetails). I'm trying
to undo the parent form if the form is closed with no records in the
sub. I've done some research and found that the parent saves as soon
as the user tabs into the subform, but I didn't find how to get rid of
it. I've tried this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.DetailsSub.Form.RecordsetClone Is Empty Then
Me.Undo
End If

End Sub

Didn't work, of course. Should I be using the before_insert instead or
what? TIA!

 

[Alex White MCDBA MCSE]

Hi,

2 ways to solve this problem, make the forms unbound so that no default
record saving goes on and you write your own function to grab the values off
the form into the database, sometimes I use the tag property of textboxes to
load previous values to allow for undoing on the form.

using dao/ado to copy/clone the recordset so that things can be undone. If
the parent record is being written for the first time (new record), then why
not use dao/ado to just delete the record if there is no child record.

 

[Steve Schapel]

Angi,

The Before Update event is not appropriate, but neither is Before
Insert. Both of these events have already happened. I suggest the
Unload event of the QuoteMain form would be the one. And Undo won't
work either... once again, it's too late. I would do it like this...
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

 

[Angi]

Alex and Steve,
Thank you for the replies! Ok, I tried combining both of your
suggestions, and now get the error:

Run-time error '2046':
The command or action "DeleteRecord" isn't available now.

Which would tell me that the record isn't being saved yet. Which will
be possible if the user hasn't tabbed into the subform yet, but I tried
it with going into the subform and I still get the error. Another
thing, in this code, aren't I saying to delete the subform's record?
I'm still learning how to use recordsets so, please, be patient.
Thanks!

Here's my new code:
Private Sub Form_Unload(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

Set rst = Forms!zquotemain1!DetailsSub.Form.RecordsetClone

If rst.RecordCount = 0 Then
'DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
'DoCmd.SetWarnings True

End If

End Sub

 

[Alex White MCDBA MCSE]

Hi Angi,

this should help

change your code to the following

Private Sub Form_Unload(Cancel As Integer)
on error goto Err_Form_Unload
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = Forms!zquotemain1!DetailsSub.Form.RecordsetClone

If rst.RecordCount = 0 Then
'DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
'DoCmd.SetWarnings True
End If
Exit_Form_Unload:
exit sub
Err_Form_Unload:
if err.number <> 2046 then
msgbox err.number & " " & err.description
end if
resume next
End Sub

this allows your code to silently trap the error and ignore it, should do
the job.

 

[Steve Schapel]

Angi,

Have you put this code on the Unload event of the subform? I meant the
main form.

Another minor point... I would do it like this:
Set rst = Me.DetailsSub.Form.RecordsetClone

 

[Angi]

Steve,
Ok, did what you said and it does ignore the 2046, but now I'm getting
an error

3021 No current record

I tried ignoring that one too, but it's not working. Now what?

 

[Angi]

Sorry about that, I meant Alex.

Steve,
Yes it is in the Unload event of the main form....not the subform. I
changed it to the me. I did that before and it didn't like
it...must've been some other error going on at the time. Thanks for
that!

 

[Angi]

Anyone have any ideas on how to now get rid of the 3021 error?

I have:
Exit_Form_Unload:
Exit Sub
Err_Form_Unload:
If Err.Number <> 2046 Or Err.Number <> 3021 Then
MsgBox Err.Number & " " & Err.Description
End If
Resume Next

 

[Douglas J. Steele]

If Err.Number <> 2046 And Err.Number <> 3021 Then

The way you have it, if Err.Number is 3021, then the first part of the
comparison (Err.Number <> 2046) is obviously True.

 

[Alex White MCDBA MCSE]

Angi,

To follow Doug,

You Need

either

if err.number = 2046 then
resume next ' or whatever you want to do
elseif err.number = 3021 then
resume next ' or whatever you want to do
endif

or the select case

select case err.number
case 2046
resume next
case 3021
resume next
case else
end select