BeforeUpdate problems (1 Viewer)

[ShaneMan]

Hey G Hudson,

I stripped it down to one form, that I'm currently working on and the tables associated with it. There are no records in the table. Hope it doesn't give you problems but if it does, let me know and I'll send a little more with it.

Thanks,
Shane

 

[Pat Hartman]

Yup. Sorry about that. I removed the With from your code since you weren't using it properly and I personally don't like it anyway. I also altered your missing data message.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.Tag = "Required" Then
            If ctl.Value = "" Or IsNull(ctl.Value) Then
                MsgBox "Missing data in " & ctl.Name
                ctl.SetFocus
                Cancel = True
                NoErrors = False
                Exit Sub
            Else
                NoErrors = True
            End If
        End If
End If

Next

End Sub

 

[ShaneMan]

Pat, thank you for your reply. I copied and pasted your code straight into my db.

Problem that I am still having is two Access messages.
*Can not save this record
*Close Action has been cancelled.

Also, after these messages fire. Whatever data had been entered up to this point is undone.

I really, really appreciate you guys hanging in on this one.
Shane

 

[Pat Hartman]

You're going to need to post all the code so we can see what events it is in. The Close event cannot be cancelled. If you want to prevent a form from closing, the code must go in the unload event.

 

[ShaneMan]

Pat, the code is in the Unload event. You posted yesterday telling me how to put NoErrors in Current, Unload and check in BeforeUpdate. I did it just as you instructed. I did get a stripped down attachment to GHudson above that you can see the form that I'm attempting to do this on.

 

[Pat Hartman]

The code in your BeforeUpdate event wasn't the code I posted so I commented it out and replaced it with the code I suggested.

I had to add the Category table since it wasn't in the database.

I made a lot of changes to the form because it was way too annoying trying to debug it in its original state. I never remove the close box or make forms modal unless they absolutely work. You can change them back.

I added error trapping that ignores those errors since they are just Access being overly cautious. Error 2501 - Action cancelled - is a pretty common error. It happens EVERY time a macro action is cancelled so many events should specifically ignore it. "DoCmd.Close" is a macro action even though it is VBA.

One thing I discovered when working on this problem was a "bug" in the way Access handles the Close Method/Action. I've never run into this problem because I don't normally put close buttons on my forms. I just use the control box X because people have become used to that method.

The bug is that when you use the close Method/Action to close a form and Jet returns an error, the message is not displayed and the changes to the form are quietly discarded. So, I looked up the help entry for the Close Method and they mention this little "quirk" and offer suggested code for the Unload event which answers my question of why everyone wants to put their error checking code in that event. The code still belongs in the BeforeUpdate event but if you use a Close button, you need to be much more careful to anticipate Jet errors and verify that your error trapping is working properly if a jet error happens.

This bug/error is no longer happening in the copy I returned because I fixed the underlying cause of the error. The three foreign key fields were set to default to 0 so if you filled in only the fields your code designated as required, the foreign key fields would default to 0 which is of course an invalid value since no 0 key record exists in any of the tables. I removed the defaults. If the fields are required, make them required in your code. To see the error, just put the defaults back in the table or check it in your copy.

I also noticed the use of SQL strings in VBA. It is more efficient to create querydefs and use them. In an Access database that uses Jet tables, querydefs are the equivalent of stored procedures. Since none of the SQL strings I noticed were dynamic, there is no reason that they shouldn't be querydefs.

 

[ShaneMan]

Pat,

Thank you very much for all your time and efforts. This is my first database. My family and I are in medical equipment sales, so I certainly don't do this for a living. With that being said, thank you also, for the advise and observation.

I do the Modal on my forms because I have a splash screen that stays open in the background at all times in a maximized state. I do this to keep the users from using the toolbars and etc. I take it that since you seldom use Modal, this is probably not a real good idea?

I'll take a look at the changes as soon as I can.

A big thanks once again,
Shane