Check for match in table 1 and change status in table 2 if there is a match

[rlsublime]

I found a way to do it in the design view of the table by setting the required field to YES under the general tab, but is there a way to change the message that is displayed. there is currently an auto generated message. I wanted to display a more user friendly message such as "Please enter 'FIELD NAME"". Is there a way to possibly recreate this in vba?



Thanks

[rlsublime]

I think the code below contains an idea of what I am trying to do. I am trying to generate a message box prompting the user to enter a value in the loan number field and to keep displaying the message till the user enters a value in the field and then move on to the rest of the code.
Hopefully this helps a little in disecting my issue. Thanks for your help.

Private Sub Save_Record_Click()
 
  Dim SQL As String
Do
  If IsNull(Me![Loan Number]) Then MsgBox "Please Enter Loan Number. This is a required field."
End If
 
Loop Until (Me![Loan Number]) = .Value
 
SQL = "Update Investor_Request set Status = 'Closed' where Loan_Number = " & Me![Loan Number] & _
  " AND status = 'Outstanding'"
 
 
CurrentDb.Execute SQL, dbFailOnError
 
On Error GoTo Err_Save_Record_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_Save_Record_Click:
    Exit Sub
 
Err_Save_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Record_Click
   
End Sub