Show MsgBox rather than Standard Access Error

[davebhoy]

Hi there

I'm new to all the VBA coding and wondered the simplest way to code for the following:

1) The user inputs data to a form based on a table. Where the field is set to required I need to show a message box that says this rather than the standard MS Access error message

2) Where the record is not unique based on the index I need to show a similiar message

Here's hoping this is a simple query! Thanks

Dave

 

[Bodisathva]

look at "Error Handling" in the help files. Inside the Error Handling routine you create, you want to use the MsgBox function

 

[TedMartin]

Try managing the error using the ON EXit

 

[TedMartin]

Try managing the error using the On Exit property of the field

e.g.

If Me.Field = "" or isnull(Me.Field) then
msgbox "Field Needed"
Field.SetFocus
else
End If

 

[Ripley]

for example, if you wanted to add your own message box instead of s standared error for say a delete command, you would do the following:

Private Sub deleterecord_click()
Dim confirm
confirm = MsgBox("You are about to delete a record. Proceed?", 65, "Confirm Delete?")
If confirm = 2 Then
Exit Sub
End If
If confirm = 1 Then
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
End If
End Sub

In this example, when the command button "deleterecord" is clicked, a message box is displayed, displaying the buttons OK and Cancel.

The text "You are about to delete a record. Proceed?" is the text that is displayed within the body of the textbox. The text "Confirm Delete?" is the title of the text box. Replace it with a "," to use the aplication name.

If the cancel button is clicked, the output from the textbox is 2, and if OK is clicked the textbox outputs 1.

So now we can use a simple IF command.

If cancel is clicked, then the procedure is ended (Exit Sub), and if OK is clicked, then it will do the procedure.

The commands Set Warnings True and False simpley turn off the default warnings, so they will not be displayed. Be sure to use both False and True outside the delete procedue, otherwise you may end up inadvertainly deleting a record. Hope this helps.

 

[davebhoy]

Thanks a mill for that!

What would the confirm code be if this was to add a record to the table and the cancel code to clear the entry made?

Thanks again

Dave

 

[davebhoy]

IS there an equivalent "onexit" property for the new record rather than the field?

Ta.

 

[davebhoy]

Hi TedMartin

I've tried the following code similar to what you suggested but it's saying "object required"

Private Sub name_Exit(Cancel As Integer)

If Me!name = "" Or Me!name Is Null Then

MsgBox "This is a required field. Please enter data.", vbOKOnly, "Field Needed"
Me!name.SetFocus

Else
End If

End Sub

 

[TedMartin]

On Exit code

You can't call the field Name - it must be a reserved word within this context. The atached works so run with this.

 

[Ripley]

sorry i didnt reply sooner, but that database answers all!

 

[davebhoy]

What Access version is that DB as I'm having problems opening it?

 

[ted.martin]

What Access version is that DB as I'm having problems opening it?

v2003 - do you need it in v2000?

 

[davebhoy]

Yes please Ted!

 

[davebhoy]

Got the database open since my last post, thanks. Just two more questions:

1) your example still lets you tab forward without making an entry, how can I code it to stay on that field until something is entered

2) If the user is entering a new field how can we maessage him/her to confirm the new entry or alternatively clear his entry?

Thanks once more.

 

[TedMartin]

Got the database open since my last post, thanks. Just two more questions:

1) your example still lets you tab forward without making an entry, how can I code it to stay on that field until something is entered

2) If the user is entering a new field how can we maessage him/her to confirm the new entry or alternatively clear his entry?

Thanks once more.

Q1 - Cancel = True cancels the exit and effectively locks the focus back BUT be careful with this as you may not be able to close the form with a blank field which could prove to be a problem with New Record and then changing your mind and want to cancel.

Private Sub FieldName_Exit(Cancel As Integer)

If Me.FieldName = "" Or IsNull(Me.FieldName) Then

MsgBox "This is a required field. Please enter data.", vbOKOnly, "Field Needed"
Cancel = True
FieldName.SetFocus

Else
End If
End Sub

Don't understand Q2

 

[davebhoy]

Thanks again Ted

What I mean in Q2 is that when they enter all fields and are therefore entering a new record in the table, how can I pop a msgbox to get to confirm the new record entry or cancel to clear the record.

 

[TedMartin]

Hope this will do it. Couple of things - you will need to distinguish between an existing and a new record. I use a Public Variable called strNewRecord which needs to be set to "Yes" when the new Record is called. You will need to find a suitable way of identifying when a New Record is being added.

I have put the 'check' on the Windows form Close button but it may be better for you to disable that and use your own Close button instead. If so, then this code should be on your Close button rather than the Form. Give it a try.


Private Sub Form_Close()

On Error GoTo Err_Close

Dim iResponse As Integer

If strNewRecord = "Yes" Then

iResponse = MsgBox("Are all the fields are correct?" & vbLf & vbLf & "or do you wish to Delete/CANCEL this new Record?", vbOKCancel, "Please Check")
If iResponse <> 1 Then ' 1 is OK 2 or X is Cancel

number.SetFocus 'Need to select record
' this deletes the current record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Else
DoCmd.RunCommand acCmdSaveRecord
strNewRecord = "No"
End If
Else
'Do nothing
End If

Exit_Close:
Exit Sub

Err_Close:
MsgBox Err.Description & " --- " & Err.number
Resume Exit_Close

End Sub