Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2008
    Posts
    5

    Unanswered: Help needed with a simple form

    Hi everybody, I am new to this forum, I signed up as I am seeking some help on what most of you guys think is a very simple form! I have a job interview on Friday and I have to build a simple form that allows the user to input data into a very simple table in the database. Thing is, I haven't used Access since I was at college about 5 years ago and I am getting myself a bit lost

    Like I say, my table is very simple, and it contains the following data fields:-

    Account No, Customer Name, Address, Postcode, Telephone No., Credit Limit and Payment terms.

    I have given the data fields the following types:-

    Account No (text), Customer Name(text), Address(text), Postcode(text), Telephone No.(text), Credit Limit(currency) and Payment terms(text).

    The problem I am having is validating the following:-

    Account No must be 6 characters in length
    Credit Limit <= 10,000
    Payment Terms can only be 30, 45 or 60 days


    The other fields (name, address etc also cannot be left blank)

    I have set validation rules and validation text at table level e.g.<=10000 for the credit limit and it works to a certain point. I really want it to work so that when the user tabs between the text boxes, the validation is tested then.

    To attempt this, I have written a small sub routine in the LostFocus event of the CustomerName textbox:-

    Private Sub CustomerName_LostFocus()
    If CustomerName.Text = "" Then
    MsgBox ("Please enter customer name")
    CustomerName.SetFocus
    End If

    End Sub


    This flags up a message box if the text box is empty, but I want to put the cursor back into that text box using SetFocus, but it's not happening. Can anybody tell me what I am doing wrong. And would it be much easier just to code all the validation that I need for this? Thanks.
    Reply With Quote

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You are on the right path...I would use the AfterUpdate property instead of LostFocus. This way your code will only be ran if something has changed in the control.

    I know that you said that you wanted to validate the data when the user tabs between text boxes but sometimes it's easier to check when the user clicks Submit, OK, etc.

    C

  3. #3
    Join Date
    Feb 2008
    Posts
    5
    So I would just need to use my If statement in the AfterUpdate method of the customer text box? eg:-

    Private Sub Customer_Name_AfterUpdate()
    If Customer_Name.Text = "" Then
    MsgBox ("Please enter customer name")
    Customer_Name.SetFocus
    End If
    End Sub

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Yeah...that should work. I don't have Access in front of me right now to test it though.

    C

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I agree with the original suggestion to do the checking behind a "save" button (or the before update event of the form). The posted code won't work if the user doesn't change anything, as the update event wouldn't fire. Focus events would fire, but not if the user skips that control with the mouse. You can't count on them doing things the way you expect.

    I sometimes use conditional formatting to highlight controls that are required but not yet filled out. It seems to help the users.
    Paul

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    lol

    You guys just love to complicate things don't you??!?!

    All of those can be done with table/control properties. No code is required.

    Keeping it simple... which seems to be the theme of post 1.....

    Account No must be 6 characters in length: Input Mask.
    Credit Limit <= &#163;10,000: Validation Rule
    Payment Terms can only be 30, 45 or 60 days: Validation Rule

    Maybe ask your "potential employer" if they want the simple method or the complex method?

    *sniggers*
    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
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Validation Rules and Input masks work only if the form is bound to the table. Which may or may not be the case.

    Personally, I have never liked the way the input masks "feel" on a form. They always seem clunky. But that's just me.

    C

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Validation Rules and Input masks work only if the form is bound to the table.
    Umm.... nope. You can use them on unbound controls on a form too, which is why I said "table/control"

    I agree though about Input Masks... I personally don't like them either, but they do work.
    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

  9. #9
    Join Date
    Feb 2008
    Posts
    5
    The job won't involve programming in any way, I have never been a programmer, and never will be, but If I get this job, I will be using Access databases and I think the employer just wants to see that I have a knowledge of how they work if that makes sense, so I don't think he will really care what way I do it. I just thought it would be cool to have a working thing which operated completely the way he has asked.

    Like I say, I tried using validation rules and masks at table level, but they don't seem to do what I want them too, so was just looking for maybe one example of code which validates one of my fields, and then I'm sure I could figure out the rest of them myself

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If you're not going to be a programmer, then doing this by code would be a mistake imo.

    My advice would be to get the Validation Rule and Input Mask working.

    Account No must be 6 characters in length: Input Mask: aaaaaa
    Credit Limit <= &#163;10,000: Validation Rule: <=10000 Validation Text: The credit limit cannot exceed &#163;10,000.
    Payment Terms can only be 30, 45 or 60 days: Validation Rule: 30 or 45 or 60 Validation Text: The Payment Terms can only be 30, 45 or 60 days.

    Otherwise you should use the BeforeUpdate property to code a validation routine:

    Code:
    Private Sub CompanyNo_BeforeUpdate(Cancel as Integer)
       If Len(Me.CompanyNo) > 6 Then
          MsgBox "The company number can't be more than six characters"
          Cancel = True
       End If
    End Sub
    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

  11. #11
    Join Date
    Feb 2008
    Posts
    5
    Thanks StarTrekker, appreciate it. I did a bit of VB in college, and I thought the code for doing these validations would be quite simple, so that's why I though I could handle coding them.

    Can you tell me, is there anyway of validating the fields such as CustomerName and Address, using these validation rules? They are just text fields so I'm not sure how to compare them to an empty value if that makes sense, but I don't want the record to be added if they are left blank by the user.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    If Nz(address, "") = "" Then
      MsgBox "Validation error"
    End If
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2008
    Posts
    5
    Cheers georgev

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not with a validation rule... they only work when you enter data into the field / control.

    But the Required property will do it.
    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

Posting Permissions

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