What am I doing wrong with subquery
There are many many ways to do this. Here is one that only slightly changes your attempt:

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2
                    WHERE m2.ModuleConvenor = l.StaffID) ;