You are opening the form form2 then running the code you're running so form 2 will ALWAYS open.
You need to perform this check on FORM1 *before* you open any other forms, then based on the retrieved value open the appropriate form.
on your form FORM1 remove the embedded macro from your button and use
Code:
If IsNull(DLookup("[Drawing_Type]", "[FileA MAPPS]", "[Signal_ID] = '" & Forms!form1!Text4 & "'")) Then
DoCmd.OpenForm "form2"
Else
DoCmd.OpenForm "form3"
End If
instead.
Also remove the form opening commands from FORM2 or you will continue to see form3 opened as well.