specific text/number problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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

Graham Mandeno said:
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
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


adrian said:
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
Click to expand...
Click to expand...
 
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


adrian said:
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

Graham Mandeno said:
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
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


adrian said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
I don't know how my added code and the original code fit
together.
Click to expand...

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
 
Back
Top