problem deleting the LAST record in a single form

[markjkubicki]

I have a single form, that allows additions, deletions, edits, <etc.> F


rom it, I can delete any record at any given time...
It's not a problem with any of the other records... just the last one; and the real problem is I have no idea where to look (or what to look for);
it's one of those things that wasn't before, and sometime between "now" and "then" it must have started...


the new problem:
When I delete the last record, it creates / goes to a new record, rather than navigating to the next to last record (previous record).
The new record is null; and because (?) one of the fields is required, I first get a warning that says: "can't go to the specified record", and then the null record is displayed.
The user is forced to enter a "junk" value (in the required field) in order to navigate away from the record (... and then to delete that bogus record, they have to have entered a value that would have put the record somewhere (not last), navigate to it, then delete it).
(this is not good)

this is the code I use to delete records:

Private Sub cmdDelType_Click()
On Error GoTo Err_cmdDelType_Click
    
    'delete the record and move to the previuos record
    DoCmd.SetWarnings True
    strPrompt = "Confirm that you want to delete this fixture type..."
    strTitle = "DELETE FIXTURE TYPE"
    Response = MsgBox(strPrompt, vbYesNo + vbExclamation, strTitle)
    If Response = vbYes Then
        If Me.NewRecord Then
            DoCmd.GoToRecord , , acPrevious
        Else
            DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
            DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
            If Me.NewRecord Then
                DoCmd.GoToRecord , , acPrevious
            End If
        End If
    End If
Exit_cmdDelType_Click:
    Exit Sub
Err_cmdDelType_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelType_Click
End Sub

any suggestions will be greatly appreciated in advance,
Mark

[ranman256]

Normally you can go to a new record and not get an error because you didn't enter any values. But it could give an error if you have defaults and the user presses save.
So,
In the form properties, you could turn off allow additions and it won't go to a new rec
or
change the cycle records to single, so it won't go to the next record.

[markjkubicki]

unfortunately, the user does need to be able to add new records

[rpeare]

I made this table:

PK	TestField1	TestField2	TestField3
6	TEst1	5	1 /1 /2015
7	test2	10	1 /2 /2015
10	test3	15	1 /3 /2015
11	test4	20	1 /4 /2015
12	test5	25	1 /5 /2015
13	test6	30	1 /6 /2015
14	test7	35	1 /7 /2015

I created a bound form (which is what I assume you're using) that was ordered by the PK

This is the code I used:

Private Sub cmdDelType_Click()
Dim lCurrPK As Long
Dim lNextPK As Long
Dim db As Database

On Error GoTo Err_cmdDelType_Click

'delete the record and move to the previuos record
If IsNull(PK) Then
    MsgBox "No record chosen to delete"
Else
    DoCmd.SetWarnings True
    strPrompt = "Confirm that you want to delete this fixture type..."
    strTitle = "DELETE FIXTURE TYPE"
    Response = MsgBox(strPrompt, vbYesNo + vbExclamation, strTitle)
    If Response = vbYes Then
        lCurrPK = PK
        If DCount("*", "tblTest", "[PK] < " & lCurrPK) = 0 Then
            lNextPK = DMin("[PK]", "tblTest", "[PK] > " & lCurrPK)
        Else
            lNextPK = DMax("[PK]", "tblTest", "[PK] < " & lCurrPK)
        End If
        Set db = CurrentDb
        db.Execute ("DELETE * FROM tblTest WHERE [PK] = " & lCurrPK)
        Set db = Nothing
        Me.Refresh
        Me.Requery
        Me.RecordsetClone.FindFirst "[PK] = " & lNextPK
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
End If
Exit_cmdDelType_Click:
    Exit Sub
Err_cmdDelType_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelType_Click
End Sub

I do not favor menu items because they may not work as you move from version to version of access, you also forgot to account for whether you were deleting the 'first' record in which case a move previous would bomb out.

[ranman256]

This could be the problem. You dont need code to delete a record, just a single delete query.
build a delete query that looks at the key your are on. 1 simple command:
docmd.openquery "qdDel1Rec"


query sql =
delete * from table where [pk] = forms!frmEdit!pkKey

[markjkubicki]

thanks all for the help;
unfortunately, I've got to put this task on hold for a short bit;
as soon as I return to it, I'll update the post...

CIAO !
mark