Access help needed. Validating data in one table based on data in another

Dolph · 17 Nov 2007 at 12:04

Hoping someone can help me with this, my access stuff is a bit rusty.

I'm working on something where there is a table for primary jobs, with an associated table with sub-jobs on a one to many relationship. Each sub job has a completed yes/no entry, as does each primary job.

What I want to do is to make it so you cannot set the complete field in the primary job table to yes if there are linked sub jobs where the complete field is set to no.

Any thoughts on the simplest way to do this?

RockLobster · 17 Nov 2007 at 12:20

Are the users using the tables directly or forms?

Dolph · 17 Nov 2007 at 12:33

All data changes are done using forms, the users will be unable to access the tables directly.

suarve · 17 Nov 2007 at 14:56

I'd imagine you could add a bit of VBA code utilizing IF statements or attach a macro to the 'onclick' event of a submit button or similar. Thats the way i did last time, but im sure there are quicker/more efficient ways.

Dolph · 17 Nov 2007 at 15:37

suarve said:
I'd imagine you could add a bit of VBA code utilizing IF statements or attach a macro to the 'onclick' event of a submit button or similar. Thats the way i did last time, but im sure there are quicker/more efficient ways.

That's my thought too, or to the 'onupdate' property of the checkbox in question.

The problem is my vba and sql is really rusty after 5 years of non-use.

RockLobster · 18 Nov 2007 at 10:54

http://www.functionx.com/vbaccess/howtoado/locaterecord.htm - This will get you started.

Dolph · 18 Nov 2007 at 12:22

RockLobster said:
http://www.functionx.com/vbaccess/howtoado/locaterecord.htm - This will get you started.

*hears gears grinding and lightbulbs illuminating*

That's most helpful