Can't find a way to trap error!

[melissa_may1]

Hi All,

I'm using Access 2007, and have created a database with some forms and reports that are working fine.

The user is having some trouble, due to accidental changes on the forms.

I want to alert the user when a change has been made, and give her the option of saving or discarding the changes.

I've added code to the form's BeforeUpdate event (see below), and the code seems to do its job. If a change has been made, the messagebox pops up with "Do you wish to save the changes?" and the user can choose "Yes" or "No."

When the user selects "Yes" everything works as expected.

However, when the user chooses "No." there is an undesired error message that pops up: "You can't go to the specified record."

I've done everything I can think of to trap this error, but nothing seems to work.

Since the query for the form has been entered through the form's properties, I'm thinking that the error is being generated by the query, that VBA doesn't have control over the query (since it's in the form's properties and not in a code module), and thus can't trap the error.

So, what do I do?

Do I move the query into VBA? (And, I'm guessing that I'll have to move all of the First/Next/Previous/Last logic into VBA as well?)

Or is there a way to eliminate or trap this error?

Thanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strMsg As String
   Dim iResponse As Integer

   ' Specify the message to display.
   strMsg = "Do you wish to save the changes?" & Chr(10)
   strMsg = strMsg & "Click Yes to Save or No to Discard changes."

   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
   
   ' Check the user's response.
   If iResponse = vbNo Then
      
      ' This doesn't stop the error.
      'On Error Resume Next
      
      ' Undo the changes to record
      ' DoCmd.RunCommand acCmdUndo
      
      ' Undo the changes to record
      Me.Undo
      
      ' Requery to avoid error
      ' DOESN'T WORK
      'Me.Requery

      ' Cancel the update.
      Cancel = True
   End If

End Sub



Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    ' This messagebox never fires!
    MsgBox DataErr
    
    ' None of this code seems to work!
    Select Case DataErr
    Case 2169
        Response = acDataErrContinue
    Case 2105
        Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
    End Select
End Sub

 

[vbaInet]

When the error message comes up and you hit DEBUG, what code line does it highlight?

 

[melissa_may1]

Hi,

Thanks for the reply!

I've tried all of the following keys, and nothing happens:

F9, F8, Shift+F8, Ctrl+Shift+F8, Ctrl+F8, Ctrl+F9

Is there another "Debug" key I'm missing?

Note that the error msgbox has only an OK button, not even a Close "X" button, so there's nothing else I can think of to do.

Any ideas?

Thanks!

 

[vbaInet]

* Comment out all the code in the form's Error event
* Comment out the On Error Resume Next line
* Comment out the Me.Requery line
* Re-run the code

 

[melissa_may1]

Hi Again!

Thanks for your continued replies.

I have done as you suggested, and still get the same error message.

I'm out of ideas!



Thanks!

 

[vbaInet]

I don't think the error message is coming from that event. It's kicking off some other event.

That error message normally indicates that you are trying to move to a record at a time when it isn't possible. Do you have code that moves between records?

 

[spikepl]

I had a similar problem recently, but then was doin a lot of weird stuff.

I do not believe it's your BeforeUpdate that generates this message, but check that. Stept through the code and say when the error occurs. Set a break point. See the "Step through code" here, if you don't know how. http://www.access-programmers.co.uk/forums/showthread.php?t=149429

What is in you OnCurrent handler, if any? Do you have a subform attached with some child records? Some Oncurrent there?

 

[melissa_may1]

Hi vbaInet and spikepl,

I really appreciate your help!

I think we're zeroing in on the problem!

"I don't think the error message is coming from that event. It's kicking off some other event."

Yes, that's what I've been thinking. But I couldn't figure out where this error message is coming from.

"That error message normally indicates that you are trying to move to a record at a time when it isn't possible. Do you have code that moves between records? "

No, I don't have code that moves the records. That's being done on the form's macro...

Huh?

Be right back!!!

 

[vbaInet]

Perhap's the On Current event like spikepl mentioned? So, step through your code as explained in spikepl's post and see where it stops.

Can you upload s sample db?

 

[melissa_may1]

OK you two, I think we've got it!

I'm using a "split form" with the datasheet below the form.

On the form, I have First/Next/Previous/Last buttons, each of which has a macro attached to it to move the record.

It is in the macro that I have the msgbox pop up if there's an error!

If I use one of the form buttons, the error pops up. But if I use the navigation buttons below the datasheet, no error!

Here's what I have in the macros:

OnError Next
GoToRecord ,,Next,
[MacroError]<>0 MsgBox =[MacroError].[Description], Yes, None,

So, now it's obvious that the msgbox is popping up from the macro!

Silly me.

Now, I could fix this by either moving this code to VBA, or by "fixing" [MacroError]<>0 so it ignores this error.

Which, on second thought, won't work because it will then ignore a legitimate error!

So, I guess that moving this code to VBA is a better idea!

Now, to figure out how best to code record navigation...

Thanks again to you both for ferreting this out. I find it difficult to work these things out alone, when running it by another person or two gives me some ideas, and together we figure it all out.

You two are great!

 

[spikepl]

Hmm ... the macros run when you hit the button, so I don't quite see how this should trigger your error. Unless you have some macro attached to another event than a *navigation* button's OnClick.

As to how to get the code? Copy your form. On the copy, in design view, somewhere there is a button so you can convert all the form's macros to vba. Then you can copy it from there.

 

[melissa_may1]

One more thing...

I have no code in OnCurrent.

And setting a breakpoint in Form_BeforeUpdate showed me that the error message was popping up after Form_BeforeUpdate was done.

Further, single-stepping through Form_BeforeUpdate showed that the error message was not being generated by VBA, since it appeared after the code had executed.

The question about how the Next/Previous buttons were coded led me to look in the macro for those buttons, and ultimately to the actual problem.

Good catch you two!

 

[vbaInet]

So, I guess that moving this code to VBA is a better idea!

That's the spirit!

Now, to figure out how best to code record navigation...

Same code as the macro but I don't see what is causing it to fire. If you can create a quick test db we will be able to tell you how best to proceed.

Thanks again to you both for ferreting this out. I find it difficult to work these things out alone, when running it by another person or two gives me some ideas, and together we figure it all out.

It's all in the team work

 

[melissa_may1]

Hey spikepl,

Good question, and exactly why I was confused!

I'm guessing that the sequence of events is:

1) Make a change to the record
2) Click "Next"
3) The macro behind the Next button fires, and starts the "move to next record" process
4) The "Move to next record" process detects a change, so starts the "Update" process.
5) The Form_BeforeUpdate event fires.
6) The Form_BeforeUpdate code pops up the msgbox, asking if it's OK to commit the change
7) The user clicks "No"
8) The Form_BeforeUpdate code does an "Undo" to reset the record, and a "Cancel" to stop the Update.
9) Form_BeforeUpdate code ends, and execution returns to the macro
10) The macro sees an "error" because the record is not updated, and therefore it cannot move to the next record.
11) I then spend 11.75 hours trying to figure out where the error's coming from!

At least that's as good an explanation as I can come up with.

Now, off to do the "copy form and convert macro" thing!

Thanks!

 

[spikepl]

Ah, the blessings of macros ..another tool of Beelzebub

 

[melissa_may1]

OK, so you can't get rid of me this easily...

I converted the macro to VBA code.

Here's what I did:

1) Open the form in Design View
2) Click on the DataBase Tools tab up top
3) Convert Form's Macros to Visual Basic
4) Accept the defaults in the msgbox, and click OK.
5) Macros are magically transformed to VBA
6) Yay!
7) Not quite.

I'm still getting the error! Though now it's a bit easier to find, since it's in with all the other code now.

Here's what the "macro-converted-to-VBA" looks like:

Private Sub btnNext_Click()
On Error GoTo btnNext_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNext
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If


btnNext_Click_Exit:
    Exit Sub

btnNext_Click_Err:
    MsgBox Error$
    Resume btnNext_Click_Exit

End Sub

The weird thing is that program execution halts on error at the line "DoCmd.GoToRecord , "", acNext", but doesn't "OnErrorResumeNext."

So, I'm still thinking that the problem is that I'm now running a macro command from code, rather than having the code to move the record.

I have a major headache right now...

 

[spikepl]

Now you need to handle the error properly. Kick out the "On Error Resume Next
line. And all that Macro-blabla stuff -it's redundant.

And in the error handler, trap the code for your error.


The thing is complaining about the GoToRecord, that gets cancelled by the BeforeUpdate. So just make it ignore the wailings by trapping the message.

 

[spikepl]

DL http://www.mztools.com/index.aspx - it makes creation of error handlers much easier, and there are other goodies there too.

 

[melissa_may1]

Hi spikepl,

I did what you suggested, and removed both the "On Error GoTo btnNext_Click_Err" and the "On Error Resume Next."

However, it still errors on DoCmd.GoToRecord , "", acNext

This thing is making me (more) crazy!

Should I just put in code to move to the next record, rather than rely on DoCmd.GoToRecord , "", acNext?

If so, I'll need to do some studying to figure out how that code should look.

Sheesh!

 

[spikepl]

I did not suggest kicking "On Error GoTo btnNext_Click_Err" !!! Thatis your error handler !

if you don't know how error handling works in Access then have a look here: http://allenbrowne.com/ser-23a.html