VBA - Refer to all controls on a form

[Mi]

hello,
I am trying to find a code. I would like to check all the controls on a form, if null, then msg box to alert. Looking for something like:

if isnull(me.formALLCONTROLS) then
msgBox "no empty fields allowed"
end if
New record is then allowed

BTW, setting the fields on the table level to required has not worked, i have many subforms and it still allows a new record to be added.

Thanks,
Mi

 

[Mi]

This code works but has a problem if the form opens in add mode only and not edit. It also looks at all the past records. I think i am heading in the wrong direction with this problem. Any suggestions on a better way? Main form has currently 10+ fields on it and it continues to grow. Subform has two fields with dropdowns, (select review and then score it).

Table A
ReviewID
Office
ReviewDate
ProjectName

Table B
ReviewTypeID
ReviewType

Table C
ScoreID
Score

Table D
ReviewID
ReviewTypeID
ScoreID


Thanks,
Mi