Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I add a complex validation rule to a field in Access 2003 database?

Posted on 2012-08-29
2
Medium Priority
?
549 Views
Last Modified: 2012-08-29
I have a table that I need to add some complex validation in order to keep users from saving a record with invalid data.

Specifically, there is a Taxable field.  If Taxable = No, then there are 4 fields that MUST have something in them before the record can be saved.

So, if Taxable = Yes, record can be saved.

If Taxable = No, record can only be saved if Type <> "" and ExemptNo <>"" and ExpirationDate <>"" and Certificate <> ""

Would I build an expression and put it in the Taxable field?  Or have different expressions in each of the 4 required fields when Taxable = No?

Thanks for any guidance!
0
Comment
Question by:lthames
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38348526
use the beforeUpdate event of the form

private sub Form_beforeUpdate(cancel as integer)
   select case Me.Taxable
        Case="Yes"
            'continue saving
        case="No"
              if  Type & ""= "" Or ExemptNo & ""="" Or ExpirationDate & ""="" Or Certificate  & ""= ""  then
                   msgbox "The following fields are required" _
                            & vbcrlf & " Type" _
                             & vbcrlf & " ExemptNo " _
                            & vbcrlf & " ExpirationDate" _
                            & vbcrlf & " Certificate"    
                   cancel=true
              end if


   end select
end sub

you can also use the validation codes from this link

http://www.experts-exchange.com/Database/Miscellaneous/Q_22653980.html#a19351039
0
 

Author Closing Comment

by:lthames
ID: 38348646
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question