Change text in error message (1 Viewer)

[mkdrep]

I have a table with a field, [Job Process].[JPGopher_ID], which requires a value in the field. The form I have developed for my users has the label, "By Whom" instead of [JPGopher_ID], which makes more sense for the users.

Unfortunately, the error msg that pops up (see attached .jpg) states "You must enter a value in the "Job Process.JPGopher_ID field", which doesn't mean anything to the users.

The 2nd attached .jpg, (Must_Enter_By_Whom_Value), is what my users see on the form.

Is there a way to change the MS Office generated error msg so it reads "You must enter a value in the 'By Whom' field?" which will make sense to my users?.
thank you.

 

[theDBguy]

Hi. You can use the form’s OnError event to display your own message.

 

[mkdrep]

Hi. You can use the form’s OnError event to display your own message.

I'm still new to Access, so can you give me an idea of what type of expression I should write? thx

 

[MajP]

https://docs.microsoft.com/en-us/office/vba/api/access.form.beforeupdate(even)

Look at the form level example.

 

[mkdrep]

https://docs.microsoft.com/en-us/office/vba/api/access.form.beforeupdate(even)

Look at the form level example.

Attached (form_fields.jpg) to show the names of the fields that are reflected in the error messages below...

I went to the form level, copied the code and inserted the name of my control [JPGopher_ID] for [oContr] and when I click on anyone of the 5 buttons in my BYWhom box, I get (4) msgs reading "JPCallnotes is empty","JPActionItem is empty", "JPGofer is empty", "JPFUDate is empty". Each "JPxxxxx" is a different field in the form.

Here is the code I put in the BeforeUpdate field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim JPGopher_ID As Control
For Each JPGopher_ID In Me.Detail.Controls
If IsNull(JPGopher_ID) = True Then
If MsgBox(JPGopher_ID.Name & " is empty", vbOKCancel) = vbCancel Then
Cancel = True: JPGopher_ID.SetFocus: Exit Sub
End If
End If
Next JPGopher_ID
End Sub

Any suggestions as to why I get the error msgs?

 

[theDBguy]

Hi. Looks like you're using the BeforeUpdate event rather than the OnError event I suggested.

 

[mkdrep]

Hi. Looks like you're using the BeforeUpdate event rather than the OnError event I suggested.

Yes, I was using it at the suggestion of MajP because he sent me this link with a code to reference....

https://docs.microsoft.com/en-us/office/vba/api/access.form.beforeupdate(even)

 

[mkdrep]

Hi. Looks like you're using the BeforeUpdate event rather than the OnError event I suggested.

I put this code in the OnError event box and the result I get is the program advising that "Cancel" is not defined...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim JPGopher_ID As Control
For Each JPGopher_ID In Me.Detail.Controls

If IsNull(JPGopher_ID) = True Then
If MsgBox(JPGopher_ID.Name & " is empty", vbOKCancel) = vbCancel Then

Cancel = True: JPGopher_ID.SetFocus: Exit Sub
End If
End If
Next JPGopher_ID
End Sub

 

[MajP]

In the example they were looking to ensure all controls were filled. You seemed to be concerned only with one. In that case do not loop all the controls

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim JPGopher_ID As Control
  If IsNull(JPGopher_ID) Then
     If MsgBox("JPGopher_ID is empty", vbOKCancel) = vbCancel Then
     Cancel = True
     JPGopher_ID.SetFocus
     Exit Sub
   end if
 End If
End Sub

 

[MajP]

I get is the program advising that "Cancel" is not defined.

Cancel will only work in the Before Update

Form_BeforeUpdate(Cancel As Integer)

It allows you to cancel the before udpate event.
I do not use the Form_Error event, so I am less familiar with its use.

 

[mkdrep]

In the example they were looking to ensure all controls were filled. You seemed to be concerned only with one. In that case do not loop all the controls

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim JPGopher_ID As Control
  If IsNull(JPGopher_ID) Then
     If MsgBox("JPGopher_ID is empty", vbOKCancel) = vbCancel Then
     Cancel = True
     JPGopher_ID.SetFocus
     Exit Sub
   end if
 End If
End Sub

I put the code into the OnError even field and when I compile the code I get an error msg "Compile error: Variable not defined" and it is highlighting
"Cancel ="

Any suggestions?

 

[MajP]

Here are the details for Form_Error if you prefer to go that route instead.
https://docs.microsoft.com/en-us/office/vba/api/access.form.error

 

[MajP]

I put the code into the OnError even field and when I compile the code I get an error msg "Compile error: Variable not defined" and it is highlighting
"Cancel ="

See post #10

 

[mkdrep]

Here are the details for Form_Error if you prefer to go that route instead.
https://docs.microsoft.com/en-us/office/vba/api/access.form.error

Here is the code I inserted for OnError Event for the Form. It worked but I have not idea why as I am not sure what "Case" means. thx for your help

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr

Case 3314
MsgBox "By Whom Field Is Required, so you cannot leave this field empty"
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
ActiveControl.Undo
End Sub

 

[Micron]

Maybe I missed this suggestion, but part of the solution might be to refer to the textbox label control caption IF it is attached/associated with the textbox. So the message could be
msgbox "Must provide a value for " & txtMyTextbox.Controls(0).Caption
Textboxes (and some other controls) have a controls collection, which is zero based and only contains one member - the attached label. I like to use this method when possible because it's still valid if the caption changes.

 

[MajP]

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr

Case 3314
MsgBox "By Whom Field Is Required, so you cannot leave this field empty"
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
ActiveControl.Undo
End Sub

When an error occurs this event is triggered, and it is passed in an Error Number. All access errors have a unique number. It comes in here "DataErr As Integer".

You select case says if it is this number do this, if this other number do that,... It is a short way to do lots of if thens.
Error 3314 is what is thrown when required data is not supplied.

 

[isladogs]

Here is the code I inserted for OnError Event for the Form. It worked but I have not idea why as I am not sure what "Case" means.

Select Case is another way of handling conditions.
Similar to If...Else...End If ... but often easier to read and more efficient to execute

So in your code, if the error is 3314, your error message appears.
Otherwise the default message is shown.

 

[MajP]

Select Case
https://docs.microsoft.com/en-us/of...nce/user-interface-help/select-case-statement

Verfy useful if not familiar. Can save a lot of typing.

 

[mkdrep]

Maybe I missed this suggestion, but part of the solution might be to refer to the textbox label control caption IF it is attached/associate with the textbox. So the message could be
msgbox "Must provide a value for " & txtMyTextbox.Controls(0).Caption
Textboxes (and some other controls) have a controls collection, which is zero based and only contains one member - the attached label. I like to use this method when possible because it's still valid if the caption changes.

thank you for your suggestion! Much appreciated