Trying to hide an error when it occurs

[George_E]

Hey guys

I have a form with textboxes retrieving data from a query.

On the form I also have 2 buttons. One takes the user to the next record and the other takes the user to the previous record.

The problem arises when the user reaches the end of the record set...basically runs out of records. I am getting the message

You may have reached the end of your recordset

and another message saying the error number 2950.

What I have attempted is in the Before update property of the form I have entered the below code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Form_BeforeUpdate_Error

Form_BeforeUpdate_Error:

If Err.Number = 2950 Then
    MsgBox("You have reached the last record")
End If
End Sub

Unfortunately my coding isnt stopping the error from appearing.....

Can anybody guess why? or have any suggestions to how to stop this occurring?

I look forward to your responses

George

 

[dcb]

Your code looks about right - You need to place it in the same procedure as the button

You could also est if you are at the end of the recordset instead of trapping...

 

[George_E]

Your code looks about right - You need to place it in the same procedure as the button

Hello Dcb, thankyou for your reply

The only problem with that is that the buttons are running with macros.....

Amateur, I know, the only reason I am not running my buttons with vba, is because when I press the buttons next or previous, I want the records being retrieved, to have their Id number equal to the number in one of the textboxes in the form....if that makes sense..and the problem with that is that I dont know how to code that...


George

 

[dcb]

I want the records being retrieved, to have their Id number equal to the number in one of the textboxes in the form

This textbox is on a mainform and you have a sub form or something in the header? explain a little more.... Hopefully someone here will know how to error trap a macro and jumps in - not my cuppa tea

 

[Zigzag]

Try using

Response = acDataErrContinue

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Form_BeforeUpdate_Error

Form_BeforeUpdate_Error:
Response = acDataErrContinue
If Err.Number = 2950 Then
MsgBox("You have reached the last record")
End If
End Sub

I would also endorse bcds comments that you should check for next record before move.

 

[George_E]

The textbox is on the form, and it is retrieving its value from a textbox in another form with the below code in its default value..

=[Forms]![frmIssues]![Issue Reference]

The buttons then browse all of the records that have the same Id number as the number in that textbox.

Hope that helps

George

 

[George_E]

Hello Zigzag, I just attempted that and its not making a difference...thankyou for your response though

George

 

[dcb]

The textbox is on the form, and it is retrieving its value from a textbox in another form with the below code in its default value..

=[Forms]![frmIssues]![Issue Reference]

The buttons then browse all of the records that have the same Id number as the number in that textbox.

Hope that helps

George

That is already set up in your recordset? How does your movenext have any reference to that?

What if you use DoCmd.GoToRecord , , acNext

 

[Zigzag]

If DoCmd.GoToRecord , , acNext does not work can you post your db so we can have a look and identify a solution for you.

 

[George_E]

That is already set up in your recordset? How does your movenext have any reference to that?

What if you use DoCmd.GoToRecord , , acNext

I have just tried using the above code and it works, it takes me to the same recordset with the Id number equal to the number in the textbox, however once I reach the end of the recordset now, I am getting the error

Run-time error 2105

You can't go to the specified record

I have altered my code now to cater for error 2105 instead of the error 2905 which I was getting before, but I am still getting an error message

George

 

[dcb]

Post the procedure quick

 

[George_E]

post your db so we can have a look and identify a solution for you.

Ok, I'll just make a sample now

 

[dcb]

Your code should look something like this:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click


    DoCmd.GoToRecord , , acNext

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    If Err.Number = 2105 Then
    Debug.Print Err.Number, Err.Description
    Resume Exit_Command6_Click
    Else
    MsgBox Err.Number & " - " & Err.Description
    End If
End Sub

 

[George_E]

Hey guys

I attach a sample of the database.....another thing I have noticed is when I click the next button (Later Notes), eventually the textboxes show default values.......

Anyway, I hope the database gives you an idea

George

 

[George_E]

Hey Dcb, I just tried your code and thats not making a difference either

 

[George_E]

sorry Dcb, I stand corrected, your coding works

 

[George_E]

I have just been fiddling around with the code and it works beautifully

I made a slight ammendment so a message appears when you are at the end of the recordset saying

You have reached the last record

Private Sub btnNextRecord_Click()
On Error GoTo Err_btnNextRecord_Click


DoCmd.GoToRecord , , acPrevious

Exit_btnNextRecord_Click:
Exit Sub

Err_btnNextRecord_Click:
If Err.Number = 2105 Then
MsgBox ("You have reached the last record")
Debug.Print Err.Number, Err.Description
Resume Exit_btnNextRecord_Click
Else
MsgBox Err.Number & " - " & Err.Description
End If

End Sub

The only thing is could I stop the default values coming up as the last record?

Thankyou Dcb and Zigzag, I wouldnt have managed it without you

George