Supress error msg and create custom msg on required form controls, help?

[freenet_2k@hotmail.c]

I've been tinkering with this problem for a few days now and I can't get it to work. The code I am having trouble with is below. In some sense it works, but not completely. I have a form control that is linked to a table column that is "required" so that if the user does not enter anything in the required field before trying to save the record, an error msg pops up. Well the code I have posted here does supress the system error, but it appears as if two error messages come up instead of one. If I hit refresh, my custom msg comes up, but only after another system runtime error msg. Both of the error messages basically say the same thing, it's just that they look different. The one that gets supressed by this code, says I cannot leave the field blank. That message has only an "OK" button. The other message says basically the same thing, but is a ruintime error msg that gives me "End, Debug and cancel" buttons. The msg that has the "OK" button is the one that gets suppressed and is replaced with my custom msg when I hit refresh. The msg with "Debug" and "End" buttons is the one that comes up no matter what I do. If I take this code out of the form, I get two error msg's, so it looks like it is supressing one, but not the runtime msg. Can anyone help me with this? I need to be able to get rid of that runtime error. I'm VERY new to access and I have a strict deadline where I have to have this finished today or I'm in big trouble. Thanks in advance for any and all replies. I do appreciate it.
Here is the code I have in the On Error event of the form. BTW...I'm using Access 2007.

Begin VBA ode:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'If an error occurs because of missing data in a required field
'display custom error message
Const conErrRequiredData = 3314
If DataErr = conErrRequiredData Then
MsgBox ("Please enter Manufacturer Name")
Response = acDataErrContinue
Else
'Display a standard error message
Response = acDataErrDisplay
End If
End Sub

 

[dkinley]

Just a guess on the data you are passing to the sub.

You use "DataErr As Integer" when you call it are you going ....

Form_Error(Err, x).

If so you probably need to use Form_Error(Err.Number, x).

-dK

 

[freenet_2k@hotmail.c]

Just a guess on the data you are passing to the sub.

You use "DataErr As Integer" when you call it are you going ....

Form_Error(Err, x).

If so you probably need to use Form_Error(Err.Number, x).

-dK

Thanks for your reply. I sure wish I knew what you were telling me to try. I'm too new to VBA code to quickly understand what you think the problem might be from your post, but I'll try to figure it out though. Thanks again!

 

[dkinley]

My bad. I just noticed (even though you said it) that you were using the error event, I was thinking a function you created ...

I was thinking in terms of handling errors a different way. If so, all you would need to do is change this ...

If DataErr = conErrRequiredData Then

To this ...

If DataErr.Number = conErrRequiredData Then

Don't know if it will work in this instance or not - I am not that familiar with the form error event but give me a few minutes. =]

-dk

 

[freenet_2k@hotmail.c]

My bad. I just noticed (even though you said it) that you were using the error event, I was thinking a function you created ...

I was thinking in terms of handling errors a different way. If so, all you would need to do is change this ...

If DataErr = conErrRequiredData Then

To this ...

If DataErr.Number = conErrRequiredData Then

Don't know if it will work in this instance or not - I am not that familiar with the form error event but give me a few minutes. =]

-dk

You Rck! Thanks friend!

 

[dkinley]

I've reread again ...

The requirement is set at the table so it is an engine-level error. I am thinking the double-message is because of what the error is. I am not sure what you are trying to do after supress the message.

For instance, you might still be trying to force a save or close after (meaning the return from the error sub doesn't force a stop) so it gives you the other message. Or you are not treating the sub return correctly because of the type of error. This would point to a different area of the database as being the culprit.

If you could run it again and this time click Debug. Copy and past the highlighted code that is causing you problems.

-dK

 

[dkinley]

You Rck! Thanks friend!

Hehe .. I was typing going on in case that didn't work.

Glad that did work; I learned something in it too =]

Good luck!

-dK

 

[freenet_2k@hotmail.c]

I've reread again ...

The requirement is set at the table so it is an engine-level error. I am thinking the double-message is because of what the error is. I am not sure what you are trying to do after supress the message.

For instance, you might still be trying to force a save or close after (meaning the return from the error sub doesn't force a stop) so it gives you the other message. Or you are not treating the sub return correctly because of the type of error. This would point to a different area of the database as being the culprit.

If you could run it again and this time click Debug. Copy and past the highlighted code that is causing you problems.

-dK

Thanks again. When I debug the error which is Runtime Error 3314, it highlights the line that says DoCmd.Close iin this section of the code only that command is highlighted when I debug it.

Private Sub SbmtNewItem_Click()
Dim MfgAfterNewSubmit As String
'Create a variable to hold the manufac number
MFGCATALOGNUM.SetFocus
MfgAfterNewSubmit = MFGCATALOGNUM.Text
'Close this AddNewItemForm and go back to the Main form
DoCmd.Close
'On Main form clear out the manufac number, requery the combobox, and reset the value from the variable
Forms!main!Combo61.Undo
Forms!main!Combo61.Requery
Forms!main!Combo61.Text = MfgAfterNewSubmit
End Sub


Thanks again!

 

[dkinley]

Ah okay, suspicion confirmed then. Still trying to save the record with the DoCmd.Close.

Thanks for posting the response.

-dK

 

[freenet_2k@hotmail.c]

Hehe .. I was typing going on in case that didn't work.

Glad that did work; I learned something in it too =]

Good luck!

-dK

Sorry, I didn't meant confuse things. I just wanted to thank you for helping me out. I still have the problem so I posted the debug code as you requested. I'm totally lost (Still) . Sorry

 

[freenet_2k@hotmail.c]

Ah okay, suspicion confirmed then. Still trying to save the record with the DoCmd.Close.

Thanks for posting the response.

-dK

Does the close Cmd give you any info as to why I am getting the second error msg still? thx

 

[dkinley]

lol Oh. Yes. You are still trying to save a record on a required field.

All you've done is tell the system to 'say my custom message instead of yours'. What you haven't done is tell the system 'I know there is a required field, but save the record anyway.'

I don't think you can because it is at the engine level. The required field is required for a reason. You can either turn off the requirment (not advised because it could create havoc in other areas of the database) or stop the code (DoCmd.Close) from processing so the record won't try to be saved.

Now, you have ...
Without testing because I am not sure how to handle it after it is flagged from the form event. What if we preempted the error to prevent it from ever occuring by writing in something that would check it first?

Private Sub SbmtNewItem_Click()
Dim MfgAfterNewSubmit As String
 
'check to see if manufac name is not filled in
If Len(Nz(Me.txtManu, "")) = 0 Then
    Msgbox "Please enter manufacturer name.", vbCritical , "Manufacturer Name"
    Me.txtManu.SetFocus
Else
    'Create a variable to hold the manufac number
    MFGCATALOGNUM.SetFocus
    MfgAfterNewSubmit = MFGCATALOGNUM.Text
 
    'Close this AddNewItemForm and go back to the Main form
    DoCmd.Close
 
    'On Main form clear out the manufac number, requery the combobox, and reset the value from the variable
    Forms!main!Combo61.Undo
    Forms!main!Combo61.Requery
    Forms!main!Combo61.Text = MfgAfterNewSubmit
 
End If
 
End Sub

Now, I used "txtManu" for the control of the manufacturer name. It seems you use a combo box for this possibly. So it will need to be referenced correctly.

-dk

 

[freenet_2k@hotmail.c]

lol Oh. Yes. You are still trying to save a record on a required field.

All you've done is tell the system to 'say my custom message instead of yours'. What you haven't done is tell the system 'I know there is a required field, but save the record anyway.'

I don't think you can because it is at the engine level. The required field is required for a reason. You can either turn off the requirment (not advised because it could create havoc in other areas of the database) or stop the code (DoCmd.Close) from processing so the record won't try to be saved.

Now, you have ...
Without testing because I am not sure how to handle it after it is flagged from the form event. What if we preempted the error to prevent it from ever occuring by writing in something that would check it first?

Private Sub SbmtNewItem_Click()
Dim MfgAfterNewSubmit As String
 
'check to see if manufac name is not filled in
If Len(Nz(Me.txtManu, "")) = 0 Then
    Msgbox "Please enter manufacturer name.", vbCritical , "Manufacturer Name"
    Me.txtManu.SetFocus
Else
    'Create a variable to hold the manufac number
    MFGCATALOGNUM.SetFocus
    MfgAfterNewSubmit = MFGCATALOGNUM.Text
 
    'Close this AddNewItemForm and go back to the Main form
    DoCmd.Close
 
    'On Main form clear out the manufac number, requery the combobox, and reset the value from the variable
    Forms!main!Combo61.Undo
    Forms!main!Combo61.Requery
    Forms!main!Combo61.Text = MfgAfterNewSubmit
 
End If
 
End Sub

Now, I used "txtManu" for the control of the manufacturer name. It seems you use a combo box for this possibly. So it will need to be referenced correctly.

-dk

Thanks again for all your help. I still can't get it to work, but I do appreciate your time!

 

[dkinley]

Is all good.

Any way you can strip all the data out and post your db?

-dK

 

[freenet_2k@hotmail.c]

Hi, Yes I am working on it now. Thanks so much man! The max DB size I can post is 768Kb and I havemine down to 900Kb. Just trying to find a few more things I can delete that wont break it before you see it. Thanks again!

 

[freenet_2k@hotmail.c]

I can'
t upload the file even though it's well below the max size. Grrrrrrrrrrrr. I'm destined to be screwed on this project. I know I'm gogin to get fired over this!!!!!!!!!!!!!!1

 

[freenet_2k@hotmail.c]

OK here's the DB in MDB format. I converted it from the 2007 format which I think is .accdb. When you open it, hit submit, then hit ok , then clear screen. Enter any gibberish in the Mfg Catalog Num field and hit tab. This will open the problem form. Enter gibberish in all but the Mfg Name fields and click submit and you will see the problem. Then hit REFRESH on top and you will see my custom msg. Thank you so very much my friend!

 

[freenet_2k@hotmail.c]

Well I posted the DB file, but it looks like I took too long and may have lost you. Thanks anyway for your time and effort. Looks like I'll be hittin' the unemployment line on Monday.

 

[dkinley]

Okay, did what I could.

I added some primary keys to the tables and renamed some controls so I could keep track of them.

I modified the not in list and go rid of the macro from main and it works; however you can't tab around after that in the new form. I think the problem is your main form and add form both open the same table. I am not sure of your overall requirements and such, but think the main form should be based on a query.

The primary keys and relationship I set will allow this query to be ran effectively.

Hope all of that helps.

-dK

 

[freenet_2k@hotmail.c]

-WOW Thanks Dk! I will take a look at it now. I don't know how to thank you for all your time an effort! Thank You again and again! You're the best!