Would like to handle run time error 94 (Invalid Use of Null) in my code so that I can put my own msg in there so my users don't get the standard one mentioned.
Any help as to how I refer to this error in error handling would be appreciated..
Note: Would like to place this in the 'On Error' event of my form..
Thanks..
EDIT: The sequence that creates this is when the user would enter garbage text into a dropdown, delete the garbage text and then try to exit the form by clicking the x (to close the form)
Because you should deal with things that are not really errors and this is avoidable. If you handle it in the Form's Before Update event you can verify with the user that they really didn't want to have a record. If you just handle the error then you assume for them that they did not, and this may not be the case.
The error is occuring because you, or your user, have started entering a record and then blank out the fields which then would cause a null value to be entered and it is probably set to not allow that (properly so). But you can UNDO things either manually by going up to EDIT > UNDO CURRENT RECORD or by doing it via code.
No, I'm saying you should verify that your user is not really wanting to enter a record and if they answer negative then you put
Cancel = True
Me.Undo
and that will AVOID the error altogether.
Yes, because since a record was started, the form has been "dirtied' and so the Before Update event will occur.
Because you should deal with things that are not really errors and this is avoidable. If you handle it in the Form's Before Update event you can verify with the user that they really didn't want to have a record. If you just handle the error then you assume for them that they did not, and this may not be the case.
The error is occuring because you, or your user, have started entering a record and then blank out the fields which then would cause a null value to be entered and it is probably set to not allow that (properly so). But you can UNDO things either manually by going up to EDIT > UNDO CURRENT RECORD or by doing it via code.
I don't think I'm quite getting my point across. In the scenario I'm thinking of, there will never be a chance to verify if the user wants to enter a record..!
I'll describe the situation to you that I'm trying to avoid:
A user opens the form and see one drop down box. They then type in some garbage that isn't on the drop down.
IMPORTANT POINT - > They have not yet hit enter or will ever hit enter in this scenario, nor have they selected the drop down box arrow to see what's in the drop down box.
The next thing they do is select the garbage they have entered and hit delete, thus leaving the drop down box blank or null. They then click the x to close the form and get the error.
I don't see how I can get error handling as you suggest for this scenario. It seems to me the only way to avoid the error as you are suggesting, would be to use the Me.undo command after the user deletes the garbage text, but I don't think Access will detect the deletion..?
Anyway, the reason I want to put this in the On Error event of the form is because I have a couple more drop downs that become visible once the first drop down is populated and the user could make the same actions causing the same problem; so rather than enter separate error checks for each drop down, I'd like to just enter one piece of code that looks for this one error type and acts accordingly..
This error is unlikely, but it could happen and I'm trying to head off everything I can think of..
well just try it in the error event - see if it works
offhand i think you have to use accesserror code, not the error object in there (see the sub header - that includes accesserror)
you have to use response = something (acdataerrcontinue is normal, i think) to tell access that you have handled the error, and dont want the normal error to pop up.
I am also not sure offhand how the normal programme flow continues from here - i dont think the sub that caused the error continues to run.
As Bob says, its fairly rare to use the general error handler - most errors can be handled in more targeted ways.
but if you want to, just try in the error event
I think you get the description relative to the accesserror with Error(accesserror)
Code:
if accesserror=94 then
msgbox "myerror94handler"
response = acdataerrcontinue
else
msgbox "unexpected accesserror " & accesserror & vbcrlf & _
"Desc: " & error(accesserror)
end if
-----------
re Bob's point.
you don't HAVE to undo the edit - instead of running the undo, you just test in the controls before_update event with the following code. The point is, you can handle this sort of issue at an atomic stage.
The things you need the generic error event for are things like
- you try to enter values that would give a duplicate record, which is prohibited by a unique key - now, in the before update event you could specifically check to see if the values already exist, with a dlookup, and if so respond with the sort of code below. But you can just let access try to insert the record, which fails, and gives you a "access was unable to add the record type error". Now THIS situation is a candidate to intercept with the generic error-handler and replace the message with a more user-friendly one.
Code:
sub myfield_beforeupdate(cancel as integer)
'note i prefer this nz test, to an isnull test
if nz(field,vbnullstring)=vbnullstring then
msgbox "You Must enter a value"
cancel = true
exit sub
end if
end sub
or in the forms update event (as a validation type thing)
Code:
sub form_beforeupdate(cancel as integer)
'note i prefer this nz test, to an isnull test
if nz(field,vbnullstring)=vbnullstring then
msgbox "You Must enter a value for myfield"
cancel = true
myfield.setfocus 'optional
exit sub
end if
end sub
well just try it in the error event - see if it works
offhand i think you have to use accesserror code, not the error object in there (see the sub header - that includes accesserror)
you have to use response = something (acdataerrcontinue is normal, i think) to tell access that you have handled the error, and dont want the normal error to pop up.
I am also not sure offhand how the normal programme flow continues from here - i dont think the sub that caused the error continues to run.
As Bob says, its fairly rare to use the general error handler - most errors can be handled in more targeted ways.
but if you want to, just try in the error event
I think you get the description relative to the accesserror with Error(accesserror)
Code:
if accesserror=94 then
msgbox "myerror94handler"
response = acdataerrcontinue
else
msgbox "unexpected accesserror " & accesserror & vbcrlf & _
"Desc: " & error(accesserror)
end if
-----------
re Bob's point.
you don't HAVE to undo the edit - instead of running the undo, you just test in the controls before_update event with the following code. The point is, you can handle this sort of issue at an atomic stage.
The things you need the generic error event for are things like
- you try to enter values that would give a duplicate record, which is prohibited by a unique key - now, in the before update event you could specifically check to see if the values already exist, with a dlookup, and if so respond with the sort of code below. But you can just let access try to insert the record, which fails, and gives you a "access was unable to add the record type error". Now THIS situation is a candidate to intercept with the generic error-handler and replace the message with a more user-friendly one.
Code:
sub myfield_beforeupdate(cancel as integer)
'note i prefer this nz test, to an isnull test
if nz(field,vbnullstring)=vbnullstring then
msgbox "You Must enter a value"
cancel = true
exit sub
end if
end sub
or in the forms update event (as a validation type thing)
Code:
sub form_beforeupdate(cancel as integer)
'note i prefer this nz test, to an isnull test
if nz(field,vbnullstring)=vbnullstring then
msgbox "You Must enter a value for myfield"
cancel = true
myfield.setfocus 'optional
exit sub
end if
end sub
I'm pretty confused. I don't know where you are saying to add the code (on what 'On' event?) and I also don't see how the code will pre-empt the run time error #94 that I'm getting. I have tried putting a msgbox code in all relevant 'On' events (Close, unload, Before Update, Error) and it never gets activated.
Perhaps if you create a sample form with a drop down, make it a required field and then perform the action I spoke of it might help for you to see what I'm up against? Either I am misunderstanding your solution or you are misunderstanding my problem?
I do appreciate the assistance though. This seems simple. It seems like I should be able to just pre-empt any errors with the form's On Error event, but that doesn't work..
