Validation Rule Within Table

[rajeshsarin]

Hi,

I have two fields in a payment table.
1st Field is "Method", the users can only shoose 1 of the four value i.e. Cash, EFTPOS, Direct Debit, Internet Transfer.

2nd Field is "Trans_No" (transaction number).

I want to setup a validation rule so that if users select the value "EFTPOS" in the Method Field, then they cannot leave Trans_No as NULL

Basically something like
IF Method = 'EFTPOS' Then Trans_No IS NOT NULL.

How can I do this, note both fields are within same table.

Regards

 

[Kryst51]

You'll do this at the form level, and use code to perform the check and what you want to happen.

I am not sure of what code to use though.

 

[vbaInet]

Hi,

I have two fields in a payment table.
1st Field is "Method", the users can only shoose 1 of the four value i.e. Cash, EFTPOS, Direct Debit, Internet Transfer.

2nd Field is "Trans_No" (transaction number).

I want to setup a validation rule so that if users select the value "EFTPOS" in the Method Field, then they cannot leave Trans_No as NULL

Basically something like
IF Method = 'EFTPOS' Then Trans_No IS NOT NULL.

How can I do this, note both fields are within same table.

Regards

In the Before_Update event of the form do this:

If Ucase(Method.value) = "EFTPOS" Then
    If Trans_No.value = "" or IsNull(Trans_No) = True Then
        Trans_No.SetFocus
        Msgbox "Trans_No cannot be blank"
        Cancel = True
    End If
End If

Amended code. Form level of course

 

[The_Doc_Man]

Supposedly, in a "big boy" database you can do cross-field validation, but the primary use of the validation propery of a field in an Access database is things like >0 or <>6 or (for text) <> "X" (where X is some text string)

Doing anything else requires Access to actually run a query when processing your field. The normal validations can be tested when Access looks at the tables("tablename")!fields("fieldname") properties without having to search for other elements of the table's fields collection.

With ORACLE and some other "big boy" databases you can generate a "trigger" event without a form or report being present to provide event context. In Access, you need a context that supports VBA to do that sort of thing, and the raw table definition context does not offer that support.

 

[boblarson]

Actually, you CAN set a table level validation rule in Access.

In Access 2007, Open the table in design view and do not select a field. Make sure the PROPERTY SHEET button is depressed on the DESIGN RIBBON TAB and then you can select the General Properties and go to VALIDATION RULE and enter:

IIf([Method] = 'EFTPOS',[Trans_No] IS NOT NULL,"")

And in Access 2003, you would get to the Table's General Properties by going into the table in design view and then clicking VIEW > PROPERTIES.

 

[The_Doc_Man]

Damn, Bob - another feature that sneaked in without checking with me first. How dare they?

 

[boblarson]

Damn, Bob - another feature that sneaked in without checking with me first. How dare they?

I learned that one from someone on the site that corrected me when I said there was no way to do it. So, I was in your spot back then.

 

[vbaInet]

I learned that one from someone on the site that corrected me when I said there was no way to do it. So, I was in your spot back then.

lol I was actually aware it could be done, but I don't ever use table validation (for obvious reasons) that was why I wasn't suggesting that option.

 

[sjl]

Actually, you CAN set a table level validation rule in Access.

In Access 2007, Open the table in design view and do not select a field. Make sure the PROPERTY SHEET button is depressed on the DESIGN RIBBON TAB and then you can select the General Properties and go to VALIDATION RULE and enter:

IIf([Method] = 'EFTPOS',[Trans_No] IS NOT NULL,"")

And in Access 2003, you would get to the Table's General Properties by going into the table in design view and then clicking VIEW > PROPERTIES.

Hi Bob,
I haven't had need to come to the Forum over the last 6 months, but today I was stuck...with this very problem.

I was trying to place this validation at the field level:

IIf([dtmCopAmendSubmitDate] IS NOT NULL,([dtmCopAmendAppDate]>=[dtmCopAmendSubmitDate]),Null)

Repeated attempts with the wording did not work....

But, when I placed it into the table's General properties, it worked like a charm....

Thanks....you helped simplify my life!
Sarah