New Record Command working in admin mode but not normal mode when If statement present

Question

I am at my wit's end. This seems like it should be the most straightforward thing in the world. I have a button on a form that should start a new record when pressed. Here is the code in the button

Private Sub NewLocProLink_Click()

    mbResult = MsgBox("Are you sure you would like to add a new provider to 
    location relationship?", vbYesNo, "New location to provider relationship")

    If mbResult = vbYes Then
        DoCmd.GoToRecord , "", acNewRec
    ElseIf mbResult = vbNo Then
        MsgBox ("No new record")
    End If

End Sub

This code does not work. When I click "Yes" it just seems like nothing is happening. However, when I click "No" I do receive my test MsgBox that says "No new record". So I know that the code enters my If statement.

Now here is the weird part. When I access the DB in admin mode (by holding down shift when I enter) and test the button out, it works. But then when I close the DB and go back in without holding down shift and push the button, it does not work.

If I edit the button's code to only have the command to go to a new record without the confirmation message box like this:

Private Sub NewLocProLink_Click(
    DoCmd.GoToRecord , "", acNewRec
End Sub

Then it works fine in both modes. I would really like to have a confirmation box before, though. Just FYI, I can't think of why this would matter but the button is on a subform. But the subform is where I want to create a new record, so that is where the button belongs. Any ideas would be appreciated.

One other piece of information that I'm not sure why it would have an impact, but maybe someone knows that it might, is that all my tables are using an ODBC connection to link to tables in SQL Server 2014.

Erik A Erik A · Accepted Answer · 2018-04-26T20:31:21

The problem is focus. If you pop up a message box, the message box has focus. Since you're not specifying which form to navigate, it fails.

A simple solution is to navigate using the forms recordset instead:

Private Sub NewLocProLink_Click()
    mbResult = MsgBox("Are you sure you would like to add a new provider to 
    location relationship?", vbYesNo, "New location to provider relationship")

    If mbResult = vbYes Then
        Me.Recordset.AddNew
    ElseIf mbResult = vbNo Then
        MsgBox ("No new record")
    End If
End Sub

New Record Command working in admin mode but not normal mode when If statement present

2 Answers