Access wont follow the command GoToRecord,,acNewRec

[Stephenson]

Hello all,



I have a public sub for delete toggles on my forms. I'm having trouble with having the form advance to a new record once the toggle has been clicked. After my MsgBox runs I get a message a runtime error 2105 "You can't go to the specific record." I have tried acNew without any luck. Here is the code I'm using.

Public Sub Delete()
Dim Deletebtn As Integer
Deletebtn = MsgBox("Do you want to delete curernt record?", vbYesNo Or vbQuestion, "DELETE?")
 
    If Deletebtn = vbYes Then
        MsgBox Prompt:="Your record has been deleted", Buttons:=vbOKOnly Or vbInformation, Title:="DELETED"
        DoCmd.GoToRecord , , acNewRec
    ElseIf Deletebtn = vbNo Then
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub

If anyone can see what I'm doing wrong here or maybe there is a bigger issue that's not in this code.

Before anyone asks. I use a toggle for the delete with a filter so that user won't be able to permanently delete any records, but will believe they have.

[June7]

Toggle button is bound to a Yes/No field? Is this sub the form OnDelete event? If not, Delete is a reserved word. Should not use reserved words as names for anything.

What if user responds No - how do you undo the user edit?

[Stephenson]

The toggle button is bound to a Yes/No field. I'm calling this code on the click event for my toggle button, tglDelete. I was unaware that delete is a reserved word and will make the appropriate changes. Thank you for pointing that out.

You make a very good point asking what happens if the user clicks no. I hadn't even thought of that. Would it be possible to link a regular button to a Yes/No field and use similar code?

Thank you for your help June7.

[June7]

Command button cannot be linked to a field - not a data control. Code behind button could set value of field.

[NTC]

a soft delete - where the user makes a delete but the record remains in the database - is typically implemented with a simple yes/no check box field in the table that gets checked to yes (-1) when the user makes the delete (how this appears/displays in the User Interface is up to you - I am just describing what happens to the record in the table)

then thru out the db design it is simple to rely on queries that work with only active non deleted records....plus it is also easy to undelete a record if that is ever needed....

[ItsMe]

Perhaps you are deleting the last record within the Recordset or the filtered recordset. I do not know for sure, but I will guess that using DoCmd to navigate to a new record will not work in that case.

[June7]

I really don't know why the code fails, looks fine to me. If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

Of course, another possibility is that you simply can't add a New Record using that Form...which can be caused by a number of things! Can you, in fact, add a New Record, using the native Navigation Button to do so?

Linq ;0)>

[Stephenson]

Thank you all for your suggestions. I have taken your advice June7 and made this into a button (much smarter). I have used the following code behind it to get it to do the "soft delete" as NTC called it.

Public Sub Dlt(Form)Dim Dltbtn As Integer
Dltbtn = MsgBox("Are you sure you want to delete the curernt record?", vbYesNo Or vbQuestion, "DELETE?")
 
    If Dltbtn = vbYes Then
               MsgBox "Your record has been deleted", vbOKOnly Or vbInformation, "DELETED"
               Form.optDeleted.Value = 1
               DoCmd.GoToRecord , , acNewRec
    ElseIf Dltbtn = vbNo Then
               Form.optDeleted.Value = 0
    End If
        DoCmd.GoToRecord , , acNewRec    
        DoCmd.RunCommand (acCmdRefresh)
End Sub

However if I remove the above code and only use this

Private Sub cmdDltRec_Click()    DoCmd.GoToRecord , , acNewRec
    tboFirstName.SetFocus
End Sub

I get the same results.

Missinglinq, next I tried your idea and you're correct. If I use the native record selector and select go to new record. lets say 45. as soon as I begin typing in the first textbox I get a message that says I'm about to delete 1 record. If I click no on the msgbox it returns me to record 44. If I click yes it returns me to an empty record number 44.

This form is bound to my table, Employees. I have tried switching "Data Entry" in the properties section to both Yes and no with no success. I have also created a new DB and imported all my work with no luck.

This problem is driving me crazy. Any help is greatly appreciated.

[June7]

Provide db for analysis. Follow instructions at bottom of my post.

[Stephenson]

Here is a cut down version of my problems. I'm having the same problem on both the delete and Add Employee button.

Thank you for having a look.

[June7]

Why have DefaultValue of =Null?

I don't allow zero length strings in text fields so I always change property in table to No.

Already on new record row because of the code when form opens. In the Add button, save record first.

If Me.Dirty Then
Me.Dirty = False
DoCmd.GoToRecord , , acNewRec
End If
Me.tboFirstName.SetFocus

If you want to allow users to delete records (which should be a rare event, I seldom grant users this power - why delete employee record?), and you want them to do it with command button, need to handle the keyboard delete button. I use code in form BeforeDelete event to capture this key action.
MsgBox "Must use form Delete button."
Cancel = True

Suggest you get code working behind form before trying to build universal code module. Delete button:

    'Call Dlt(Me)
    If Not Me.NewRecord Then
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.GoToRecord , , acNewRec
    End If
    Me.tboFirstName.SetFocus

Can't use the Me. qualifier in general module, only behind forms and reports.

[Stephenson]

Thanks for the help June7. Being new to this I used the = Null to remove the annoying zeros in my controls. Would I be better off accomplishing this by not allowing zero length strings from my table properties?

I have tried the code

If Me.Dirty Then
Me.Dirty = False
DoCmd.GoToRecord , , acNewRec
tboFirstName.SetFocus
End If

This produces the same results for me. I also attempted

DoCmd.RunCommand (acCmdSave)
If Me.Dirty Then
Me.Dirty = False
DoCmd.GoToRecord , , acNewRec
tboFirstName.SetFocus
End If

This also did not work for me.

There has got to be something I'm doing wrong on my end.

[June7]

What annoying zeros?

The suggested edits work for me.

Sorry, just realized you don't want to delete record, just set the value of Deleted field. So InActive and Deleted are not the same thing?

If Not Me.NewRecord Then
Me.Deleted = True
Me.Requery
DoCmd.GoToRecord , , acNewRec
End If

[Stephenson]

Occasionally there would be a random zero in my textboxes when I would open a form. I had/have no idea why they were there.

I'm going to continue trying the code you wrote and see if I can get it to work.

I tried putting it behind my command button cmdAddRec as,

If Not Me.NewRecord Then        
        DoCmd.GoToRecord , , acNewRec
    End If
    tboFirstName.setFocus

This gives me the same result though. I will continue to tinker with it. Thank you so much for the support.