Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Intercepting Access error messages!

    Can this be done?

    For example, you have a form that stores value in a numeric field that has validation range set between 1.0 and 3.2!

    The user enters the value of 3.4 and error message from Access pops up saying it violates validation rules!

    That's fine as long as:
    1. your users know what are validation rules
    2. your users speak English
    3. you like the design of error message pop-up

    Neither of 3 above conditions are satisified in my case!

    So, can I intercept Access error message and design my own pop-up saying something like "Vrijednost koju ste unijeli nije unutar dozvoljenog raspona" ("The value you entered is outside allowed range").

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes it can
    you could put the message in the column definition for that item, but you will need to start a new form for those changes to be reflected

    you could put some validation code in the before update event.

    select the form, right click the form (away from any cotnrols), then select build event selet code

    that should bring up the code window, select the before update event

    insert the code
    if <mycontrolname> <1 or <mycontrolname> >3.2 then
    msgbox "Vrijednost koju ste unijeli nije unutar dozvoljenog raspona", vbwarning 'this displays a message.. you may need to refine it for your requirements
    cancel = true ' this stops the record being updated until the fault(s) are fixed
    endif


    change <mycontrolname> to whatever the control containing the value you want validated.
    you could change your message to be a bit more helpful and say effectively field so and so is out of the permitted range 1...3.2

    you can if you prefer place the code int he controls before update event. but if you have more than one validation requirement then you user is going to get mighty hacked off if they get an individual error message for each error. if you have multiple validations paint he msgbox message using a string
    eg
    strmessage "Errors found" & vbcrlf ' vbcrlf starts a new line
    if validation 1 fails then strmessage = strmessage & "field1 failed validation, value was: blah, range is blah to blah" & vbcrlf
    if validation 2 fails then strmessage = strmessage & "field2 failed validation, value was: blah, range is blah to blah" & vbcrlf
    msgbox strMessage, vbwarning

    say you were validating a birthday:
    your error message could be... sorry we cannot accept members of this company if their date of is less than 19
    as opposed to The value you entered is outside allowed range.

    reason it tells the user what the fault is, which item is wrong, and how to go about fixing the fault.

    if you want you could wrap this up into function that tests for accurate values (and returns true or false if the infoamrtion is invalid), and place a call to that function int he controls on lost focus event and the before update

    function isField1Valid () as booelan
    isfieldvalid=false 'set up our default value
    if isnumeric(<mycontrolname>) = false then isfieldvalid=false 'a control that isnt a number is rejected
    if <mycontrolname> <1 or <mycontrolname> >3.2 then exit function
    isfieldvalid=true
    exit function

    then in your controls on lost focus event
    if isfield1valid = false then msgbox "Vrijednost koju ste unijeli nije unutar dozvoljenog raspona", vbwarning 'this displays a message.. you may need to refine it for your requirements


    in the forms before update event
    if isfield1valid = false then
    msgbox "Vrijednost koju ste unijeli nije unutar dozvoljenog raspona", vbwarning 'this displays a message.. you may need to refine it for your requirements
    cancel=false
    endif
    HTH
    Last edited by healdem; 11-28-07 at 13:51.

  3. #3
    Join Date
    Apr 2007
    Posts
    108
    Thanks healdem!

    It doesn't matter in fact if you are coding rules before update occurs or if you are catching the error and remedieng it after it happens!
    It all boils down to either using validation rule in Table definition or using validation rules through code on forms used to enter that data.

    What I was originally asking is available through OnError event of form and you can catch "validation rule error" through if DataErr=3316 but still it doesn't prevent Acces from displaying its "Validation rule" pop up (AND I WOULD STILL LIKE TO KNOW IF IT'S POSSIBLE - even if it's unneccessary).

  4. #4
    Join Date
    Apr 2007
    Posts
    108
    Anyway if someone is interested and was following this topic with interest here's the link that helps!

    http://www.thescripts.com/forum/thread622060.html

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Validation errors? Sounds like another job for... ta-da.... UNBOUND forms
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    You are persistent georgev!

    OK, validations may call for unbound forms, but I found at least one use for intercepting error messages!

    Here it is:
    Access forms don't have true drop down lists, controls available are combo box and list. But you want to have a control one row high (to save screen space) that expands on arrow click and displays all the items in the list - effectively drop down list.

    If you use one row high list you have two miniature scroll up and down buttons and awful looking list.

    If you use combo-box users are allowed to enter data (hence the term combo) - a scenario you don't want because all allowed values are already in the list part of the combo box.
    You can use Limit to List property to Yes to prevent this but then if your users enter text and leave control you get Access Error message:
    The text you entered isn't an item in the list.

    And if your users are unfimiliar with Access and English you wan't to intercept that!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it seems to me that you are beating up Access because of workarounds you are using. I though the non English language versions of Access displayed non English error messages, but I don't know. Certainly the English language version of Access uses lots of non English error message (well American English)

    If you are concerned about it then set the combobox to allow any user input and validate the input yourself. Access isn't a development environment like VB, VC, C++ or whatever, its a hybrid that allows you to develop your application but doens't allow you the degree of control (or for that matter minor technical complications that C++ developers have to face. Its targetted as a RAD and it can be used to deploy db apps very quickly, but it takes a long time to deploy server type apps, because most of the shortcuts in Access are no longer applicable.

    these shortcuts refer primarily to the requirement to use unbound controls, do all the error handling, record locking and myriad of other tasks which are required to make an Access application fit well with a server datastore. Its part fo the reason why many people think (wrongly) that Access is only really a 'toy' db application generator because Access using JET and the slinky controls using bound data runs out of grunt around 15..30 users, where as a well designed Access front End using the traditional server db design paradigm can support thousands of concurrent users.

  8. #8
    Join Date
    Apr 2007
    Posts
    108
    I really don't know if there is Croatian version of Access around!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Riorin
    I really don't know if there is Croatian version of Access around!
    there is for office 2007, and I would expect it to be available in some, mebbe all currently supported versions of office.

  10. #10
    Join Date
    Apr 2007
    Posts
    108
    We don't have that version yet at the place I work and I don't think current hardware would be able to deal with 2007 version!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Riorin
    You are persistent georgev!

    OK, validations may call for unbound forms, but I found at least one use for intercepting error messages!

    Here it is:
    Access forms don't have true drop down lists, controls available are combo box and list. But you want to have a control one row high (to save screen space) that expands on arrow click and displays all the items in the list - effectively drop down list.

    If you use one row high list you have two miniature scroll up and down buttons and awful looking list.

    If you use combo-box users are allowed to enter data (hence the term combo) - a scenario you don't want because all allowed values are already in the list part of the combo box.
    You can use Limit to List property to Yes to prevent this but then if your users enter text and leave control you get Access Error message:
    The text you entered isn't an item in the list.

    And if your users are unfimiliar with Access and English you wan't to intercept that!
    Personally I only ever use combo/dropdowns for data entry -to me they're for data selction only. But we all have our own way of doing things - I just avoid letting Access doing anything itself; hence my love for unbound (love's probably too strong a word... ahem) forms. Granted, it may take a little while longer to code an application, because you have to write the entire control and validation, but I think you end up with a more user friendly interface (because you cotrol all the errrs for a start!)
    And because my wireless eyboard is running out of battery I shall stop there.

    ...that'll be 2cents please
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •