[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Trying to change the default message

hi all
when a user of my database tries to add a film that is over 250mins, the user is alerted by a msgbox (this is what i want to happen) it asks them if this is correct. however if they press cancel on the msgbox i get the unfriendly access message (an error has been encountered etc etc) how can i change this to something more user friendly?
code for this cmd button is attached
tia
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim mbxResult As VbMsgBoxResult

If Me.txtFilmLengthMinutes.Value > 250 Then
mbxResult = MsgBox("Film Length is over " & "250 minutes. Is this correct?", _
vbQuestion + vbOKCancel)
End If

If mbxResult = vbCancel Then
Cancel = True

Me.txtFilmLengthMinutes.SetFocus
End If
End Sub

Open in new window

0
dirtycarpet
Asked:
dirtycarpet
  • 13
  • 5
  • 2
  • +7
1 Solution
 
ElliottJohnCommented:
To catch the error you will need the code I have attached.

I presume the error is occuring when you are setting focus to the textbox - is this disabled?
Private Sub Form_BeforeUpdate(Cancel As Integer)
Try
Dim mbxResult As VbMsgBoxResult

If Me.txtFilmLengthMinutes.Value > 250 Then
mbxResult = MsgBox("Film Length is over " & "250 minutes. Is this correct?", _
vbQuestion + vbOKCancel)
End If

If mbxResult = vbCancel Then
Cancel = True

Me.txtFilmLengthMinutes.SetFocus
End If
Catch ex as Exception
msgbox Ex.toString()
End Try
End Sub

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
u need to add custom error handling, check this tutorial:
http://blogs.techrepublic.com.com/howdoi/?p=103
0
 
dirtycarpetAuthor Commented:
thanks,
i want to keep the focus to the textbox so when the user presses cancel (they have made a mistake by entering to many minutes) they can delete the data theyve tried to input

il take a look at the tutorial now:..cheers both
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
dirtycarpetAuthor Commented:
guys is there a way i can just let the user press cancel on the message box:

mbxResult = MsgBox("Film Length is over " & "250 minutes. Is this correct?", _
vbQuestion + vbOKCancel)

and then let them go back to the form so they can change the data theyve entered WITHOUT showing another message?
something like: doCmd.SetWarnings(false) ?
0
 
ElliottJohnCommented:
The an error has been encountered etc etc is actually an error and not produced by the messagebox.

If you please the error handlers in you will find where the error has occurred in the application, when you fix the error you will get the flow you require.

0
 
dirtycarpetAuthor Commented:
eh? elliott john i dont understand.. its not an error as such.
when users try to add a film to the d/b that is longer than 250mins, an msgbox is displayed to alert the user that the length entered is longer than 250mins. if they are correct (it is longer than 250mins) they press ok and it is added to the record.. if it is NOT longer than 250mins (theyve made a data entry error) they press cancel...its this error (a default error) i want to change. the default message after pressing cancel is "an error has been encountered, description:you cant go to the specified record. error number:2105.
its this default error i want to change....
thanks
0
 
ElliottJohnCommented:
I have taken your code and ran it, when I click cancel I do not receive any error.  

The error is not coming from the message box, but some other code that is running after this section - I'm guessing that you are showing this form modally and then presuming the user has clicked ok and that the row is added to the database.  

If you set a break point on the msgbox line and step through line by line you should see where the error actually occurs
0
 
dirtycarpetAuthor Commented:
somethings wrong somewhere because i am definatly getting a error. but its when the user (me) clicks cancel on the msgbox. ive put a breakpoint on but this tells me nothing.
any other suggestions v welcome!
0
 
Rey Obrero (Capricorn1)Commented:
dirtycarpet,
try doing the following first..
from access window
  tools > database utilities > compact and repair database

from the VBA window
  Debug > Compile  

  correct any errors raised




0
 
dirtycarpetAuthor Commented:
capricorn1,

i did just that..nothing...no errors at all..

am i right in thinking this is a default access error when pressing cancel? the d/b compiles, runs and works A1.. its just like i described..if the user has inputted a figure over 250 mins long (length of film) i have a msgbox pop up asking them if this is correct.. if they click ok, the record is added to the d/b if they click cancel they get the following error message:
an error has been encountered, description:you cant go to the specified record. error number:2105...the database doesnt crash or anything.. they click ok on the error msgbox and it gives focus to the text box as required..they then delete their entry and continue
tia dirtycarpet

...
0
 
Rey Obrero (Capricorn1)Commented:
are there any data validation rule set in the underlying table of your form?


can you upload a copy of your db.
0
 
dirtycarpetAuthor Commented:
no there is no validation rules set up on the underlying table as far as i can see. However i am a beginner in access and in vba.
ive attached my database...
again the problem is on the film form (frmFilm).. open the frmFilm->add record->enter more than 250mins in the length mins text box->add record....this will bring up the warning box.."film length is over 250mins..is this correct"...click cancel...displayed is the warning error i wish to change..
tia
FilmLibrary.mdb
0
 
ElliottJohnCommented:
If the database is ok then just put

on error resume next

before the call for the messagebox - you will then not see any error.
0
 
dirtycarpetAuthor Commented:
elliott john, ive tried this but still the error comes up after i click cancel..
thanks for the suggestion but no unfortunatly it didnt work...
0
 
ElliottJohnCommented:
Have you tried vbYesNo rather than OK Cancel?
0
 
dirtycarpetAuthor Commented:
yes i get exactly the same error.
have you had a look at the attached database? are you getting the error?
0
 
Michael VasilevskySolutions ArchitectCommented:
I would suggest you check the film length value in the After_update event of the txtFilmLengthMinutes control, so if a user enters that amount you have them confirm it before they leave the control. Use the below. Delete your existing: Form_BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim mbxResult As VbMsgBoxResult

If Me.txtFilmLengthMinutes.Value > 250 Then
mbxResult = MsgBox("Film Length is over " & "250 minutes. Is this correct?", _
vbQuestion + vbOKCancel)
End If

If mbxResult = vbCancel Then
Cancel = True

Me.txtFilmLengthMinutes.SetFocus
End If

End Sub
Private Sub txtFilmLengthMinutes_AfterUpdate()
    If Me.txtFilmLengthMinutes> 250 Then
        If MsgBox("Film Length is over " & "250 minutes. Is this correct?", vbYesNo) = vbNo Then
            Me.txtFilmLengthMinutes = ""
        End If
    End If
End Sub

Open in new window

0
 
dirtycarpetAuthor Commented:
mvasilevsky,
thank you, that works as i want it to. However i want to set the focus back to txtFilmLengthMinutes after the user clicks 'no'..Im new to vba so pls bare with me...ive used Me.txtFilmLengthMinutes.SetFocus previously (i think this was correct) but now this doesnt seem to reset the focus..any ideas greatly appreciated..
0
 
dirtycarpetAuthor Commented:
ive also tried
Forms!frmFilm!txtFilmLengthMinutes.SetFocus
but that doesnt seem to work either
tia
0
 
dirtycarpetAuthor Commented:
also when i click 'no' on the message box it saves the film in the database table.
so to clarify.
the message box allows me to say 'no' the film length is incorrect. it no longer throws up the error, however it doesnt reset the focus to the text box required and it saves the record
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post the code you're running the Form's BeforeUpdate and AfterUpdate events?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<the message box allows me to say 'no' the film length is incorrect. it no longer throws up the error, however it doesnt reset the focus to the text box required and it saves the record>>
First, any validation checks need to be done in either the controls BeforeUpdate event or the Forms. AfterUpdate events are too late. In either case, you display a message and set CANCEL = True.
Setting cancel = true tells Access to abort the event. In the case of a control, this means that focus stays on the control. In the case of the form, it means the record does not get saved. If you do your checks here, you need to setfocus back to a specific control yourself if you need to.
Second thing is that if your getting an error message, it is most likely occuring in the forms OnError event. Place a STOP in that event. If you hit that, then you know your getting an error.
DataErr is the error number that was encountered. Response is a variable you set to tell Access what to do. The default is to throw up it's own error message. You can display your own message by:
1. Displaying your message
2. Setting Response = acDataErrContinue
The last supresses the default error message that Access provides.
JimD.

0
 
MikeTooleCommented:
I believe that the reason you're getting this behaviour is that the method used to save the new record is to go to another new record. This command fails when the save of the current new record is cancelled.

The best place to check the value is in the film length textbox's before update event, not the form's before update. That way the focus stays with the textbox.

Private Sub txtFilmLengthMinutes_BeforeUpdate(Cancel As Integer)
    Const strMsg As String = "Film Length is over " & "250 minutes. Is this correct?"
    If Me.txtFilmLengthMinutes.Value > 250 Then
        Cancel = MsgBox(strMsg, vbQuestion + vbOKCancel) = vbCancel
    End If
End Sub

Open in new window

0
 
Sjef BosmanGroupware ConsultantCommented:
And isn't there a vbYesNoCancel, Yes for Ok, No for "let me change it", and Cancel to bail out?
0
 
Jim P.Commented:
The other thing to check -- In VBA --> Tools --> Options is a panel that says Break on all errors vs Break on unhandled errors. See which is selected.
0
 
dirtycarpetAuthor Commented:
guys sorry for a delay with this question. im off work for a week so i will pick it up on my return. thanks for all your input so far

dirtycarpet
0
 
dirtycarpetAuthor Commented:
ok problem solved..
mikeToole..
u get the points!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 13
  • 5
  • 2
  • +7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now