Run-Time Error '2105'

[ITwannabe]

Hi, I posted previously that I was having issues getting a form to update a table. Thanks to everyone's help, it's working now. I'm still having one issue though.

The form has required fields and I have a custom message box that pops up when one or more field is left blank. When I click "ok" to the message box, I'm getting: Run-time error '2105': You can't go to the specified record.

When I click "End", it takes me back into the form and I'm able to continue entering the fields and when I click Enter, it is updating the table successfully.

I've attempted to stop error 2105 from appearing, but haven't been successful. Looking for feedback. I'm VERY new to VBA so as much detail as you can provide would be most appreciated!

Here's the code:

Option Compare Database
Private Sub cboEmpID_Change()
    Me.txtLegalName.Value = Me.cboEmpID.Column(1)
    Me.txtDOH.Value = Me.cboEmpID.Column(2)
    Me.txtLocation.Value = Me.cboEmpID.Column(3)
    Me.txtMgr.Value = Me.cboEmpID.Column(4)
    Me.txtHRBP.Value = Me.cboEmpID.Column(5)
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim sMsg As String
    
    If Nz(Me.txtDateIssued, "") = "" Then
        sMsg = "Date Issued " & vbCrLf
    End If
    
    If Nz(Me.CAPIPLevel, "") = "" Then
        sMsg = sMsg & "CA/PIP Level " & vbCrLf
    End If
    
    If Nz(Me.Reason, "") = "" Then
        sMsg = sMsg & "Reason " & vbCrLf
    End If
    If Nz(Me.HRAdvisor, "") = "" Then
       sMsg = sMsg & "HR Advisor " & vbCrLf
    End If
    
    If sMsg = "" Then Exit Sub
    
    MsgBox "The following fields are missing data; " & vbCrLf & sMsg, vbInformation, "Missing data!"
    
    Cancel = True
           
End Sub
Private Sub Form_Current()
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2105
    Response = acDataErrContinue
Case Else
    Response = acDataErrDisplay
End Select
End Sub
Private Sub NextRecord_Click()
    DoCmd.GoToRecord , , acNewRec
    
End Sub

 

[Frothingslosh]

So, the first step is this:

Have you stepped through the code to see what line is actually throwing the error? (You can do that by either creating a breakpoint on the line you want to pause execution at, or by adding the command Stop, probably immediately BEFORE the MsgBox command.

Then, every time you press F8, a single line will be executed. (If you break before the MsgBox, it will display and you'll need to hit the OK button, but you'll still be in break mode and can continue stepping through with F8.

Also, are you aware that your Form_Current event does nothing?

And while I'm thinking about it, is this thrown on EVERY record or only one?

 

[MarkK]

To do this...

Private Sub cboEmpID_Change()
    Me.txtLegalName.Value = Me.cboEmpID.Column(1)
    Me.txtDOH.Value = Me.cboEmpID.Column(2)
    Me.txtLocation.Value = Me.cboEmpID.Column(3)
    Me.txtMgr.Value = Me.cboEmpID.Column(4)
    Me.txtHRBP.Value = Me.cboEmpID.Column(5)
End Sub

...I would make an employee subform, and link it to the EmpID combo. It's totally fine to do it the way you are, but if you have a subform, you can drop that subform on any other form where this employee data would be handy to see, and make your system a little more snap-in-snap-out--a little more modular--with self-sufficient replaceable parts. Also, if you then edit that subform, those edits are immediately implemented wherever this subform appears, so it eases your maintenance overhead to begin to think of implementing features as discrete replaceable parts.
Cheers,
Mark

 

[ITwannabe]

@frothingslosh

I hit debug and the line that's highlighted is this:

Private Sub NextRecord_Click()
    DoCmd.GoToRecord , , acNewRec

I'm getting the error every time I enter a record and hit ok to the custom message box regarding missing fields.

Hope this helps. I'm new so I don't always get things on the first try

 

[The_Doc_Man]

I am not saying that you did anything wrong at all, but as an experiment, you might consider deleting that entire NextRecord_Click() routine AND the button. Then create a new command button for the same purpose and let the command-button wizard build a code "scaffold" for you that will do what Access thinks is the right procedure for new record creation. The command wizards are inelegant; they are dumber than a box of rocks; but they get the basics in place - and you can customize what they give you by adding more code as needed in that place.

If you do that, you get TWO things.

- First, the wizard "knows" what to do for creating new records. So you get the default Access behavior for adding a record via a form.

- Second, the command-button wizards always build an error handler inside the event routine, so instead of using the Form_Error() routine, you can use an event routine closer to the actual event to intercept your error.

Just a suggestion, you understand, not a statement of anything that you did wrong. It would be something to try in order to fix your problem.

 

[Frothingslosh]

Okay, I think I see your problem.

Your function is named NextRecord_Click, but you have it GOING to the NEW record, not the next record. (Next record is just the next one in the dataset, regardless of where you are. New record is a brand new record added to the end of the recordset.

My guess is that you have the form's Allow Additions property (in the Property Sheet on the Data tab) set to No, which would absolutely prevent you from adding a new record.

I would take Doc's advice and delete the Next Record button and then add a new one letting the wizard create the code (macro, actually, IIRC). Either that, or replace 'acNewRec' with 'acNext'.

Be aware, though, that using that when you're already on the last record will ALSO generate a 'You can't go to the specified record' error, so you'll want to check to see if you're already AT the last record so you don't try to move further forward if that's the case.

 

[The_Doc_Man]

Good suggestion on "AllowAdditions" property, Frothy! Absolutely something worth checking.

 

[ITwannabe]

Hi all,

Thanks so much for all of your input. I followed Frothy's advice and deleted the NextRecord_Click() routine and the button. I had the wizard create a new button and viola!...error 2105 has been banished to the netherworld from whence it came!!!

Thanks again everyone

 

[Frothingslosh]

To be fair, that was Doc's advice; I just seconded it.