Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: problem wtih validation of feild.

    Hi,
    My project is in MS Access 2002.
    In that one form of data entry I am using this code for validation of feild.

    Private Sub CustomerCode_Combo_LostFocus()
    If Me.CustomerCode_Combo = "" Or IsNull(Me.CustomerCode_Combo) Then
    strMsg = "You must select Customer Code from the ComboBox."
    strTitle = "CustomerCode Required"
    intStyle = vbOKOnly
    MsgBox strMsg, intStyle, strTitle
    Me.CustomerCode_Combo.Undo
    Me.CustomerCode_Combo.SetFocus
    End If
    End Sub


    Its not working properly.
    only First time whenever I lostfocus of this feild and go to next field to this field then it shows msg whatever I enter in validation.But even if I used that line
    Me.CustomerCode_Combo.Undo
    Me.CustomerCode_Combo.SetFocus

    its not setfocus to that feild again and its useless.
    And I can go further even validation is not correct.
    I don't know what's the problem?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally speaking the best place to do form/control validation is the before update event. If validation fails you add:

    Cancel = True

    and the update stops.
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    If you put the validation into the table them you don't need to write code
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And you can also put the validation into the controls for the form too, but you lose control of the messaging and what happens etc.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    yes startrekker you are right

    but if put in the table when you create a form they are copied to the form for u.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not if you create forms by copying existing templates
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2007
    Posts
    49
    Ya I know the thing that I can put validation in table.But I have one problem that I am using the table which already have very very large amount of data in it,In that some fields left blank, bcz my company was using Excel sheet only for entry directly and now I have to use that data in Access.so I can't put validation in table.So I have to put validation rule in Form only.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ermm nasty, do you are closing the stable doors after the event to stop future horses doing one.

    you are going to have a problem with all your old data, should a user make the mistake of visiting and old record, which depending on your design may then be impossible to leave.

    Not nice.
    if you are going to implement data validation checks then you need to consider what to do about your old data....... in my books if that means creating entries with 'customer code not known'. Ie you define a customer code that unknown. allowing a link to be null isn't going to help you here because you don't want your new records to have no customer code.

    I think you need to think very carefully on what you are trying to achieve here, there is a huge risk that you could inadvertantly introduce data corruption for the best reason, but corruption none the less.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agr33d with healdem.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Dec 2007
    Posts
    49
    Now I know I can't put validation in my table bcz of older data so I am using this code for my form validation.
    But its not working at all.Now its simply enter anything even Null.No validation anymore.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     
     If Me.CustomerCode_Combo = "" Or IsNull(Me.CustomerCode_Combo) Then
      strMsg = "You must select Customer Code from the ComboBox."
      strTitle = "CustomerCode Required"
      intStyle = vbOKOnly
      MsgBox strMsg, intStyle, strTitle
      Cancel = True
      Me.CustomerCode_Combo.SetFocus
    End If
     
    If Me.Date_Text = "" Or IsNull(Me.Date_Text) Then
        strMsg = "You must Enter Order Date."
        strTitle = "ODate Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.Date_Text.SetFocus
    End If
        
    If Me.LONo_Text = "" Or IsNull(Me.LONo_Text) Then
        strMsg = "You must Enter the LO No"
        strTitle = "LONo Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.LONo_Text.SetFocus
    End If
     
    If Me.PONo_Text = "" Or IsNull(Me.PONo_Text) Then
        strMsg = "You must Enter the PO No"
        strTitle = "PONo Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.PONo_Text.SetFocus
            
    End If
     
    If Me.ItemNo_Combo = "" Or IsNull(Me.ItemNo_Combo) Then
        strMsg = "You must Select the Item No from the ComboBox"
        strTitle = "ItemNo Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.ItemNo_Combo.SetFocus
    End If
        
    If Me.Description_Text = "" Or IsNull(Me.Description_Text) Then
        strMsg = "You must Select the Description from the ComboBox"
        strTitle = "Description Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.Description_Text.SetFocus
    End If
     
    If Me.BatchOrLotNo_Text = "" Or IsNull(Me.BatchOrLotNo_Text) Then
        strMsg = "You must Enter the Batch Or Lot No"
        strTitle = "BatchOrLotNo Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.BatchOrLotNo_Text.SetFocus
    End If
        
    If Me.Cartons_Text = "" Or IsNull(Me.Cartons_Text) Then
        strMsg = "You must Enter the Cartons"
        strTitle = "Cartons Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.Cartons_Text.SetFocus
    End If
     
    If Me.PcsOrCarton_Text = "" Or IsNull(Me.PcsOrCarton_Text) Then
        strMsg = "You must Enter the Pcs Or Carton"
        strTitle = "PcsOrCarton Required"
        intStyle = vbOKOnly
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
        Me.PcsOrCarton_Text.SetFocus
    End If
     
    End Sub
    what to do now?

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That looks okay; are you sure the code is firing?
    Paul

  12. #12
    Join Date
    Dec 2007
    Posts
    49
    Ya I am Sure.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok. Set a breakpoint on the first line of your procedure and go and save a record with a null.

    Use F8 to step through the code. Does it run as you would expect? Does it run the Cancel = True line? If so, does it actually save the record or display the error message?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Dec 2007
    Posts
    49
    I did whatever u tell me. Its going code trough but Still validation not working.It should be work in beforeUpdate event of Form.I don't know what a wierd problem ?

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db that exhibits the problem? That process should work.
    Paul

Posting Permissions

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