Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-15-2019, 12:17 AM   #1
Pat_D
Newly Registered User
 
Join Date: Jan 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Pat_D is on a distinguished road
Validating data in one field based on data in another field

Hi, I am attempting to create a database that holds data about steel.
One characteristic about the steel in this database is its dimensions.
Some sections of steel only need two dimensions (Length and Breadth), while other sections of steel need 3 dimensions (Length, Breadth and Height).
In this table about the steel, I have a field that is named "Dimensions", I have input the data in this field as "00 x 00" or for 3D steel pieces "00 x 00 x 00".
The problem is, the data type for the field of "Dimensions" is Short Text and so I currently have no way of validating the data being typed into these boxes under the field "Dimensions".
However, I want the data validation for this field to be dependent on another field in the table- "Type".
In the "Type" field I have a Lookup Wizard that I have set to have 10 options.
What I am not sure is possible is to be able to validate the "Dimensions" field depending on what is selected in the "Type" field from the dropdown box.
For example,
If "Flat" is selected in the dropdown box under the "Type" field, then the "Dimensions" field would only allow "00 x 00" data to be entered.
However,
If "Channel" is selected in the dropdown box under the "Type" field, then the "Dimensions" field would only allow "00 x 00 x 00" data to be entered.

Is this even possible and does anyone know how to do it?
This would be a great help and it is extremely appreciated.
Thanks

Pat_D is offline   Reply With Quote
Old 01-15-2019, 03:13 AM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,950
Thanks: 0
Thanked 462 Times in 458 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Validating data in one field based on data in another field

I am sure it can be done with enough code. The validation could check length of the input. Code could also change InputMask property to force a particular input structure. The real trick is figuring out what event(s) to put code into.

However, why depend on user to correctly input a text string? What if they don't get the spaces correct or accidently type a z instead of x? Why don't you have 3 number fields instead of the 1 text? Conditional Formatting can manage availability of the Height textbox based on Type.

Be aware Type is a reserved word and should avoid using reserved words as names for anything.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 01-15-2019 at 03:29 AM.
June7 is online now   Reply With Quote
Old 01-15-2019, 01:59 PM   #3
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,115
Thanks: 3
Thanked 458 Times in 451 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Validating data in one field based on data in another field

Validation and data entry would be a lot easier if you implemented normalization and used 3 fields to record the dimensions. It's too easy to concatenate the data into the format you want in forms and reports.

Cronk is offline   Reply With Quote
Old 01-17-2019, 12:13 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,573
Thanks: 13
Thanked 1,460 Times in 1,391 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Validating data in one field based on data in another field

I'm going to go with Cronk. It is ALWAYS better to do it right. And "right" in this case is to use three separate fields for the dimensions. You can add the type field to aid with validation.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 02-09-2019, 10:08 AM   #5
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 737
Thanks: 14
Thanked 125 Times in 122 Posts
Solo712 will become famous soon enough
Re: Validating data in one field based on data in another field

Quote:
Originally Posted by Pat_D View Post
Hi, I am attempting to create a database that holds data about steel.
One characteristic about the steel in this database is its dimensions.
Some sections of steel only need two dimensions (Length and Breadth), while other sections of steel need 3 dimensions (Length, Breadth and Height).
In this table about the steel, I have a field that is named "Dimensions", I have input the data in this field as "00 x 00" or for 3D steel pieces "00 x 00 x 00".
The problem is, the data type for the field of "Dimensions" is Short Text and so I currently have no way of validating the data being typed into these boxes under the field "Dimensions".
However, I want the data validation for this field to be dependent on another field in the table- "Type".
In the "Type" field I have a Lookup Wizard that I have set to have 10 options.
What I am not sure is possible is to be able to validate the "Dimensions" field depending on what is selected in the "Type" field from the dropdown box.
For example,
If "Flat" is selected in the dropdown box under the "Type" field, then the "Dimensions" field would only allow "00 x 00" data to be entered.
However,
If "Channel" is selected in the dropdown box under the "Type" field, then the "Dimensions" field would only allow "00 x 00 x 00" data to be entered.

Is this even possible and does anyone know how to do it?
This would be a great help and it is extremely appreciated.
Thanks
You can check the valid form by placing this into the form's BeforeUpdate event. You might need further checking for numeric validity, but this provides the basic test you are asking for.
Code:
Sub Form_BeforeUpdate(Cancel As Integer)
    Dim i as Long
    i = Len(Me!Dimensions) - Len(Replace(Me!Dimensions, "x","")) 
    If Type = "Flat" Then 
        If i <> 1 then 
             MsgBox "Invalid dimension formula for flat steel!"
             Cancel = True
             Me.Dimensions.SetFocus
             Exit Sub
    ElseIf i <> 2 Then 
       MsgBox "Invalid dimension formula for channel steel!"
       Cancel = True
       Me.Dimensions.SetFocus
       Exit Sub   
    End if    
End Sub
Best,
Jiri

__________________
Dyslexics of the world untie !
Solo712 is offline   Reply With Quote
Reply

Tags
input mask , table , validation rule

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] How do I collect data based on the same data in one field but differ data in another wheddingsjr Queries 5 01-08-2018 10:57 AM
How Mark disappear field data based on field data last aftfm Queries 1 10-15-2014 09:38 PM
Creating a Lookup Field that Displays Data based on Other Field's Selection spora Tables 5 01-11-2013 12:05 AM
Validating data using a range in another field dash68 Forms 3 03-02-2010 10:27 PM
Validating 2 field's data sdawson Forms 4 04-04-2007 12:52 AM




All times are GMT -8. The time now is 06:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World