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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |