Deleting Record Generates a New One (1 Viewer)

[Ally]

I have code on a command button to delete a record, generated by the wizard with a bit added to requery the next form that opens. But, when I use it, it then generates a new blank record which I don't want. It was okay at first, but I appear to have done something in the last couple of days that's changed it, but I'm not sure what.

This is the code:

Dim UserResponse As Integer

    DoCmd.SetWarnings False

    UserResponse = msgbox("Are you sure you wish to delete 
the current record?", vbYesNo, "Delete?")
    If UserResponse = vbYes Then
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.Close
        DoCmd.SelectObject acForm, "frmPatient"
        DoCmd.Restore
        Forms![frmPatient].Requery
        
    End If

Does anyone know why this is doing this and how to get over it please?

 

[3699wat]

How do you want the new form to open?

 

[Ally]

Sorry - should have said that the form is already open. It's just been minimized and is now via this code, being restored. Not sure what you mean by how I want the form to open?

 

[3699wat]

The reason for my question was because your post states
" to requery the next form that opens"
Do you want to delete the record, requery and show like the last or first record on the form you have open alreday?

 

[Ally]

The form with the record on that I want to delete is the Episode form. This was opened via the Patient details form and opens the Episode relating to that particular patient. The Patient form has a list box showing the Episode information (for that patient), that I want refreshed / requeried to reflect the changes made in the Episode form, be it information changed or deleted.

 

[3699wat]

Try this code on the On Click event of your command button


Private Sub CommandName_Click()
On Error GoTo Err_CommandName_Click

DOCMD.SETWARNINGS FALSE

UserResponse = msgbox("Are you sure you wish to delete
the current record?", vbYesNo, "Delete?")
If UserResponse = vbYes Then

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.ListName.Requery

End if

Exit_CommandName_Click:
DOCMD.SETWARNINGS TRUE '<---by placing this here, you're guaranteed to get your regular warnings turned back on.
Exit Sub

Err_CommandName_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_CommandName_Click
End Sub

 

[Ally]

Sorry, but apart from the DoCmd.SetWarnings True being put back in a different place, a bit in the error trapping code, and Me.ListName.Requery this is the same as my code and does the same thing. The requerying of the list box would have to remain as "Forms![ ... " as it's on a different form.

I can try and reduce down my db and attach it if it will help?

 

[Ally]

Got to go out so attached it anyway.

Open frmPatient and double click on any of the episodes on the list box. The episode form will open. Try deleting and a new record will automatically be added after deletion.

 

[3699wat]

I got the copy of your db. I had a quick look and will check it in more detail later on (gotta go to a meeting). One thing I noticed is that your tables (and subsequent query) have a lot of default values. This might be a problem: when you requery it updates the underlying query / table, but at the same time creates a new record. Something to look into?

 

[Ally]

The reason I had to put in default values was because of the referential integrity - I was getting errors all over the place - see this old post: Previous Post

 

[Ally]

I still haven't managed to get this sorted - has anyone any ideas please?

 

[jjturner]

After reading through your earlier post on RI, and on a hunch from Pat's comments, I deleted your controls out from the 'Outcomes & FUs' tab. After that, DELETE-ing worked - it did not create a new record in tblTENSEpisode. Seems like your TENS Episode form was still re-SAVE-ing based on the 2nd tab's control values (or lack thereof) - or something like that (you'll probably need to follow through on Pat's comments with respect to all this, making sure your main form controls are somehow defined to prevent over-SAVE-ing of your records).

 

[jjturner]

Just a follow-up to my observations - you might be able to get around the inadvertent new record creation and still preserve your controls on the 2nd tab:

Set your 2nd tab controls' ControlSource's to "" until that tab gets the focus. In other words, have your controls on that tab unbound until you need to access them, then unbound them again when that tab loses the focus. I haven't had a chance to test my theory out, but it seems feasible. Kind of like 'if a tree falls in the woods and no one hears it, does it make a sound?'.

Regards,
John

 

[3699wat]

John
I hope you can solve this. I did about the same and deleted the command buttons and refreshed the form with new ones. It worked for a while, but then the problem started again.
Most of my similar setup work well though, but none of them have default settings for a field. I did that with some of them in the past and at always created a new record after deleting another. That's why I mentioned that in aprevious post.

 

[jjturner]

Ally, this works with the following code on your 'cmdDelete_Click' event:

    If UserResponse = vbYes Then
        'set all controls whose fields generate default values to 'Unbound'
        Me.fraMachineOutcome.ControlSource = ""
        Me.PtInfoLeaf.ControlSource = ""
        Me.UnitNo.ControlSource = ""
        Me.ElectrodeID.ControlSource = ""
        Me.fraClinicPhone.ControlSource = ""
        Me.RefSourceID.ControlSource = ""
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.Close
        DoCmd.SelectObject acForm, "frmPatient"
        DoCmd.Restore
        Forms![frmPatient].Requery
    End If

Regards,
John

 

[jjturner]

One last note (I think) - you don't really have to 'Requery' your frmPatient, or the listbox specifically, to update the Episode count - just 'Refresh' it.

 

[Ally]

Wow - thanks for all your responses.

I have done as you said, re setting the Control Sources to "". It does appear as if it deletes okay without regenerating another record because the one you're deleting disappears from the Episode listbox on the patient form and doesn't add one there, but my pc is a bit slow and when it deletes from the Episode form, you can see another number generate in the EpisodeID field but doesn't allocate it anywhere - I've looked in the Episode table, but it's not there. When you do click "Add New Episode" from the Patient form, it is then +1 from the one that I saw regenerated. Hope this makes sense!

 

[jjturner]

OK,

I put a stop point on your Form_Current event and saw that it is triggered right before your DoCmd.Close statement on the cmdDelete_Click event.

Patch-over solution: Create a Class-level boolean (e.g. -booBeingDeleted). Assign it to True prior to DoCmd.Close on the cmdDeleted_Click event. Then test for it on the Form_Current event - If Not booBeingDeleted, Then run your Form_Current code, Else bypass that code and set booBeingDeleted to False - because for some reason, I think when you assign Null to ReturnDt, that is what triggers another record creation.

Regards,
John

 

[Ally]

Thank you but can I ask for a bit of clarification please?

I have declared a boolean in the code for cmdDelete and set it to True before the DoCmd.Close.

When you say run the Current code, do I put it on the OnCurrent event or on the cmdDelete_Click event? If I put it on the OnCurrent, I will have to declare it again.

Also, is this still with keeping the ControlSources = "" or resetting them back to bound fields?

 

[jjturner]

Declare your boolean at the top of your Form's module (at the Class-level as opposed to the Procedure-level), before any procedures. That way, the variable stays visible to all the Events/Procedures in your module until your form is closed. You have to be careful assigning values to it since it can hold a value for as long as the form stays open, even if you move to a different record on that form.

So, once you've declared it up top, there are 2 more steps to getting it working:

1) Set it to True in the cmdDelete_Click event (prior to docmd.Close)

2) Check to see if it's been set to True on the Form_Current event

If Not booBeingDeleted Then
    'Run all the code that currently exists in your Form_Current event
Else
    'bypass your normal Form_Current event code and set 
    booBeingDeleted = False
End If

Now, realizing that your assignment of ReturnDt to Null on the Form_Current event is what triggers another record creation, I commented out the setting of your ControlSources to "" and your Delete button still worked fine. The ControlSource solution was a bit overkill.

After all this, you're gonna have the world's greatest Delete button