Results 1 to 12 of 12
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    VBA Data Validation And Best Practices

    I'm trying to decide on how I'm going to design my vba validation subs/functions and modules for my project.

    A couple of tables will require more than just simple validation rules to make sure my data is nice and clean. I figure that for each one of these tables I will create a module called [TableName]_Module or something like that. Within the module will be all the functions / sql I need to validate a record. I figure this way if I want to validate from any number of forms I can always call the same validation procedure in my tables validation module.



    1) Do you guys approach it like this or just put it all in the form's code?


    2) My current setup is my validation function has a parameter for each field of the table, defined with ByRef
    When I go to validate a record from a form's BeforeUpdate event, is there a way to just send the whole row as a parameter to another function rather than listing out each field?
    Code:
    Dim v as boolean
    v = validate_my_data( Me!row )
    or something like that...

    3) my functions accept paramters like Function validation_function(ByRef some_field AS Long) AS Boolean
    I call them from the form's code like this: some_boolean = validation_function( Me!some_field )
    I thought because the function was a ByRef that my function could edit the some_field data in place. This is not the case. Poking around it looks like it's passing a text box object from the form. If I call my function like this: some_boolean = (validation_function( Me!some_field.Value )) it passes the value but changes made from the function still aren't reflected back at the form.
    How can I pass a field from the form to my module's function that would allow my module to change value of the field 'in place'?

    At the moment I'm having to change my module's function to return a variant array, the first value being true/false for valid or not, and the remaining values being the "adjusted" fields. It would be cleaner if my module's function could change the fields value's in place byref.

    3.5) Combining #2 and #3 can I just pass whole records as one variable to my function and they be editable 'in place' from my function

    4) Any generic advice, tips tricks, favorite practices, whatever, when it comes to this subject?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    1. Since I seldom (probably never) have multiple forms bound to the same data, I just do validation behind the form.

    2. No.

    3. Can pass form and control names then reference them to set control's value. It may even be possible to pass form and control as VBA objects but I've never done that.

    3.5 Can 'send' a unique record ID or even a recordset object to another procedure. However, record must already be committed to table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Thank's, June.

    2. Damn!
    3.5 Not sure that'll work as I'm trying to do some validation procedures before records are committed.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I'm trying to do some validation procedures before records are committed.
    In that case, you would be better off doing the validation in the form; the Before Update event is a good one to use, because it can be cancelled.

    From June7: It may even be possible to pass form and control as VBA objects
    Yes, that is easily done.

    A subform first line would look like this: Sub MySub (frm as form), and you would use it like this: MySub Me or Mysub Forms!Myform

    It similar for a control: Sub MySub (ctl as Control) and MySub Me!SomeControl or Mysub Forms!Myform!SomeControl

    But I think you would find it easier (and more flexible) just to include a validation sub in each form.

    Here is an example of how I use the Before Update event for validation:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      On Error GoTo ProcErr
      '
      ' Verify if still set to Country Number 1, Aden
      '
      If [COUNTRYNUM] = 1 Then
        If MsgBox("Country is still set to ""Aden"". Are you sure?", vbYesNo, "Has a country been selected?") = vbNo Then
          Me.Undo
          Cancel = True
          Exit Sub
        End If
      End If
      '
      ' Has First been entered?
      '
      If [FIRST] = 0 Then
        MsgBox "No value was entered for ""First"""
        Cancel = True
        Exit Sub
      End If
      '
      ' Has Condition been entered?
      '
      If IsNull([condition]) Then
        MsgBox "Condition was not specified"
        Cancel = True
        Exit Sub
      End If
      Exit Sub
    ProcErr:
      MsgBox "BeforeUpdate Error " & Err.Number & ": " & Err.Description
    End Sub

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I think you underestimate the size of my validation procedures! But my modules functions are called from the forms before update event...

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    But my modules functions are called from the forms before update event...
    So all you need to do then is have those validation functions (and they should be functions, not subs) return a value to indicate a validation error has occurred. Typically, this could be any non-zero value indicates an error (but you can choose whatever you want, really). Then, have the before update event procedure check for an error code, and if it gets one, cancel the event. Something like this:

    Code:
    If MyValidationFunction(me) <> 0 then
       Cancel = -1    ' Any non-zero integer value for Cancel is fine
       Exit sub
    Endif
    Since you are passing the form object itself to the function, you can access all the controls on it as you normally would in VBA.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    That's what I'm doing. What I was asking was is if their is a clean way to pass references (pointers) of a form's fields to the function such that the function could manipulate them 'in place'. As it is now I'm passing ByVals and having to change my module's function to return a variant array, the first value being true/false for valid or not, and the remaining values being the "adjusted" fields. (Adjusted values in this case are just numeric fields that the function makes sure are constrained to some upper and lower bounds)

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can pass just about any valid variable or object to a function AFAIK. I have passed forms, controls, recordsets, worksheets, workbooks, etc.
    For repetitive tasks, I try to write functions in standard modules that can be used anywhere in the project. If your validation is simply Null or "" then that's fairly simple. If you require quantifiable validation, then I'd try to pre-visualize what that will look like and create the function accordingly, or create as few as possible that will do all those tasks. This overly simplified example might look like
    Code:
    Function ValidateEntry (ctl as Control, var as Variant) As Boolean
      If ctl >= var Then ValidateEntry = True
    End Function
    and the call might be (when the control contains 9)
    Code:
    If Not ValidateEntry(Me.txtQty,10) Then 'pass the control and test - assume the goal is to ensure the control value >= 10
      Cancel= True (because the function returns false
    End If
    Some might wonder why I wrote a variant. Trying to illustrate that such a declaration could handle text, numbers, nulls or even objects, making the function fit a wide variation of supposedly identified validation tasks.
    For me, the issue of validation can be quite complex or simple. The fact that (as I mentioned) you can pass so many things to a function makes it a very powerful tool. While I'm at it, your multi-use function might also make use of the Optional keyword. For example, if you find out that altogether you sometimes want to check 2 aspects and sometimes 3, you might make the occasional one optional and either pass it or don't.
    If I understand this intent
    is a clean way to pass references (pointers) of a form's fields to the function such that the function could manipulate them
    I'd say pass the whole form and alter its controls, or pass a recordset, update it in the function and return the recordset.
    Last edited by Micron; 09-07-2017 at 09:42 PM. Reason: added code tags
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    is there is a clean way to pass references (pointers) of a form's fields to the function
    From my post above:

    For a control: Sub MySub (ctl as Control) and MySub Me!SomeControl or Mysub Forms!Myform!SomeControl

    In MySub, you can manipulate the control properties and values just as you would in the form's form's code module, for example ctl = "This is new text" will change the text in a textbox. There is no need to worry about ByRef or Byval, or returning edited values in an array.

    The advantage is MySub does not have to be in a form module - it can be in a general module, and I think that is the question you are asking.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Ah, thanks John & Micron. AS Control was what I needed! As long or as variant weren't cutting it. Interestingly though, when I was watching the variables in my functions the watch window would sometimes tell me they were passed as textboxes, and sometimes weren't.

    Micron, when you say pass the whole recordset do you mean every row in the form's recordset? That could be a lot in some instances. What about just the current row?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes, the entire recordset. To pass a "row" could be a one record recordset. Otherwise I don't know how. Maybe pass a form property like CurrentRecord?

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You pass a recordset to a sub the same way you do a control or a form; the sub will be Sub MySub (rst as Recordset) and MySub rstMyRecordset.

    All it does is pass the reference to the recordset, not the data, so that is not an issue. The sub can manipulate the recordset just as any VBA code does.
    If you are talking about the "Current row", I have found that if your "main" code positions the recorset at the record you want, and then you call
    MySub rstMyRecordset, the sub does retain the position in the recordset, so as long as the sub does not move the recordset "pointer" (e.g. rst.MoveNext), you are in effect working with the "current record".

    I have code lines that look like this:

    Public Sub Auto_Transfer_Duplicate(rst As Recordset, Optional Remainder As Boolean = False) to call the sub,

    and

    NewDuplicates = (rst!Quantity - rst![ToMain]) - Nz(rst![ToDuplicates], 0) in the sub to work with the "current record"

    The "current record" can be updated in the sub, too:

    Code:
          rst.Edit
            If Not Remainder Then
              rst![ToDuplicates] = rst!Quantity - rst!ToMain
            Else
              rst![ToDuplicates] = rst!Quantity - 1
            End If
          rst.Update
    All of these work perfectly for me, so I don't think you should have too much difficulty.

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

Similar Threads

  1. Replies: 8
    Last Post: 10-13-2014, 12:19 PM
  2. Replies: 14
    Last Post: 01-07-2014, 04:20 PM
  3. Best Practices
    By Sinkerhawk in forum Access
    Replies: 2
    Last Post: 06-30-2011, 11:44 AM
  4. Data Validation using VBA
    By Cheshire101 in forum Programming
    Replies: 3
    Last Post: 05-10-2011, 08:43 AM
  5. Data Validation - Please help
    By larry S in forum Access
    Replies: 0
    Last Post: 11-16-2008, 10:24 PM

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