Trying to add validation... Please help

[padiq75]

I am trying to make a table in which there are two fields, e.g. field
A and field B.

I want there to be validation so that ---> if field A is empty then
the user cannot enter anything into field B.

In reality one of the fields is a lookuplist which displays options.
The other field is where the user can specify an option if it is not
present on the list. I don't want any records to have both fields
filled...either one or the other..not both...

I hope you understand since i have not described it that well...

Please help.

thankyou

 

[Scott McDaniel]

I am trying to make a table in which there are two fields, e.g. field
A and field B.

I want there to be validation so that ---> if field A is empty then
the user cannot enter anything into field B.

You could disable FieldB when the form is opened (or in the Current event of the form, if you need to do this on each
record), then re-enable FieldB in the AfterUpdate event of FieldA:

Sub FieldA_AfterUPdate

In reality one of the fields is a lookuplist which displays options.
The other field is where the user can specify an option if it is not
present on the list. I don't want any records to have both fields
filled...either one or the other..not both...

I hope you understand since i have not described it that well...

Please help.

thankyou

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

I'm not sure if I understand completly. This sound very unusual! You may
want to explain further to get some advice from other developers.

Your question does not make sense.

"if field A is empty then the user cannot enter anything into field B."
Therefore to enter info into B, A must have a value (thus both fields get
filled). And then you say "I don't want any records to have both fields
filled...either one or the other..not both..." - Which is it??

As for the question, using my judgement. First off, you and your users
should not be manipulating tables, that is why there are form. Now that
you're using forms, you can easily accomplish this using some simple code.

The simplest way is to enable and disable the 2nd control based on the first.

If isnull(Me.Control1) then
Me.Control2.Enabled=false
Else
Me.Control2.Enabled=True
End if

Using the afterupdate event for the 1st control this should work fine.
Also, you'll need to add this to the form's current event as well.

If this isn't what you wanted, explain more and I'll try again.

 

[Graham Mandeno]

At the table design level you can add a validation rule to prevent such
records being saved:

In design view, click View>Properties, then in the Validation Rule box
enter:
[A] Is Null Or Is Null

This is fine as a "backstop" but at the form level you can make things much
more user-friendly by doing a check in the Form_BeforeUpdate event
procedure:

If Not ( IsNull([A]) Or IsNull() ) Then
MsgBox "Here is a friendly reminder to fix the problem"
Cancel = True
End If

 

[padiq75]

Thankyou all for your help.

I will try these solutions out tomorrow.

Sorry that my first post was unclear. What i meant was "if field A is
Thank you again... i will report back tomorrow if i have a problem.

 

[Jamie Collins]

I am trying to make a table in which there are two fields, e.g. field
A and field B.

I want there to bevalidationso that ---> if field A is empty then
the user cannot enter anything into field B.

In reality one of the fields is a lookuplist which displays options.
The other field is where the user can specify an option if it is not
present on the list. I don't want any records to have both fields
filled...either one or the other..not both...

I'm wondering whether a second nullable column is worth the effort.

A seemingly close example that springs to mind is the oft seen
security Q&A when creating an account on a web site: they supply a
list of suggested questions ("First pet's name", "Place of birth",
etc) but the user can also create their own ("Favorite Access MVP's
name"). In your suggested design, you'd have one nullable column for
the predefined questions with referential integrity to a lookup table
and a second column for a user-defined question again nullable. Not
only would you need a validation rule to ensure exactly one of the two
columns has the NULL value, you'd additionally have to check that the
user-defined question is not a pre-defined question (otherwise there'd
be no point in having two columns) plus you'd have a maintenance issue
if you decided to 'promote' a popular user-defined question to your
pre-defined list i.e. values would need to switch columns in the
usage table.

In such a scenario it seems to me better to have just one column in
the usage table with no RI and simply use a query to the lookup table
to determine whether the question is on the pre-defined list.

Jamie.

--