Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: validate data type for data entry

    I have two fields in data entry form. One is date, another is amount. I would like to validate the correct data type before inserting to database. For date i can use Isdate(txtDate) on lost focus event. What about amount? Is there Iscurrency(txtamount). If there is wrong data type, i am going to give error mesage. Actually i enter the format as date and currency in the data entry form. But the error message is not going to help users. I want to have my own error message such as "Please check the format". Where this message put in? Do i have to use Lostfocus() event? Thank you very much.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's wrong with using input masks?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Input mask are a pain! You not only have to place them for each control, you have to put code in the OnClick for each one, to assure that a user "clicking" on them will go to the beginning of the mask/textbox, and unless you have Access set up to move to the beggining of field on enter, you have to do the same thing for the OnEnter event ofeach field.

    Actually, you can do both validations at the form level, throwing up a customized error message for each one! The error code for entering data that doesn't match the format of a control is 2113, so we do something like this:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 Then 'Data entered doesn't match datatype 
    
     If Screen.ActiveControl.name = "txtAmount" Then
      Response = MsgBox("Data For Amount Field Must Be Entered in Currency Format!", vbExclamation, "Data is Not in Currency Format")
      Response = acDataErrContinue
     End If
     
     If Screen.ActiveControl.name = "txtDate" Then
      Response = MsgBox("Field Must Be a Valid Date!", vbExclamation, "Data is Not a Valid Date")
      Response = acDataErrContinue
     End If
    
    End If
    End Sub
    You can, of course, edit the messages to suit your needs. This method allows you to do datatype validation for an unlimited number of controls in a form, all from one spot, rather than ahving to do it in an event for each textbox.

    Linq ;0)>
    Last edited by Missinglinq; 08-17-07 at 16:20.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2004
    Posts
    660
    Thank you, i am going to try this way. I have input mask. But it gave the message: The value you entered isn't valid for this field. For example, you may have enter text in a numeric field or a number that is larger than the Fieldsize setting permist. I don't this example cofuse the users. That's what i thought. Thanks.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Yes, Access' error messages seldom mean anything to anyone other than the developer. I think you'll find this method much nicer!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Mar 2004
    Posts
    660
    Quote Originally Posted by Missinglinq
    Yes, Access' error messages seldom mean anything to anyone other than the developer. I think you'll find this method much nicer!
    Thanks for the code. I have question, you use sub not function, if i put this code in the save button, it would use function as boolean value, if true it will exit sub for save event. Where would you call this sub? Thanks for your help.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This is a standard Access sub! You don't do anything except copy it in its entirety and paste it in the code module (window) for your form! That's all you have to do, you don't need to call it. It's triggered when the user exits the field after entering data.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Mar 2004
    Posts
    660
    Thank you. I see how it works. The txtAmount works fine. But the date didn't show the message in the code. It showed the value you entered isn't appropriate for the input mask '99/99/00' specified for this field. As i have input mask. I think it will overwrite the message in the code. Am i right? Thanks again.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's a different error code! Let's try combining the two:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 or DataErr = 2279 Then 'Data entered doesn't match datatype 
    
     If Screen.ActiveControl.name = "txtAmount" Then
      Response = MsgBox("Data For Amount Field Must Be Entered in Currency Format!", vbExclamation, "Data is Not in Currency Format")
      Response = acDataErrContinue
     End If
     
     If Screen.ActiveControl.name = "txtDate" Then
      Response = MsgBox("Field Must Be a Valid Date!", vbExclamation, "Data is Not a Valid Date")
      Response = acDataErrContinue
     End If
    
    End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Mar 2004
    Posts
    660
    Quote Originally Posted by Missinglinq
    That's a different error code! Let's try combining the two:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 or DataErr = 2279 Then 'Data entered doesn't match datatype 
    
     If Screen.ActiveControl.name = "txtAmount" Then
      Response = MsgBox("Data For Amount Field Must Be Entered in Currency Format!", vbExclamation, "Data is Not in Currency Format")
      Response = acDataErrContinue
     End If
     
     If Screen.ActiveControl.name = "txtDate" Then
      Response = MsgBox("Field Must Be a Valid Date!", vbExclamation, "Data is Not a Valid Date")
      Response = acDataErrContinue
     End If
    
    End If
    End Sub
    Thank you. You did big help to me. By the way, where did you find the error code such as DataErr = 2279 . Thanks again!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can find error numbers by causing the error to occur.
    Example:
    Code:
    On Error GoTo ErrorHandler
    
    Dim Answer As Long
    
     Answer = 1 / 0
    
    ExitHandler:
     Exit Sub
    
    ErrorHandler:
     MsgBox "Error Number:" & Chr(9) & Err.Number & Chr(13) & _
     "Error Description:" & Chr(9) & Err.Description
     Resume ExitHandler
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As George said, one way is to force the error to occur and note the error number. There are also lists around on the web. Attached to this post is a small db I ran up a while back. You can search by Error Number or by a partial error message. I have a shortcut to it in my Task Bar so it's always available.

    Linq
    Attached Files Attached Files
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Some good tips Missinglinq and Georgev! Thanks for the info.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Mar 2004
    Posts
    660
    Thank you all. These are great!

Posting Permissions

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