Delete the last record (1 Viewer)

[John Sh]

I have a delete button that effectively deletes the current record unless it is the last record. In this case a new record is created.
How can I prevent this new record being created. Once it's there I can't get rid of it because there are required fields that are empty.
The "if newrecord" construct just causes an error message indicating certain fields cannot be empty.
If I close and reopen the form the "new record" is no longer there.

Private Sub btnDelete_Click()
    DoCmd.SetWarnings False
    RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
    If Me.NewRecord Then
        DoCmd.GoToRecord , , acPrevious
    End If
End Sub

 

[CJ_London]

think you need to explain exactly what it is you are trying to do as your code makes little sense

 

[John Sh]

think you need to explain exactly what it is you are trying to do as your code makes little sense

Not quite sure what else you want to know, CJ.
The code under the button deletes a record, "RunCommand acCmdDeleteRecord". If that record is the last record in the table a new record is created. I do not want the new record. How do i stop it from being created.
Which part of the code do you not understand?
set the warnings off
delete the current record
set the warnings back on
if there's a new record go back one.
Seems pretty straight forward to me!

 

[CJ_London]

Which part of the code do you not understand?

I am perfectly familiar with all the commands you are using, just not the way you are using them,

where is the button? on a single form? a continuous form? in the header, detail or footer section? how are you determining which record has the focus to be deleted? So what are you trying to do? and why? Perhaps you should be using undo/cancel instead of delete? perhaps the form should not allow additions? who knows if you can't tell us?

best guess is you are trying to delete the record that you are currently on - but if you are on a new record then it hasn't been created so can't be deleted - but the act of clicking the button means you leave that record and move to a new one so it is saved but is no longer the current record.

 

[John Sh]

I am perfectly familiar with all the commands you are using, just not the way you are using them,

where is the button? on a single form? a continuous form? in the header, detail or footer section? how are you determining which record has the focus to be deleted? So what are you trying to do? and why? Perhaps you should be using undo/cancel instead of delete? perhaps the form should not allow additions? who knows if you can't tell us?

best guess is you are trying to delete the record that you are currently on - but if you are on a new record then it hasn't been created so can't be deleted - but the act of clicking the button means you leave that record and move to a new one so it is saved but is no longer the current record.

I have a delete button that effectively deletes the CURRENT record.
The button is in the header of a single form. As such there is only one record, the current record, visible. This is the one I want to delete.
I am deleting it because it is no longer required and has no historical value. It is totally redundant.
The form MUST allow additions. If not it could not grow, ie take on more information.
I am not on a new record when I click the button. I am on the last, previously saved, record that exists on the current table.
Now the guts of my question.


When I click the delete button in the header of my single form, this pre-existing last record on the table, that is the record currently displayed on my single form, that record is deleted and a new record appears.
How do I either stop this new record from being created or move away from it without it becoming part of the table that currently has focus.

In other words:
I have a table with 10 records.
I need to delete record number 10.
I make record number 10 visible on my single form and click the delete button in the header.
When I do this, record number 10 is deleted but a new record number 10 is created.
I want my table to now have a total of 9 records

Alternatively,
I have a table with 10 records.
I need to delete record number 7.
I make record number 7 the current record. Ie. visible on my single form and click the delete button in the header.
Record number 7 is deleted.
I now have 9 records in my table

I think my command of the English language is more than adequate to describe most situations. If, however, the above does not adequately describe my current predicament, I admit to being at a total loss for words!

 

[CJ_London]

OK - I've just created a test form, single with header, put a button in the header and copy/pasted your code - it works as you intended. so that says to me you have some other code in play. See attached

Please note I'm going offline now

 

[John Sh]

OK - I've just created a test form, single with header, put a button in the header and copy/pasted your code - it works as you intended. so that says to me you have some other code in play. See attached

Please note I'm going offline now

I've had a play with your form, added a "golast" button and modified the table to replicate the conditions in my table and it continues to work as expected. I do have one field that is required, no zero length, and indexed no duplicates. This seems to be the culprit but adding a similar field to your table had no effect. Removing the "if me.newrecord" construct did leave a new record open when deleting the last record.

I have taken the rather extreme measure of closing and reopening my form, code below. This has solved the problem but its a bit like using a steam hammer to open a bottle.

Private Sub btnDelete_Click()
    DoCmd.SetWarnings False
        RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
    If Me.NewRecord Then
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "Main Collection"
        DoCmd.GoToRecord , , acLast
    End If
End Sub

 

[CJ_London]

when a record is deleted it will go to the next record which may be a 'new record' but that does not mean it is created unless you add something to the record. So perhaps you have something in your current event which causes the record to actually be created, like code to populate a field. If so, try moving it to the form before update event.

 

[John Sh]

when a record is deleted it will go to the next record which may be a 'new record' but that does not mean it is created unless you add something to the record. So perhaps you have something in your current event which causes the record to actually be created, like code to populate a field. If so, try moving it to the form before update event.

Thanks for your assistance with this. It turns out there were some default values set in the table design. I have removed them and it's all good.