How to lock records until specific conditions are met?

  • Thread starter Thread starter Ernie Lippert
  • Start date Start date
E

Ernie Lippert

Look at the table (tbl_Main) below. The first 6 records are Locked because
they have Null in Date_Completed. Records 7-19 & 25 are not locked because
The Date_Completed is Not Null. This is easy enough and I can implement it
with a Lost Focus event on the Date_Completed control.

However, for records 20 & 21 and 22-24 each of which is associated with its
Project_# but with possibly different analysts is another problem. The
Date_Completed is not Null for one or more records but is Null for at least
one record in these groups.

How do I programmatically lock a Project_# until all associated records have
been marked with a Date_Completed entry? See completed records 11 & 12, and
17 & 18 each having common Project_#.



Project_# Analyst Date_Entered Date_Completed Locked
1 08 3925 Lisa 14-Nov-08 Yes
2 08 3927 Lisa 14-Nov-08 Yes
3 08 3927 Lisa 14-Nov-08 Yes
4 08 3927 Lisa 14-Nov-08 Yes
5 08 4087 Lisa 14-Nov-08 Yes
6 08 4160 Lisa 14-Nov-08 Yes
7 08 4183 James 17-Nov-08 22-Sep-08 No
8 08 4186 James 17-Nov-08 25-Sep-08 No
9 08 4209 James 17-Nov-08 16-Nov-08 No
10 08 4211 James 17-Nov-08 23-Sep-08 No
11 08 4225 James 17-Nov-08 25-Sep-08 No
12 08 4225 James 17-Nov-08 25-Sep-08 No
13 08 4248 James 17-Nov-08 22-Sep-08 No
14 08 4268 James 17-Nov-08 22-Sep-08 No
15 08 4319 James 17-Nov-08 24-Sep-08 No
16 08 4942 Dale 14-Nov-08 12-Nov-08 No
17 08 4964 Dale 14-Nov-08 02-Dec-08 No
18 08 4964 Dale 14-Nov-08 12-Nov-08 No
19 08 4970 Dale 14-Nov-08 10-Nov-08 No
20 08 4994 Dale 14-Nov-08 Yes *
21 08 4994 James 14-Nov-08 12-Nov-08 Yes *
22 08 5053 James 17-Nov-08 13-Nov-08 Yes *
23 08 5053 Ernie 18-Nov-08 01-Dec-08 Yes *
24 08 5053 Ernie 01-Nov-08 Yes *
25 08 9999 Ernie 18-Sep-08 21-Nov-08 No
 
Look at the table (tbl_Main) below. The first 6 records are Locked because
they have Null in Date_Completed. Records 7-19 & 25 are not locked because
The Date_Completed is Not Null. This is easy enough and I can implement it
with a Lost Focus event on the Date_Completed control.

However, for records 20 & 21 and 22-24 each of which is associated with its
Project_# but with possibly different analysts is another problem. The
Date_Completed is not Null for one or more records but is Null for at least
one record in these groups.

How do I programmatically lock a Project_# until all associated records have
been marked with a Date_Completed entry? See completed records 11 & 12, and
17 & 18 each having common Project_#.

        Project_#       Analyst Date_Entered    Date_Completed  Locked  
1       08 3925 Lisa    14-Nov-08               Yes      
2       08 3927 Lisa    14-Nov-08               Yes      
3       08 3927 Lisa    14-Nov-08               Yes      
4       08 3927 Lisa    14-Nov-08               Yes      
5       08 4087 Lisa    14-Nov-08               Yes      
6       08 4160 Lisa    14-Nov-08               Yes      
7       08 4183 James   17-Nov-08       22-Sep-08       No      
8       08 4186 James   17-Nov-08       25-Sep-08       No      
9       08 4209 James   17-Nov-08       16-Nov-08       No      
10      08 4211 James   17-Nov-08       23-Sep-08       No      
11      08 4225 James   17-Nov-08       25-Sep-08       No      
12      08 4225 James   17-Nov-08       25-Sep-08       No      
13      08 4248 James   17-Nov-08       22-Sep-08       No      
14      08 4268 James   17-Nov-08       22-Sep-08       No      
15      08 4319 James   17-Nov-08       24-Sep-08       No      
16      08 4942 Dale    14-Nov-08       12-Nov-08      No      
17      08 4964 Dale    14-Nov-08       02-Dec-08      No      
18      08 4964 Dale    14-Nov-08       12-Nov-08      No      
19      08 4970 Dale    14-Nov-08       10-Nov-08      No      
20      08 4994 Dale    14-Nov-08               Yes     *
21      08 4994 James   14-Nov-08       12-Nov-08       Yes     *
22      08 5053 James   17-Nov-08       13-Nov-08       Yes     *
23      08 5053 Ernie   18-Nov-08       01-Dec-08       Yes     *
24      08 5053 Ernie   01-Nov-08               Yes    *
25      08 9999 Ernie   18-Sep-08       21-Nov-08       No      
Click to expand...

I think you can only do it if you view the records one at a time.
Then you could use DCount to find if a project has incomplete
records. And set the Locked property of the record to DCount(...)=0
 
Back
Top