delete current record on close

[Angi]

I have a form that opens with the current CoID in it which
automatically makes it a new record whether the user inputs anything or
not. When the user closes the form, I want to delete the record if the
Last Name field is null but I'm getting the following error:

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

I know it's because of the EditMenu, 8 line, but when I take it out, it
still doesn't delete the record. It doesn't refresh the main form, but
once you close and go back in, a blank entry is there. What do I need
to do to get this code to work?

Private Sub Form_Close()
If (Not IsNull(Me![LastName])) Then
Forms!companymain.Requery
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
End Sub

TIA!

 

[Graham Mandeno]

Hi Angi

Has the new record actually been saved yet? If not, then use Me.Undo in
Form_BeforeUpdate.

If the record has indeed been saved, then try:
DoCmd.RunCommand acCmdDeleteRecord

DoMenuItem is only provided for backward compatibility, as is evidenced by
the tell-tale "acMenuVer70" - you are using the menu from Access version 7
[Access 95]. Unfortunately the code generation "wizards" are a bit
"wizened" and produce very out-of-date and often obsolete code.

 

[Angi]

Graham,
Thanks for the reply. I used the Me.Undo first...it was still giving
me a blank record, so I tried the delete record statement instead. Now
I get the following error:

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

I put it in the On Close, BeforeUpdate and the AfterInsert event (one
at a time). Same error for all. Am I trying to put it in the wrong
place?

 

[Graham Mandeno]

Hi Angi

Basically, you can't delete a record unless it has already been saved. If
you have a new record, and have typed some data into it but not saved it,
then Me.Undo will reverse those changes and leave you with a new *blank*
record which should NOT be saved when you close the form.

I can't see how you are getting a new blank record *saved* in the table,
unless your code is explicitly saving the record. Is this the case? It
seems to me that there is a problem with the way your application is
working, and rather than undoing the problem by deleting the erroneous
record, it would be better to identify and fix the cause of the problem.

Please post a little more about your form, including some code, and I or
someone else may be able to help you further.

 

[Angi]

Graham,
Ok, I tried it again. If I click the Add contact button and close it
right away, typing nothing, I get a blank record with the current CoID.

Here's all the code assoc with the form. Note: I took out all of the
above code since it wasn't working. Thanks for all your help on this!

The Add Contact button:
Private Sub cmdAddContact_Click()
On Error GoTo Err_cmdAddContact_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ContactMainForm"
DoCmd.OpenForm stDocName, , , , acFormAdd, , Me.CoID

Exit_cmdAddContact_Click:
Exit Sub

Err_cmdAddContact_Click:
MsgBox Err.Description
Resume Exit_cmdAddContact_Click

End Sub

The ContactMainForm:
Private Sub Form_Close()
If (Not IsNull(Me![LastName])) Then
Forms!CompanyMain.Requery
Else

End If
End Sub
__________________
Private Sub Form_Open(Cancel As Integer)
If (Not IsNull(Me.OpenArgs)) Then
With Me.RecordsetClone
.AddNew
.Fields!CoID = Me.OpenArgs
.Update
Me.Refresh
Me.Bookmark = .LastModified
End With
End If
End Sub
__________________________________
Private Sub HFax_Exit(Cancel As Integer)
DoCmd.Close acForm, "ContactMainForm", acSavePrompt
End Sub
Private Sub HomeOffice_LostFocus()
If Not IsNull(LastName) Then
If HomeOffice = True Then
GoToPage 2
Else
Me!Salutation.SetFocus
Form_Close
End If
Else
Me!Salutation.SetFocus
End If
End Sub

 

[Graham Mandeno]

Hi again Angi

The problem is in your Form_Open code. By using .AddNew and .Update, you
are explicitly creating and saving a new (almost) empty record, and then
going back to edit it and add the other details.

A much better technique would be to make the CoID passed in OpenArgs the
DefaultValue for the CoID field in the new contact record. That way, the
record will not be created until the form attempts to save it
(Form_BeforeUpdate) at which point you can check all the fields and choose
to do a Me.Undo, or ask for more details, as required. If the user simply
closes the form without entering any data then nothing will happen at all,
because setting the DefaultValue property does not dirty the form.

So, replace the block "With Me.RecordsetClone ... End With" by the following
line:
Me!CoID.DefaultValue = Me.OpenArgs

If CoID is a text field, you will have to add some quotes:
Me!CoID.DefaultValue = """" & Me.OpenArgs & """"

 

[Angi]

Graham,
I think I love you!!! Thank you so very much! Of course, it
worked like a charm! I can breathe now!

Thanks again!!!
Angi