Validating data in one field based on data in another field (1 Viewer)

Pat_D

New member
Local time
Today, 15:15
Joined
Jan 15, 2019
Messages
1
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
 

June7

AWF VIP
Local time
Today, 14:15
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 09:15
Joined
Jul 4, 2013
Messages
2,770
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2002
Messages
42,976
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.
 

Solo712

Registered User.
Local time
Today, 18:15
Joined
Oct 19, 2012
Messages
828
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
 

Users who are viewing this thread

Top Bottom