Validating Text

[DBSSP]

How would you write the code to validate user entered text in 2 text boxes based information entered into a table, and once validated, have it move on to open the next form or entry screen? I'm new to this SQL stuff, and the help would be very much appreciated.

Jay

 

[THoey]

I am sorry, but I am not sure I understand. Please give a bit more detail... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[WildHare]

Jay, what do you mean by "validate"

1) Pre-exist in other table?
2) Follows logical rules?
3) Follows business rules? (often at odds with #2.. )
4) Other??

This isn't SQL per se, it's database design. But it all comes out in the wash.

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[DBSSP]

Sorry about my late response people, it's the weekend after all. The information is stored in table, when the user inputs the information, I need the input to be checked against the table. If the information is valid the user will taken to the next form, if not then an error message would be displayed. The thing I cannot figure out is how to get the input checked against the table.

Jay

 

[WildHare]

The information is stored in table, when the user inputs the information, I need the input to be checked against the table.

In what way?

If the information is valid

How will that decision be made?What determines validity? PRE-existence? NON-existence?

It sounds like what you want to do is have the user type something in a text box, say "123ABC", and then look in your table, and see if "123ABC" is there in a record somewhere, and if it is, c.b., go to Form2, if it's not, display message and punish user for keystroke error and start the whole awful mess over again, right?

Might I suggest a better way? If the VALID values are already stored in a table, display them in a combo box and allow the user to SELECT one to work with, and go from there. You remove the element of error on the part of the User.



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[DBSSP]

It is based on pre-existence. I talked to my manager about doing it with a combo box before. His answer, "Security protocol states that the information is too sensitive to display in a group, such as a list box." These inputs of an extremely sensitive nature, (eg. account numbers, SSN, etc.) Please don't shoot me, just doin what the boss orders. It would have been much easier your way though. Hence the question.

Jay

 

[WildHare]

OK, well in that case I have a few more questions:

Are the two fields in question the Key of the table?

Is the table LOCAL or in a linked Back end DB?

Will BOTH values be needed?




Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[DBSSP]

I haven't finished creating the table so I'm not sure which way to go on that yet. More than likely, both fields will be keys. The table will exist in the database but it will have very specific permissions set and both values will be needed. How is that for "clear-as-mud"? lol

Jay

 

[DBSSP]

This table is local, there will be one key field, and both values entered are needed. Anybody, have any idea on how to code this?

Jay

 

[WildHare]

There are basically three types of data validation available to you - mask validation, where the data being entered must conform to a particuar character/position orientation, domain validation, where the data being entered must pre-exist in (another) table of valid entries, and instance validation, where the data in one field must compare logically to values in another field of the same record. This last guy is a little tricky to do, because you need to make sure you trigger the evaluation any time the data is changed. Instance validation in some ways violates normalization rules.

Mask validation follows the available Access character options, such as Character only, character or space, or specific character. Those are simple enough to code in the table design mode.

Domain validation requires that you attempt a lookup-match of a piece of data being entered against another table. This is the "foreign key" concept.

After you get your text value, you can use the DLOOKUP function to make sure it exists in the foreign table. This process usually assumes the the value being looked up, while not necessarily a key in the base table, is indeed a key value in the lookup table:

Dlookup("ThisField", "ThatTable", "ThatTable!Key = " & ThisField)

or code similar. Be sure to surround text values with single quotes:

Dlookup("ThisField", "ThatTable", "ThatTable!Key = '" & ThisField & "'&quot

Instance validation simply follows some logical test to establish correctness:

eg. if SEX = "M", "Pregnant" must be FALSE
or, if PREGNANT = "TRUE", "Sex" must be "F"
if AGE < 16, DriverLicenseNumber must be NULL


Does this get you started?













78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[DBSSP]

This helps, I will probably be asking for more though.

Thanks!

Jay