Code:
UPDATE BFCD_Designs
SET BFCD_Designs.EndDt = #5/30/2007#

WHERE 
BFCD_Designs.EndDt is Null
AND BFCD_Designs.designid in
(
SELECT BFCD_Designs.DesignID
FROM BFCD_Designs INNER JOIN 
    (SELECT BFCD_Designs.DesignID,
     Max(BFCD_Designs.Revision) AS MRevision
     FROM BFCD_Designs
     GROUP BY BFCD_Designs.DesignID
    ) AS sq 
  ON BFCD_Designs.revision = sq.mrevision 
  AND BFCD_Designs.designid = sq.designid
)
The bit in bold forces only those not already end dated to have a date.
I am joining all designs to the max revision per design id from all designs and returning only the designID.
This then goes into the where clause filter to tell the db which records to update.

Since your primary key is not one field, the design id is repeated more than once, you need to use two fields to find the row. However as it is a subquery (joining on the update means you cannot update (at least in Access)) it returns only a list of DesignIds, so I added the filter in bold so it will only update those not already end dated.

Personal preference would be :
Designs table - designs information
Revisions table - Design id and revision <- might hold the start / end for revision - Or at least a start date?
UserImp table - those that need checking for updates

Either method, as long as it does the job and works is good. It also depnds on how you are getting the users to interact with it.