A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

specific text/number problem



 
 
Thread Tools Display Modes
  #1  
Old April 6th, 2005, 10:21 PM
adrian
external usenet poster
 
Posts: n/a
Default specific text/number problem

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
  #2  
Old April 7th, 2005, 12:38 AM
Graham Mandeno
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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



  #3  
Old April 22nd, 2005, 08:01 PM
adrian
external usenet poster
 
Posts: n/a
Default

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" wrote:

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" wrote in message
...
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




  #4  
Old April 22nd, 2005, 09:31 PM
tina
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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" wrote:

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" wrote in message
...
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






  #5  
Old April 23rd, 2005, 12:54 PM
adrian
external usenet poster
 
Posts: n/a
Default

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
  #6  
Old April 24th, 2005, 11:56 AM
tina
external usenet poster
 
Posts: n/a
Default

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


"adrian" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem Retrieving Printer Object Information Beverly R Setting Up & Running Reports 2 May 16th, 2005 06:02 PM
Problem Updating New Messages from NTTP News Server OE Chad Harris Outlook Express 19 February 7th, 2005 08:21 PM
Office XP Update Problem Bill General Discussions 0 December 1st, 2004 07:11 PM
Problem with display of header in Word 2003 Padser Page Layout 0 October 8th, 2004 07:47 AM
Big Problem. We need the fix Captain K Calendar 0 July 8th, 2004 04:50 PM


All times are GMT +1. The time now is 07:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.