I have the following table:
Code:
Color CoilNumber Gage DateOpened DateFinished
 
Black 10378490200 29 3/1/2011 3/29/2011
 
Blue Q197127 29 3/28/2011 
Bright White Q057086 29 3/28/2011
 
Bronze 51728370300 29 2/2/2011 3/29/2011
 
Bronze 3000298961 29 3/11/2011 
 
Brown 51797910400 29 3/9/2011 
 
Burgundy 2 29 
Burgundy 51790641700 26 3/28/2011
 
Burgundy 1 29 3/18/2011
You’ll notice that the Burgundy has three entries. 2 of them are 29 gage and 1 is 26 gage. A date in [DateOpened] indicates that a coil is open for use. We can only have one coil open for use in a particular gage and color. So the coil #1 is the 29 gage “open” coil for Burgundy. When I “open” the Burgundy coil #2 by putting a date in [DateOpened] I would like the “OnUpdate” event on that field to contain code that would search to see if there are any other coils of this color and gage “open” and if it finds one then insert today’s date in [DateFinished]. But the code needs to bypass the current record that I just updated.


Can you give some code that will get the job done or steer me the right way. Thanks in advance for your help.
Richard
PS I couldn't get my table to display right so please view the PDF file below.