Results 1 to 14 of 14

Data validation on a field on a form using another field on that form as a "Field Type"

  1. #1
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    25

    Data validation on a field on a form using another field on that form as a "Field Type"

    Hi Folks

    I am looking at a Data Entry form that I have. It has a Description that is filled in when opening the form and a Result that the user will be enter as a result of there inspection. This form is linked to a table. I have another field in the table called FieldType that defines the data type that should be in the Result field. For example one maybe be Pass or Fail another maybe a number I have attached a picture of the form I am working on. I plan on hiding the fieldtype field after I get this working.



    I am wanting to do this data validation on the Result Field using the FieldType as my Data Type Validation.

    So if the user puts in Pass or Fail and the field Type is PassFail it should think thats ok I have tried iif statements and events after updates but having no luck. Some other field types are Numbers meaning what would should be entered in the results should be a number I also have one that is Ok or Low.

    Any help is appreciated. Please see the picture attached.
    Attached Thumbnails Attached Thumbnails FieldType.JPG  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,969
    If a field is number type then only numbers can be input. If it is yes/no then only only true/false can be entered (btw, 0 is false and any other number input will be translated as true).

    You have a single text type field called Results and you are allowing different types of data? All data will be text type.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,647
    Building on June7's info, I think you'll have to code this yourself, not use a validation rule (if that's where you were headed). I'm visualizing a Select/Case structure where the case is the value of the type field, then within is the appropriate test. In other words, for the Pass/Fail type you'd make sure the value was either "Pass" or "Fail". For a number test, you'd make sure the value was numeric. The logical place to do it is the form's before update event:

    http://www.baldyweb.com/BeforeUpdate.htm
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,291
    Why not just have the user pick from a combo and control this by design rather than vba code? A table for valid values for the observations could be the row source for the combo, or one table per combo. Impossible to say without knowing more. I'm guessing there is something about each record that could filter or build the list items so that 1 or 2 isn't in the list when Pass or Fail are the appropriate choices. It appears that this is a continuous form so I think that would be important.

    Or can you not control the row source of individual combos in a continuous form? If not, then I think controlling the type of data for individual textboxes on a continuous form would be just as problematic, no?
    Last edited by Micron; 09-13-2018 at 06:51 PM. Reason: added questions
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  5. #5
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    25
    Hello

    I am using a Text field and I have this form for use in the field as an inspection sheet they will be entering Pass or Fail on some items and Ok or Low on others and Numbers for others I thought it best to use 1 field called results with Text type and then using validation / code to make sure they put in the correct type for the item they are inspecting. That will then trigger maintenance accordingly.

    I did think I could use If statements in the before update but not sure how to make the Me.Result control to allow the type set by the Me.FieldType.

    I tried this but it doesn't work.. Not really sure what's wrong with it.



    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.FieldType.Value = PassFail Then
    Me.Result.Value = Pass Or Fail
    MsgBox "Incorrect Inspection Value"
    Cancel = True
    Me.Result.SetFocus
    End If
    End Sub

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,291
    You can't code like you think. First, any comparison must include the thing being compared on both sides of the AND / OR part. Secondly, you are creating two conditions that need to be checked, not one. Thirdly, any text or date value involved in an expression must be delimited so as to distinguish it from an object or variable name. As in

    Code:
    If Me.FieldType = "PassFail" Then
      If Me.Result <> "Pass" OR Me.Result <> "Fail" Then
        Msgbox "Incorrect Inspection Value"
        Cancel = True
        Me.Result.SetFocus
      End If
    End If
    Note that you don't need .Value as it is the default property of the control, but it isn't wrong to use it - it's just uncommon with experienced vba programmers.
    If this works for you, then I'd suggest a UDF (user defined function) might cut down on having to write the same code for every control for this exercise. Let's see if it works before going there. You'll notice the post and the email won't be the same. I just noticed it appears you had the wrong comparison operator for your logic (=) or at least that's the way it ended up in my version. I changed it to <>.

    Forgot: please use code tags with indentation for anything more than a line or two. It makes it easier to read. Also could mention that date delimiter is #.
    Last edited by Micron; 09-14-2018 at 06:30 AM. Reason: code corrections
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  7. #7
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    25
    Hi I tried this and it does make since however. When I put in Pass and then tabbed to next input and got the message box it was incorrect even though it was PassFail in the FieldType. Should I put this on the form level before update?

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)If Me.FieldType = "PassFail" Then
      If Me.Result <> "Pass" Or Me.Result <> "Fail" Then
        MsgBox "Incorrect Inspection Value"
        Cancel = True
        Me.Result.SetFocus
      End If
    End If
    End Sub

    I do have a few Types so far I have.
    FieldType
    Number
    OkFail
    okLow
    OpenClosed
    PassFail

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,647
    I'd use Select/Case, but you need And rather than Or. Think about it.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    25
    Thanks the And did the trick. I still thought Or should do it my way of thinking if its this or that its still ok...

    I'll buy you a hamburger if its Tuesday or Thursday means your not getting a Hamburger today because its Friday but its a been awhile since college

    Now to figure out how to add the other IF's .. I am not sure how to do the case in VBA.

    Thank you

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,647
    Here, I searched for you:

    https://www.techonthenet.com/access/...anced/case.php

    Or would be correct with =; <> reverses the logic.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  11. #11
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    25
    Thank you all for you help this mess below seems to do what I need. I do wonder if there might be a way to ignore case so if they type in pass or Pass or PASS it still thinks thats ok. Perhaps force cap's on the control?

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.FieldType = "PassFail" Then
      If Me.Result <> "pass" And Me.Result <> "fail" Then
        MsgBox "Incorrect Inspection Value"
        Cancel = True
        Me.Result.SetFocus
      End If
       Else
        If Me.FieldType = "okLow" Then
          If Me.Result <> "ok" And Me.Result <> "low" Then
          MsgBox "Incorrect Inspection Value"
          Cancel = True
          Me.Result.SetFocus
      End If
       Else
        If Me.FieldType = "OkFail" Then
          If Me.Result <> "ok" And Me.Result <> "fail" Then
          MsgBox "Incorrect Inspection Value"
          Cancel = True
          Me.Result.SetFocus
      End If
       Else
        If Me.FieldType = "OpenClosed" Then
          If Me.Result <> "open" And Me.Result <> "closed" Then
          MsgBox "Incorrect Inspection Value"
          Cancel = True
          Me.Result.SetFocus
      End If
       Else
        If Me.FieldType = "Number" Then
          If Me.Result >= 9999 Then
          MsgBox "Incorrect Inspection Value"
          Cancel = True
          Me.Result.SetFocus
      End If
      End If
      End If
      End If
      End If
      End If
    
    
    
    
    End Sub

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,647
    Normally it isn't case sensitive. What is the "Option Compare..." setting at the top of the module?

    http://www.fmsinc.com/microsoftacces...#OptionCompare
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  13. #13
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    25
    Great... setting Option Compare Text
    Fixed that issue.

    Thank you!

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,647
    Happy to help!
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 19
    Last Post: 03-09-2017, 08:48 AM
  2. "Data Type" for Field
    By PATRICKPBME in forum Database Design
    Replies: 5
    Last Post: 02-03-2017, 02:00 PM
  3. Replies: 3
    Last Post: 04-22-2013, 05:08 AM
  4. Replies: 1
    Last Post: 03-03-2012, 09:17 PM
  5. Replies: 7
    Last Post: 01-29-2012, 06:44 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
  •  
Tech Forums: Microsoft Office Forums