specific text/number problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adrian - 06 Apr 2005 22:21 GMT

i would like to have a number required in a field, but only when a specific
text is present in another field of the same table using Access

Reply to this Message

Graham Mandeno - 07 Apr 2005 00:38 GMT

Hi Adrian

You can define a validation rune at table level to check for conditions
involving multiple fields. For example:
Not (TextField="ABC" and IsNull(NumField))

However, it's probably neater and more user-friendly to handle it it the
BeforeUpdate event of your data entry form:

If TextField="ABC" and IsNull(NumField) Then
NumField.SetFocus
MsgBox "You must provide a number here if ..."
Cancel = True
End If

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

>i would like to have a number required in a field, but only when a specific
> text is present in another field of the same table using Access

Reply to this Message

adrian - 22 Apr 2005 20:01 GMT

I've just got round to trying it, however it doesn't seem to work. When I try
to use the validation rule it comes up with the error "Invalid SQL syntax -
cannot use multiple columns in a column-level CHECK constraint". When I try
the second option it doesn't seem to anything at all.

Plus, am I supposed to swap TextField for [Item] and NumField for
[ItemNumber] or have I been doing that wrong?

Thanks, Adrian

> Hi Adrian
>
[quoted text clipped - 12 lines]
> >i would like to have a number required in a field, but only when a specific
> > text is present in another field of the same table using Access

Reply to this Message

tina - 22 Apr 2005 21:31 GMT

you entered Graham's table-level validation rule as a *field* validation
rule, that's what the error message is telling you: "cannot use multiple
columns (fieldnames) in a *column-level* (field level) CHECK constraint
(validation rule)".

open the table in design view. on the menu bar, click View | Properties.
enter the validation rule in the Validation Rule space on the General tab.
recommend you also enter a Validation Text, otherwise your users will get a
fairly ugly system-generated error message if they violate the rule.

hth

> I've just got round to trying it, however it doesn't seem to work. When I try
> to use the validation rule it comes up with the error "Invalid SQL syntax -
[quoted text clipped - 22 lines]
> > >i would like to have a number required in a field, but only when a specific
> > > text is present in another field of the same table using Access

Reply to this Message

adrian - 23 Apr 2005 12:54 GMT

this new method works but i am now trying to get the second method to work.
I am trying to get it so that it comes up with the request when you click an
add record button, telling the user at this point that a number is required.
I have added the following text to the button which partly works:

If Item = "Rucksack" And IsNull(RucksackNumber) Then
MsgBox "Rucksacks must have a number"
RucksackNumber.SetFocus
Cancel = True
End If

However when the button is pressed it comes up with the error message before
saying that "Microsoft Office Access can't move the focus to the control
RucksackNumber". It is also refusing to 'add record' when clicked on,
probably because I don't know how my added code and the original code fit
together.

thanks, Adrian

Reply to this Message

tina - 24 Apr 2005 11:56 GMT

> I don't know how my added code and the original code fit
> together.

i'm not sure what code you're referring to. you only need to validate the
record in one place, either at the table level or form level. as Graham
suggested, adding validation code at the form level is neater and more user
friendly, because you can control the situation better. also as he said, you
would add the "If...Then" code to your form's BeforeUpdate event, *not* to a
command button.

suggest you remove the table-level validation rule, then move the form code
as suggested above, and see how that works for you.

hth

> this new method works but i am now trying to get the second method to work.
> I am trying to get it so that it comes up with the request when you click an
[quoted text clipped - 14 lines]
>
> thanks, Adrian

Reply to this Message