Results 1 to 7 of 7
  1. #1
    planteg is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3

    Question Doing validation in code VBA

    Hi,

    Sorry if the question is not clear. I am returning to Access after a long pause. What I wish to do is:




    1. block auto-save of records, instead use a button on the form
    2. validate fields content inside the code attached to the button
    3. turn off validation at the field level on the form and at the table level


    Point no 1 is easy to do. But point 2 and 3 are turning to a nightmare. A field is not required but if there is a value, it has to be a number. A second one is required and hat to be a number, and a third one is simply required.

    Thing is when a user goes to a different field, the auto-validation quicks in and and displays loads of not specific messages. How can I get rid of this auto-validation completely and do validation myself in VBA ?

    Many thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you want complete control, you're probably looking at an unbound form. More work though. I can't think of a way to "turn off" the validation that would happen if you typed "abc" into a textbox bound to a numeric field and tried to leave the textbox. I suppose you could use unbound textboxes with hidden bound textboxes, and copy values as appropriate during your validation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you are talking about validating boxes on a form for 1 record:
    usage:
    if IsValidForm() then 'do stuff
    'run data process macro
    else
    'user must correct entries
    end if


    Code:
    public Function IsValidForm() as boolean
    dim vMsg
    Code:
      select case true
           case txtName = "" 
              vMsg = "Client Name is missing"
           case isnull(cboState )
              vMsg = "State is missing"
       cboState.setfocus
      end select
      if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
      IsValidForm =vMsg =""
    end sub

    If you are talking about validating the values in the table, you should use queries to validate. (not code)
    Q1 can verify if field is numeric
    Q2 can check if date < today
    etc.

    the query also shows the validation checked:
    in Q1: Caption: Xfield must be numeric

    put all the queries into a macro, then run macro.
    if all records are zero then all data is valid.

  4. #4
    planteg is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3

    Question

    @pbaldy,

    I like the idea of unbound controls, didn't know they exist.

    Thing is I can't get the contents of an unbound text box, I always get Null for ControlName.Value. I am using Access 365 (Family), what is wrong ?

    Thanks

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Bound or unbound shouldn't matter:

    Me.ControlName

    should get the value within (.Value is the default so doesn't need to be specified, though it doesn't hurt anything). What is your actual code, and what event is it in?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    planteg is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3

    Question

    @pbaldy

    My bad. The problem was when an unbound field was Null, I was getting "invalid use of Null". Then what I did was to change the default value of Text Boxes to "". The Combo Box also showed me this error, then I first made sure the Value was not Null before getting .Value.

    Thanks for your help !

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validation Using Code
    By Eranka in forum Access
    Replies: 7
    Last Post: 06-13-2018, 05:41 AM
  2. Replies: 3
    Last Post: 10-16-2014, 08:49 AM
  3. Help with Validation code on exit event on a form
    By thebionicredneck2003 in forum Programming
    Replies: 2
    Last Post: 05-17-2013, 06:04 AM
  4. Data validation code, I need a better solution.
    By Phred in forum Programming
    Replies: 10
    Last Post: 10-26-2012, 03:26 PM
  5. Replies: 4
    Last Post: 05-24-2011, 08:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums