How to Customise the error message if you forget to add a value.

[Bopsgtir]

Hi all, id like to make my form look a little better when it comes to missing data, basically once you enter your data you click a button and that saves the record and emails the details off, but if you forget to say imput the person first name you would get a warning message box saying, you must enter a value in the 'tblTechs.First_Name' field. then when you click ok you get the macro single step window pop up.

What id like is first stop the macro single step popping up and ideally changes the message you something like [First Name] cannot be blank. and one step further would be when you click ok the curser would then go in the field thats missing.

 

[vbaInet]

In Table level, is the Required property of the fields set to Yes?

What you can do is to trap the Form's error. I don't know what the exact error message is but when the error message pops up, just take a note of the error number and use it in the Form's Error event. To get the name of the control where the error originated from, use the Me.ActiveControl.Name method.

 

[adyas]

Hi

I have a similar problem but I know nothing about VB and so am asking if there is another solution.

I have several required fields on a form, some of which are cascading combo boxes. Upon closing the form, if any of these fields are left blank I get the usual unfriendly error message:

You must enter a value in the 'TBLRequestForService.Project Type' field

Then I'm told that I can't save the record at this time and do I still want to close the database.

It would be helpful if I could:

1. Change the wording of the error message
2. Click ok, the dialog box disappears and the cursor appears in the required field.

All advice is appreciated.

Al

 

[vbaInet]

You can change the error message to whatever text you wish. All you need to do is trap it in the correct event. In your case it would be the Form's Error event and/or the Form's Before Update event.

Or trap it in each of the control's Before Update event.

 

[Pat Hartman]

When you are trying to trap missing data, the ONLY appropriate event is the Form's BeforeUpdate event. Events for individual controls won't be fired if nothing is entered so you can't identify missing data that way.

When you click on the build box (three dots) to the right of the form's BeforeUpdate event, you should see a choice including code and macros. I suggest code. It's pretty simple and you need to learn anyway. You could choose macro but I think macro logic is confusing.

If Me.SomeField & "" = "" Then
    Msgbox "SomeField is required.",vbokOnly
    Me.SomeField.SetFocus
    Cancel = True
    Exit Sub
End If
If Me.SomeOtherField & "" = "" Then
    Msgbox "SomeOtherField is required.",vbokOnly
    Me.SomeOtherField.SetFocus
    Cancel = True
Exit Sub
End If

Concatenating a ZLS with a control will let you test for null and ZLS with one If statement.

 

[adyas]

Morning Pat

Geewiz that's amazing!

I have about 8 required fields on this database and it worked immediately for most of them.

For those that didn't work I figured out that it was because of the bound column on each of the fields, so I edited this via the table and now they work perfectly.

It a nice short piece of code for me to cut my teeth on. I understand why it is positioned where it is, in BeforeUpdate, and I understand some of the code in relation to the If function, field names, message box text and spacing I just need to do a bit of research to make sure I understand the rest. Its a whole new language!

You're wonderful, thank you Pat.

PS
You've also helped me with another query i posted 'Can I duplicate form and subforms'. I still need this and will definitely have a go at it, but my boss' priorities have changed slightly and this bit wasn't seen as an urgent which is why I haven't come back to you yet.

 

[Gin11]

I am very familiar with VBA for Excel, so was not adverse to using it in Access. Just didn't know where to put it. I'm SO glad Pat was clear - BeforeUpdate! I can't tell you how much this saves me.

BTW - Thanks to Adyas for asking the right question. That's always important.

Gin

 

[Gin11]

Pat,

I did something wrong. In form design, with selection type "form", I selected the "Before Update" on the Event tab. In the build box, I selected code and entered the following: (Actually, there are 9 questions total. Just giving the "flavor".) When it didn't work, I entered the same code for "On Error" with the same results. (Results below the code in note.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Go through each of the required fields and prompt
If [DocName] & "" = "" Then
MsgBox "Document Name is required.", vbOKOnly
DocName.SetFocus
Cancel = True
Exit Sub
If [ReviewerNameFirst] & "" = "" Then
MsgBox "Your First Name is required.", vbOKOnly
ReviewerNameFirst.SetFocus
Cancel = True
Exit Sub
If [ReviewerNameLast] & "" = "" Then
MsgBox "Your Last Name is required.", vbOKOnly
ReviewerNameLast.SetFocus
Cancel = True
Exit Sub
If [BUCRAEM01-A] & "" = "" Then
MsgBox "Rating each question is required.", vbOKOnly
[BUCRAEM01-A].SetFocus
Cancel = True
Exit Sub
End If
End Sub

This still returns the Access generated error messages. Here is the scenerio:

In the format of the form, I set the property "Close Button" to "No". In order to close, there is a Command Button that runs the [Embedded Macro] "CloseWindow".

When the user clicks this button and a required field is not populated, the error is: "You must enter a valid value in the [field name] field". Button options: "OK" or "Help".

Dialog box when click OK is:
"You can't save this record at this time.
[Application Name] may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?" Button options: "Yes" or "No".
Most of the time, the user will want to go back to the form and fill in the missing information. (The "No" option.) MS Access handling of this error is not "user friendly".

The Macro Single Step message box has the following properties/ fields:
Macro Name: My.macro.name
Action Name: CloseWindow
Arguments: , , Prompt
Buttons in upper right:
"Step" (grayed out)
"Stop All Macros" (available)
"Continue" (grayed out)

Error Number: 3021

 

[Pat Hartman]

I'm not sure why you are using macros. Try converting them to VBA so we can see what they are actually doing.

There is a bug with some versions of Access that occurs when you try to close a form without forcing a save first even though Access is supposed to do this naturally.

In the click event of the close button add:
DoCmd.RunCommand acCmdSaveRecord
ahead of the close command.

Also, use "Me." for all the control references. Without it, you won't get intellisense. You also add ever so slight overhead because you force Access to figure out where the variable is defined.

If Me.[DocName] & "" = "" Then
    MsgBox "Document Name is required.", vbOKOnly
    Me.DocName.SetFocus
    Cancel = True
Exit Sub

 

[Gin11]

Thanks Pat, but still having issues. Here are scenarios.

Scenario 1: Close button has "On Click" code you suggested:

Private Sub CloseForm_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

End Sub

When click, no action takes place. Form does not close. Therefore, I concluded that it is not getting to the error handling. So, I added error handling to this event.

Scenario 2: Close button has "On Click" code:

Private Sub CloseForm_Click()
On Error GoTo Err_cmdCloseForm_Click
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
Exit_cmdCloseForm_Click:
Exit Sub
Err_cmdCloseForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseForm_Click

End Sub

The form still did not close. So, I entered all the required fields, and it still doesn't close. Therefore, the "CloseWindow" is not working.

Scenario 1.1: Keeping all fields populated, I added Me.SetFocus [frmName] between the acCmdSaveRecord and DoCmd.Close.

Private Sub CloseForm_Click()
DoCmd.RunCommand acCmdSaveRecord
Me.SetFocus frmResultsBUCTW
DoCmd.Close

End Sub

The form still does not close.

The form will close and save a completed record if using the close window macro. Need to overcome this before continuing to error handling.

BTW - I exported the tables to SharePoint as lists. Could that impact the outcome?

Thanks again.

 

[Gin11]

Side note: I noticed the "On Click" events were stored in MS Access Class Objects, not VBA modules. Could that be my problem?

 

[Gin11]

Pat (and anyone else reading through this), I found the major part of my issue. Others may have similar issues, so posting it here.

The Access database will be used primarily by users with Access 2007. My PC with access to the SharePoint has Access 2010. I have 2007 on another PC. I brought the database to that PC. Removed the SharePoint links and added tables back in. Now, the VBA code DoCmd.RunCommand acCmdSaveRecord and DoCmd.Close work. There is an issue with 2010. I am hoping it will still work when I bring it back to 2010. (Yet to be seen.)

I'm having some issue with more than one condition. Not sure if it's because of the multiple "If" statements. Pat had multiple Ifs without using Else. When I have one If statement followed by an Else statement that worked. I could return the different conditions. When I add more Ifs, it breaks. Any hints will be appreciated.

Much thanks!