Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Dec 2005
    Posts
    67

    Unanswered: Validating user input from a form in VBA

    Hi - I would like to ensure that the data entered by a user into a text contains 6 numbers and one character at the end in MS Access 2003 from a form using VBA. How would I do this? Please help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd try to set format mask in the table definition for that row

  3. #3
    Join Date
    Dec 2005
    Posts
    67
    Thanks - but how would I format it? I mean what do I say in there?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tasha123
    Thanks - but how would I format it? I mean what do I say in there?
    Hi

    Check out "Input Mask" in Access help. There is a page of valid characters in there. You can build up expressions based on these

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    67
    Thanks quite helpful.

  6. #6
    Join Date
    Dec 2005
    Posts
    67
    Hi - sorry it's me again! Re - the input mask. Does anyone know how I may personalise the error message as the one that's generated by Access may seem ambiguous to users.

    It comes up with "The value you entered isn't appropriate for the input mask '00000L' specified for this field". Is there any way I could change it to something more meaningful?

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well... no. I believe you would have to write your own validation function and check it by hand at the form level, then provide a custom messagebox.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2005
    Posts
    67
    Thanks, I've got yet another question.

    I've done some validation so that when the user goes against the validation rule, a message comes up. The problem is the cursor still goes on to the next field in the form. I want the focus not to move if the user has done something wrong. I used on lostfocus() so that it would be after the user had tabbed that the error message would come up. How do I get back to the field the user just left? (Hope this is clear enough?)

    Thanks for your help so far.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can place some code in the controls lost focus event, and then force the focus back to the control if its invalid.

    however its not neccesarily a good solution. The major problem is that you could have 2 columns adjacent to each other both with a validation rule that is triggered on lost focus. if you attempt to move between controls both will fire their lost focus event, if both are invaild you will loose control of the app as it toggles between 2 controls.

    a smarter solution is to consider changing the background colour of controls which are invalid.

    alternatively you place your validation code in the forms before update event and then display a message detiaing the problems.

    you can place an error message in each invlaid control tooltip.

    I'm sure there are many other options, just my 2 1/d's worth

  10. #10
    Join Date
    Dec 2005
    Posts
    67
    You're a gem! Thanks. Now why didn't I think of that re - a smarter solution is to consider changing the background colour of controls which are invalid.

  11. #11
    Join Date
    Dec 2005
    Posts
    67
    Hello again

    the data being entered into the form is not being saved to the underlying table unless I close the form and reopen it. Is there a way for me to make it save when the user gets to the end of the record without closing the form?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry, don't understand

    if you are using bound controls (and I'm pretty sure you will be) then Access automatically saves records if you close the form, or if move to a new record.

    if you want to save the current record then just click on the grey margin to the left hand side of the current record and change the pencil icon to a triangle.

  13. #13
    Join Date
    Dec 2005
    Posts
    67
    When the user gets to the end of inputting data, I set it so that if they say yes they want to continue, it goes back to the beginning of the form fields and they could enter more data. At the moment, when the focus goes back to the first field and I check the table, the data for the previous record is not in there until after I close the form and reopen it. This is a bit worrying as I don't expect the user to keep opening and closing.

    From your response, do you think I should do a 'new record button instead?'

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you check it, is it in a combo box?

    are you using unbound or bound forms?

  15. #15
    Join Date
    Dec 2005
    Posts
    67
    I think 'bound' as in each text field has a control source in an underlying table. Is that what you mean?

    I check it by going into the database and opening the table to see if the data is in there.

Posting Permissions

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