Message boxes

[beeky]

I have a form based on a table where some fields are required to be filled by the user. If the user moves on to another record the default MS error message box is shown. What I would like is a friendlier message box that shows the field name (by its label name) that needs to be filled and also an instruction that if the user does not wish to complete and save the record to press escape. I have looked through previous similar questions on this and other forums but non seem to be exactly what I am looking for. Any advice on how I can over write the default error message for a required field and put in my own or alternative modifiy MS's default message?

I have tried the following code in the form's before update but the default Microsoft message is either precedent or my code is wrong or in the wrong place:

 Private Sub  Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As  Integer, Title As String
   Dim DL As String, ctl As Control
   
   DL  = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If  ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            Msg  = "'" & ctl.Name & "' is Required!" & DL & _
                  "Please  enter a [URL="http://www.tek-tips.com/viewthread.cfm?qid=1596297&page=1#"]value[/URL] or hit Esc to  abort the record . . ."
            Style = vbInformation + vbOKOnly
            Title  = "Required [URL="http://www.tek-tips.com/viewthread.cfm?qid=1596297&page=1#"]Data[/URL] Missing! . . ."
            MsgBox  Msg, Style, Title
            ctl.txtIssueDate.SetFocus
            Cancel  = True
            Exit For
         End If
      End If
   Next
End  Sub [code]

Is there another way of achieving what I am trying to do or can someone please point me in the right direction?

 

[gemma-the-husky]

its hard to see, but your code should be doing what you want - which MS error message are you getting - maybe one of the required fields doesnt have the tag property set up.

you could also ensure that each control is non-blank before you leave it.

 

[beeky]

Thanks gemma-the-husky. Actually it is not so much an error message but a message that states the field must be filled because it is a requied field as set up in the table.

As a temporary fix I am using :

 Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(txtIssueDate, vbNullString) = vbNullString Then
 MsgBox ("please enter a value in the Issue Date field or press the Esc key to abort this record")
txtIssueDate.SetFocus
 Cancel = vbCancel
 Exit Sub
End If
End Sub [Code]

but this only captures the txtIssueDate field and I want all required fields to be captured. The strange part is that this code works in the before update whereas the other one does not.

 

[SOS]

The problem with your code is this line:

If Trim(ctl & "") = "" Then


You need to specify ctl.Value because there is no DEFAULT like normal for ctl as Control.

If Trim(ctl.Value & "") = "" Then

 

[beeky]

Thanks SOS,
By Value I am assuming you mean the actual name of the text box such as "txtIssueDate". If that is the case how will it pick up any other fields that require the user to place an input? Or am I just being thick?

 

[SOS]

Thanks SOS,
By Value I am assuming you mean the actual name of the text box such as "txtIssueDate". If that is the case how will it pick up any other fields that require the user to place an input? Or am I just being thick?

No, just EXACTLY as I wrote

ctl.Value

instead of

ctl

 

[beeky]

Thank you Sos.