Deleting Record Generates a New One

[Ally]

The world's greatest Delete button ... but not yet

I have done all this but guess I'm doing something wrong as it's still regenerating another record. I tried debugging, but got the error message after DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 telling me:

The command or action "select record" isn't available now.

 

[jjturner]

My bad - put 'booBeingDeleted = True' line right after 'If UserResponse = vbYes Then' on your cmdDelete_Click event. If it gets put after your docmd.DoMenuItem statements, it won't work because Form_Current has already been triggered.

Some more thoughts regarding Record Deletion behavior:

When you open frmTENSEpisode, your openform criteria from frmPatients filters the frmTENSEpisode to a single record. By deleting that record, Access looks for a record to make current after deletion (since your form is of course bound to a recordset) but doesn't find any. Normally, it would make the Next record current on your form, so your filter is part of what makes Access confused (not that that's a 'problem' per se). By making sure no values get written to the 'empty' form, you avoid creating another record.

I think I'll say a prayer for you this time!

Regards,
John

 

[Ally]

Have put the booBeingDeleted = True before the DoCmd.DoMenuItem code and it's working fine.

Thank you very much for all your help and patience (and your prayer)!

Ally

 

[jjturner]

Yeah... a little divine sanction... what can you say. Somehow it makes all this toil and effort over a button seem worthwhile

Glad to see it's working for you now!

 

[Ally]

Resurrected Problem

I'm sorry to have to resurrect this post, but it's started going wrong again. Hadn't used this function for a while as I'd been working on something else, but now it errors, and when I debug, it occurs on the line:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

The error message box says:

The command or action 'SelectRecord' isn't available now ...

Does anyone know why this would be please?

 

[jjturner]

Check the control value assignments

Hello Again Ally,

I know we've grown to love this db

Remember that if anything gets written to the form after the cmdDelete_Click event that it will instantiate a new record (trigger the next autonumber). Put a stop point on the cmdDelete_Click event and step through the code to see if any controls get assigned a value.

Let me know what you find.

Regards,
John

 

[Ally]

Hi John - thank you very much for responding. I'll be so glad when I eventually finish this database.

Stepped through the code and found the following:

vbYes = 6
booBeingDeleted = True
acFormBar = 0
acEditMenu = 1
acMenuVer70 = 70

I did try and amend the code slightly after looking at some other help on the web and tried replacing the two DoCmd.DoMenuItem rows with DoCmd.RunCommand acCmdDeleteRecord. This threw up the same error but the value assigned when I debugged was:

acCmdDeleteRecord = 223

Does any of this mean anything?!

 

[jjturner]

Actually, what I was getting at was to see if any values are being assigned to the controls on your form after you hit the cmdDelete key. This will most likely be on an Event other than the cmdDelete_Click event, so you'll have to step until the cursor takes you to another event, and keep going (i.e. - might take you to the Current Event). Look for control references and whether there is a value assigned to them.

If there are, you need to prevent it by testing it with the booBeingDeleted variable (i.e. - If Not booBeingDeleted Then 'assign the value to the control).

PS - good move to use the Docmd.RunCommand i/o DoMenuItem

 

[Ally]

Sorry - I am a little confused. I had entered a break point. I then looked up "Stop" and realized you may have meant that, but either way, once the code errors, it won't let me step through any further. Am I completely on the wrong track?

 

[jjturner]

My Bad

When you debug, you'll have to Step Over those 2 commands - the docmd's (or just comment them out) so as to prevent the error in the code module. Then see where that takes you.

It is the same problem isn't it - you're getting 'ghost' records?

 

[Ally]

Thanks. I hope this is right.

On going to the frmPatient:

r.EOF = False
IntNewRecord = 0 therefore
recClone = Nothing
intNewRecord = 0
RecordCount = 1
recClone.Bookmark = <No Current Record>

Yes it is the same problem, getting ghost records. Delete one, and another one is generated.

 

[jjturner]

Too hasty in my responses

Touch and go day at the office today, otherwise I would've provided some clearer instructions. I'm afraid I've lead you astray a bit.

Deleting a record triggers the Form_Current event -

Check your Form_Current event code. Is there any new code that's been put before or after your If . . Then . . Else . . End If structure?

If so, look at it to see whether there are any controls getting assigned a value (for instance, Me.fraMachineOutcome.Value = Null). If there are, that is why your Delete button is generating 'ghost' records again.

Any code in your Form_Current event that lies outside the If/Then conditional needs to be cut and pasted into the section that evaluates to true (where the rest of your Form_Current event code resides).

If this check fails (no new code in Form_Current) and you don't get it ironed out, e-mail me the latest version (jrunethorn@hotmail.com) if you can get it under 1 Meg with dummy data.

Cheers,
John

 

[Ally]

Ah yes, there were a few ...=Null in the OnCurrent where I'd copied in the new code I'd put in AfterUpdate of the MachineID field. Knew it sometimes made a difference with Null values in current but thought on this occasion it wasn't ... well it wasn't the normal problem.

Thank you very much John. I hope that's it ...!

 

[Ally]

Aaargh - Sorry!

I know I must get the most points for resurrecting the same post the most no of times, and I'm really sorry to be a real pain, but it's causing me trouble again. I added a small piece of code because I realized that if I deleted a record and the machine hadn't been returned it wouldn't update the machine log. The code added was:

    Select Case Returned
        Case 0
            msgbox "Patient still has machine out on loan.  " _
                & Chr(13) & Chr(10) _
                & "Ensure machine is returned before deleting record." _
                , vbExclamation, "Check"
            Me.chkReturned.SetFocus
            Exit Sub
                            
        Case -1
        UserResponse = msgbox("Are you sure you wish to delete " _
            & "the current record?", vbYesNo, "Delete?") etc etc

When I debug it gets as far as DoCmd.RunCommand ... then goes to the Err Trapping code with the same message as before

The command or action 'Delete Record' is not available now."

Nothing's been added to the OnCurrent event, so I'm not sure why it's doing it.

 

[jjturner]

The gift that keeps on giving . . .

Hmmm. . . Nasty little critter isn't it.

Well, at this point I think it's worth re-considering the value and purpose of your <Delete> button.

From an auditing perspective, for anyone to be examining the integrity of your data and then seeing holes where the Autonumber fields aren't consecutive (because records are deleted) is usually an invitation for closer scrutiny by an auditor. This may not be any kind of concern in your situation (although I should think data pertaining to patients would be likely to fall within the realm of formal audits), but it's worth mentioning.

Secondly, your msgbox prompting the user to return the machine prior to deleting the record strikes me as more than a little schizophrenic (pardon the jest, but I only do so for the purpose of illustration). If the machine was legitimately on loan, then why is the record being deleted? If not, then how did an illegitimate TENSEpisode record get intentionally created (notwithstanding our dear 'ghost' records, of course)?

So, presuming the latter case, I suspect that much of the purpose of your delete button is merely as a means for the user to 'Cancel Out' from actually adding a TENSEpisode record they've begun to create.

Your solution to this would simply be (at least for starters) to set Cancel=True in the Form's BeforeUpdate event if user clicks the henceforth named <Cancel> button. Well, strike the 'simple' qualification - I can see this is gonna have to be thought through more clearly on my part . . .

You may still have some behind-the-scenes writing of values to tables through recordsets, which complicates the matter of Cancelling Out of a record add/update, but this should give you plenty to think about for now.

I'll be interested to hear of your findings or disclaimers, and whether or not my observations were on the mark.

Cheers!
John

PS - an executable command like Delete or Move, etc can't be run while the code module is the active window, which is why debugging won't take you past that line of code.

 

[Ally]

I see your points about auditing and the legitimacy of a record being created but then deleted, but I know that if I don’t put it in, it will be required. I have thought about the AutoNumber field problem before, and it’s not ideal, but I know that we’d get a similar problem if I wrote code to generate numbers. From experience of a previous db I wrote for another department, the delete button is required, but I think what I will do I is hide it for now, unhiding it at a later date should it prove to be required.

Also, after shutting down and re-opening the db, it now works deleting the correct record and running the code. (Plus, I hadn’t realized that the code wouldn’t work in debug mode – thank you for that – you can tell my coding skills are not so hot)!

Thanks again John for all your comments and time.

Ally

 

[jjturner]

Ally,

Glad to see the button healed itself (I used to have a car that did that! )

Further to your comments:

Regarding Autonumber; if you implemented your own serial numbering system via code, I don't see why that should cause problems - especially since this would allow you to 'suspend' the assignment of the number until the user has 'Submitted' or explicitly 'Saved' their new record. (One of the projects I've been given at work forces me to utilize a skeleton db that uses Autonumber and I have to set all controls to Unbound on my form to prevent triggering the # until user 'submits' their log-in - ugh!)

Regarding the purpose of the button; I don't know if you're considering treating it at all as a 'Cancel' button. What I was trying to think of in my prior post was Me.Undo - not Cancel=True, and my guess is you're already familiar with that method.

In any case, you're probably more interested in completing the db at this point than in revising the current structure so I'll sign off - Just thought I'd tie-up my observations, which might save you some grief with future projects.

Regards,
John

 

[Ally]

John - Thank you for your comments. What I meant by same problem, was that if at a later date it had to be deleted, there would still be a gap in the numbering system, but I see your point that implementing my own serial numbering system and "suspending" the assignment would be a much better idea for now and future assignments. I don't really like autonumbers, but have never done this as my coding skills are so hot. I think I have a copy of someone elses db somewhere which might do this, so I'll dig this out and adapt it into this db.