Macros, Error Messages, and Forms

[thebiggermac via AccessMonster.com]

I need to attach a custom error message to my forms. Each form has only one
required field hence the field cannot remain blank. I want to validate that
the field has data before the form is saved, but the cryptic message from
Access just doesn't work. I created a macro that I attached to the
BeforeUpdate property of the form (not the field) which works great EXCEPT
that I get another error message generated by Access itself.

Example:

My macro:

Macro Name: Hull_number
Condition: ([Hull_number]) Is Null
Action: CancelEvent
Action: Msgbox (with my custom error message)
Action: GoToControl: (send the cursor back to the appropriate field)

It works good when I test it but when I close my custom dialog box I get
another message from Access that says: "You canceled the previous operation."

How do I get rid of this second message box? No matter what I try I still get
it or the cryptic Access error message.

I am using Access 2000 so I don't know if this is a bug or just me.

Any suggestions?

 

[Cheese_whiz]

Hi tbm,

Try something like this in the before_update event of the form (instead of
the macro you're using):
__________________________________

Private Sub form_beforeupdate(Cancel as Integer)

If IsNull(Me.Hull_Number) Then
MsgBox("Field 'Hull Number' is required. Please enter a value for 'Hull
Number', or press 'ESC' to undo all changes made since last saved")
Cancel = True
End If

End Sub
_________________________________

I think that should work. Post back if it doesn't.

CW

 

[thebiggermac via AccessMonster.com]

I still get a second error message box, only this one says that the
DoMenuItem was canceled.

Hi tbm,

Try something like this in the before_update event of the form (instead of
the macro you're using):
__________________________________

Private Sub form_beforeupdate(Cancel as Integer)

If IsNull(Me.Hull_Number) Then
MsgBox("Field 'Hull Number' is required. Please enter a value for 'Hull
Number', or press 'ESC' to undo all changes made since last saved")
Cancel = True
End If

End Sub
_________________________________

I think that should work. Post back if it doesn't.

CW

I need to attach a custom error message to my forms. Each form has only one
required field hence the field cannot remain blank. I want to validate that

[quoted text clipped - 22 lines]

Any suggestions?

 

[Cheese_whiz]

Darn, sorry.

Add:
Exit Sub

just below where it says:
Cancel = True

See if that works
CW

I still get a second error message box, only this one says that the
DoMenuItem was canceled.

Hi tbm,

Try something like this in the before_update event of the form (instead of
the macro you're using):
__________________________________

Private Sub form_beforeupdate(Cancel as Integer)

If IsNull(Me.Hull_Number) Then
MsgBox("Field 'Hull Number' is required. Please enter a value for 'Hull
Number', or press 'ESC' to undo all changes made since last saved")
Cancel = True
End If

End Sub
_________________________________

I think that should work. Post back if it doesn't.

CW

I need to attach a custom error message to my forms. Each form has only one
required field hence the field cannot remain blank. I want to validate that

[quoted text clipped - 22 lines]

Any suggestions?

 

[thebiggermac via AccessMonster.com]

I thank you for your help CW as you have put me on the right path, however I
am still getting that second error window telling me that the DoMenuItem was
canceled.

Darn, sorry.

Add:
Exit Sub

just below where it says:
Cancel = True

See if that works
CW

I still get a second error message box, only this one says that the
DoMenuItem was canceled.

[quoted text clipped - 25 lines]

 

[Cheese_whiz]

Hi again tbm,

I think maybe you've attached that macro to a different event, possibly a
"save" button "on_click" event or something like that. So I think what's
happening is that two events are firing and one still has the macro.

Look for something like that. If you find it, try removing the macro from
whatever event it's on. It MAY be the case you need a slightlymodified
version of what I gave you to go on the "on_click" event of a "save" button
(or some other command button).

I did this recently and my solution has code like I provided on two seperate
events. I'm not sure if it's necessary for the second one but I had put it
in there already and it WAS necessary in the form's "after_update" event. I
haven't tried to remove it from the command button yet.

I'll take a look at it tonight or tomorrow morning and see if I learn
anything more.

CW

I thank you for your help CW as you have put me on the right path, however I
am still getting that second error window telling me that the DoMenuItem was
canceled.

Darn, sorry.

Add:
Exit Sub

just below where it says:
Cancel = True

See if that works
CW

I still get a second error message box, only this one says that the
DoMenuItem was canceled.

[quoted text clipped - 25 lines]

Any suggestions?

 

[thebiggermac via AccessMonster.com]

Thanks CW. The trigger works as advertised. The only thing is that second
error window keeps popping up. Now I don't know if this makes a difference, I
don't see why it should, but my required field is a drop down list. The user
has to select an item from the list to populate the field. But if it is blank,
it is blank with no data in it. The program is still checking for data in
that field. All I need to do is suppress that second error message window.

Hi again tbm,

I think maybe you've attached that macro to a different event, possibly a
"save" button "on_click" event or something like that. So I think what's
happening is that two events are firing and one still has the macro.

Look for something like that. If you find it, try removing the macro from
whatever event it's on. It MAY be the case you need a slightlymodified
version of what I gave you to go on the "on_click" event of a "save" button
(or some other command button).

I did this recently and my solution has code like I provided on two seperate
events. I'm not sure if it's necessary for the second one but I had put it
in there already and it WAS necessary in the form's "after_update" event. I
haven't tried to remove it from the command button yet.

I'll take a look at it tonight or tomorrow morning and see if I learn
anything more.

CW

I thank you for your help CW as you have put me on the right path, however I
am still getting that second error window telling me that the DoMenuItem was

[quoted text clipped - 16 lines]

 

[Cheese_whiz]

tbm,

What exactly are you doing prior to these messages popping up. You have to
either be clicking on a "save" button, or switching records, or something.
Something triggers the the process. What is it?

cw

Thanks CW. The trigger works as advertised. The only thing is that second
error window keeps popping up. Now I don't know if this makes a difference, I
don't see why it should, but my required field is a drop down list. The user
has to select an item from the list to populate the field. But if it is blank,
it is blank with no data in it. The program is still checking for data in
that field. All I need to do is suppress that second error message window.

Hi again tbm,

I think maybe you've attached that macro to a different event, possibly a
"save" button "on_click" event or something like that. So I think what's
happening is that two events are firing and one still has the macro.

Look for something like that. If you find it, try removing the macro from
whatever event it's on. It MAY be the case you need a slightlymodified
version of what I gave you to go on the "on_click" event of a "save" button
(or some other command button).

I did this recently and my solution has code like I provided on two seperate
events. I'm not sure if it's necessary for the second one but I had put it
in there already and it WAS necessary in the form's "after_update" event. I
haven't tried to remove it from the command button yet.

I'll take a look at it tonight or tomorrow morning and see if I learn
anything more.

CW

I thank you for your help CW as you have put me on the right path, however I
am still getting that second error window telling me that the DoMenuItem was

[quoted text clipped - 16 lines]

Any suggestions?

 

[thebiggermac via AccessMonster.com]

I go to a second field enter data then try to save the record. The proper
custom error message pops up as it is suppose to. As soon as I close the
custom error message dialog box the second dialog box appears informing me
that the action was canceled (as previously described).

I have performed these actions (using macros) before in other databases I
created but have never received a second dialog box which is why I am really
puzzled by this.

tbm,

What exactly are you doing prior to these messages popping up. You have to
either be clicking on a "save" button, or switching records, or something.
Something triggers the the process. What is it?

cw

Thanks CW. The trigger works as advertised. The only thing is that second
error window keeps popping up. Now I don't know if this makes a difference, I

[quoted text clipped - 29 lines]

 

[Cheese_whiz]

I go to a second field enter data then try to save the record.

HOW do you try to "save the record"? Command button?

CW

 

[thebiggermac via AccessMonster.com]

Yes. The form has numerous command buttons (save record, close, delete record,
add record, open reports, and so forth) making it easy for the end user.

 

[thebiggermac via AccessMonster.com]

As an addum, I just this moment finished updating to Office 2003. Now I don't
know what that will do me as I have never used Access 2003 before. New
learning curve I guess.

 

[Cheese_whiz]

Heh, I can't quite get you to say that you're actually clicking on a "save"
button when you get the two messages, but I'm going to assume so for this
post.

Assumming that's true, try adding this to your save button on_click event:


If Me.Dirty Then
If IsNull(Me.Hull_Number) Then
MsgBox ("You must enter a 'Hull Number' to save an issue" &
vbCrLf _
& "Enter a Hull Number or press 'ESC' to clear the issue
form")
Exit Sub
End If

Me.Dirty = False

Else
If MsgBox("No changes made!", vbOKOnly, "OGCIT") = vbOK Then
Exit Sub

End If
End If


That code goes at the top of the on_click event (above any other commands
you have in the code for that event). You probably don't have any, as I
think you said you were using a macro to save the record. If you use this,
you won't be using that macro anymore (see above for how to add this to the
on_click event of your save button).

I've used the code I"ve posted in access 2003 so it should work. Make sure
I've correctly identified your field name(Hull_Number?).

Hope this helps
CW

 

[thebiggermac via AccessMonster.com]

You are correct I do click on a Save button to save the data. Sorry about
that. I will try this code you sent and see what happens. Please remember VB
code and I generally do not get along very well (lack of knowlege on my part)
which is why I like to use macros. They are much simplier for my simple mind
to grasp.

Heh, I can't quite get you to say that you're actually clicking on a "save"
button when you get the two messages, but I'm going to assume so for this
post.

Assumming that's true, try adding this to your save button on_click event:

If Me.Dirty Then
If IsNull(Me.Hull_Number) Then
MsgBox ("You must enter a 'Hull Number' to save an issue" &
vbCrLf _
& "Enter a Hull Number or press 'ESC' to clear the issue
form")
Exit Sub
End If

Me.Dirty = False

Else
If MsgBox("No changes made!", vbOKOnly, "OGCIT") = vbOK Then
Exit Sub

End If
End If

That code goes at the top of the on_click event (above any other commands
you have in the code for that event). You probably don't have any, as I
think you said you were using a macro to save the record. If you use this,
you won't be using that macro anymore (see above for how to add this to the
on_click event of your save button).

I've used the code I"ve posted in access 2003 so it should work. Make sure
I've correctly identified your field name(Hull_Number?).

Hope this helps
CW

As an addum, I just this moment finished updating to Office 2003. Now I don't
know what that will do me as I have never used Access 2003 before. New

[quoted text clipped - 5 lines]

 

[Cheese_whiz]

tbm,

No problem. For that to work, you'll need that code I gave you yesterday in
the after_update event of FORM as well.

As for vba code, it's well worth a little effort to learn. I'm far from an
expert, but I can do most of the "small" things I need and there's quite a
few resources out there with "big chuncks" when you need some functionality
that's beyond your abilities. Macros are ok in some limited cases, but most
of what I've read indicates that experienced developers largely scoff at
them. I think there's some security issues among other things (and I also
believe, appropriate to this situation, that macros are MUCH harder to
troubleshoot than vba code).

Anyway, just my two cents. I'm not the best helper around here, but I have
dealt with the issue you have very recently so I know what I've tried to
communicate to will work, provided the code makes it into your application
properly and there's no hidden complications that I'm not aware of at this
point.

CW

You are correct I do click on a Save button to save the data. Sorry about
that. I will try this code you sent and see what happens. Please remember VB
code and I generally do not get along very well (lack of knowlege on my part)
which is why I like to use macros. They are much simplier for my simple mind
to grasp.

Heh, I can't quite get you to say that you're actually clicking on a "save"
button when you get the two messages, but I'm going to assume so for this
post.

Assumming that's true, try adding this to your save button on_click event:

If Me.Dirty Then
If IsNull(Me.Hull_Number) Then
MsgBox ("You must enter a 'Hull Number' to save an issue" &
vbCrLf _
& "Enter a Hull Number or press 'ESC' to clear the issue
form")
Exit Sub
End If

Me.Dirty = False

Else
If MsgBox("No changes made!", vbOKOnly, "OGCIT") = vbOK Then
Exit Sub

End If
End If

That code goes at the top of the on_click event (above any other commands
you have in the code for that event). You probably don't have any, as I
think you said you were using a macro to save the record. If you use this,
you won't be using that macro anymore (see above for how to add this to the
on_click event of your save button).

I've used the code I"ve posted in access 2003 so it should work. Make sure
I've correctly identified your field name(Hull_Number?).

Hope this helps
CW

As an addum, I just this moment finished updating to Office 2003. Now I don't
know what that will do me as I have never used Access 2003 before. New

[quoted text clipped - 5 lines]

CW

 

[thebiggermac via AccessMonster.com]

Thanks CW, your code worked. Now all I have to do is tweak it for the project.
I have absolutely no clue how to do that yet, but I will figure it out. At
least now I don't have that second error message box popping up. Again,
thanks a lot.

tbm,

No problem. For that to work, you'll need that code I gave you yesterday in
the after_update event of FORM as well.

As for vba code, it's well worth a little effort to learn. I'm far from an
expert, but I can do most of the "small" things I need and there's quite a
few resources out there with "big chuncks" when you need some functionality
that's beyond your abilities. Macros are ok in some limited cases, but most
of what I've read indicates that experienced developers largely scoff at
them. I think there's some security issues among other things (and I also
believe, appropriate to this situation, that macros are MUCH harder to
troubleshoot than vba code).

Anyway, just my two cents. I'm not the best helper around here, but I have
dealt with the issue you have very recently so I know what I've tried to
communicate to will work, provided the code makes it into your application
properly and there's no hidden complications that I'm not aware of at this
point.

CW

You are correct I do click on a Save button to save the data. Sorry about
that. I will try this code you sent and see what happens. Please remember VB

[quoted text clipped - 43 lines]

 

[Cheese_whiz]

tbm,

Glad it worked. If you have something particular in mind in terms of
"tweaks", I can try to help with those. Again, others are more competent
than I am, so maybe a new thread when you run into a question/problem will
elicit some less scatter-brained respondants.

Good luck,
CW

Thanks CW, your code worked. Now all I have to do is tweak it for the project.
I have absolutely no clue how to do that yet, but I will figure it out. At
least now I don't have that second error message box popping up. Again,
thanks a lot.

tbm,

No problem. For that to work, you'll need that code I gave you yesterday in
the after_update event of FORM as well.

As for vba code, it's well worth a little effort to learn. I'm far from an
expert, but I can do most of the "small" things I need and there's quite a
few resources out there with "big chuncks" when you need some functionality
that's beyond your abilities. Macros are ok in some limited cases, but most
of what I've read indicates that experienced developers largely scoff at
them. I think there's some security issues among other things (and I also
believe, appropriate to this situation, that macros are MUCH harder to
troubleshoot than vba code).

Anyway, just my two cents. I'm not the best helper around here, but I have
dealt with the issue you have very recently so I know what I've tried to
communicate to will work, provided the code makes it into your application
properly and there's no hidden complications that I'm not aware of at this
point.

CW

You are correct I do click on a Save button to save the data. Sorry about
that. I will try this code you sent and see what happens. Please remember VB

[quoted text clipped - 43 lines]

CW

 

[thebiggermac via AccessMonster.com]

I'm going to post a new thread under general users 1 this time concerning the
tweaks I need as this one is getting fairly long. See you over there.

tbm,

Glad it worked. If you have something particular in mind in terms of
"tweaks", I can try to help with those. Again, others are more competent
than I am, so maybe a new thread when you run into a question/problem will
elicit some less scatter-brained respondants.

Good luck,
CW

Thanks CW, your code worked. Now all I have to do is tweak it for the project.
I have absolutely no clue how to do that yet, but I will figure it out. At

[quoted text clipped - 28 lines]

 

[John W. Vinson]

Please remember VB
code and I generally do not get along very well (lack of knowlege on my part)
which is why I like to use macros. They are much simplier for my simple mind
to grasp.

Don't let VBA code buffalo you. It's very logical (mindlessly so, even
a totally idiotic INTEL chip can figure it out <bg>) and systematic.

In this case - VBA code has a huge advantage over macros. VBA code can
trap errors and respond to them; Macros *cannot*. If you want error
trapping, CheeseWhiz is perfectly correct - code is the way to go.

John W. Vinson [MVP]