access course microsoft training - Data Validation

Resolved · Low Priority · Version Standard

Tue 23 Jan 2007, 14:28 Delegate Nick said...

Nick has attended:
Access Intermediate course
Access Advanced course
Excel Advanced course

Data Validation

I am looking to do 2 things in a newly created database, and predictably, Help gives me nothing.

Firstly, I want to specify a rule that if certain fields in a form or subform are completed, all others must be to. How do I do this. I don't want to do it by making it a required field in the table for a number of reasons.

Secondly, I want fields in a form to autocomplete when certain data is entered. In this case, when leaving date is entered, I want data for 6 follow up dates to automatically fill in, 1 month after, 2 months after and so on.

Any help much appreciated.

Nick

Fri 26 Jan 2007, 12:54 Trainer Carlos said...

RE: Data Validation

Your first item requires somw VBA code to run it to your specifications

As for the second question all you need to do is make the subsequent date text boxes as calculated fields

So for example

You enter Today's date in a field called OrderDate

Then the field called OneMonth will have the calculation =[OrderDate] +30

Mon 29 Jan 2007, 10:04 Delegate Nick said...

RE: Data Validation

Many thanks for the calculated field information, that is now working brilliantly.

Two further questions regarding VBA code.

1. Where is a good manual / tutorial for learning code, as I think it's the next thing I'm going to need to make myself better with access generally.

2. Could you give me the code for the following?

If [learning aim reference] is not null then [course title] must not be null

With that hopefully I could create the rest of what I need.

Many thanks

Thu 8 Feb 2007, 10:05 Edited on Thu 8 Feb 2007, 10:06 Trainer Richard said...

RE: Data Validation

Nick,

Sorry for the delay in getting back to you on this.
To answer you second question:

When leaving date is entered, then autocomplete other fields

USE the following in the fields you want calculated.

=DateAdd("m",1,[leavingdate])
=DateAdd("m",2,[leavingdate])
=DateAdd("m",3,[leavingdate])

"m" stands for the interval, which is a month

Will need to do some work on your fiest question.
Perhaps one of our other trainers has some ideas.

Regards

Richard