36. VBA - Debugging and Error Handling (Programming in Microsoft Access 2013) 🎓

[@ProgrammingMadeEZ]

Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: kzbin.info/door/eXAUvo5xxDY_b-lSknPC1A

[@danielwong5182]

Your videos are outstanding and extremely informative!!! Thank you so much for taking the time to make them and sharing your expertise, I have learned so much from you!!!

[@rjaytenestrante6256]

thank you so much this will really help with all the errors that i may encounter in the near future

[@badboogl8529]

love the videos I'm not sure I misunderstand or disagree with the @6:36 (etc.) use of "syntax" rather than "lexicon" linguistics = semantics + pragmatics semantics = syntax (order and relations) + lexicon (words and their meaning or function) spelling belongs to words, which are part of a lexicon therefore it is a lexical error pragmatics = semantics (utterance or passage of text, etc.) + context (situational matters (said or unsaid) which change the meaning) that's my understanding of it, but perhaps it's convention or something to do with computer programming history

[@jay55patel]

million thanks to you i am learning lot from you you are great

[@z3eem2010]

thanks a lot for this tutorial, i am a beginner on Access and i have seen most of your videos and i have learned a lot about Access programming, i have simple question i think about when to attache the prefix "Me." and suffix ".value" with a text box for example, because i have tried simple code to sum two numbers and give the result, it works with and without theses prefixes and suffixes, you have mentioned Me. is related to the form you are working in but i appreciate if you have more information about this.... thanks a lot

[@ProgrammingMadeEZ]

+Abu Taha Well there isn't anything more to tell you other than what you already know. Me is a reference to the current form or report. Value is a property of an object which tells you what the current value is within that object.

[@rebeccadudek1367]

I'm not dividing by zero-- I'm dividing by STEVE! (X

[@ZakNazif]

Thanks for this wonderful set of tutorials. Great job! Need some help on this ... when the user does not input a password and click the login button the run-time error 94 is invoked. How to program a trap for this error? Thanks in advance!

[@arthurfelde5437]

Can't thank you enough, thx for sharing your experience !

[@FrancescoMediciBruno]

Hi, thanks for your videos. I ask you for help on the vba interface. The right edge of the page where the code is written, where the icons appear: bookmark (in blue), breackpoint (red) and pause debug (in yellow). This border has disappeared and I can't restore it. Can you help me?

[@kalyanakeerthi1397]

I like you lessons I went through 01-14 but not clear video table and queries names etc,. Thanks

[@ProgrammingMadeEZ]

Make sure you set the quality to 720p. Anything lower and it will be hard to see the text.

[@adrianeland87]

This is great! Did not know any of this.

[@stevensilcock]

Maybe I missed something, but how do you get the yellow arrow & highlighted code without having to run the code first? Say I'm already in VBA & I just want to debug it, how do you insert the yellow arrow so you can F8 to step through it?

[@kadnacer7571]

Hi Steve, My question is; how can we pause code until opening another form to gather some data and then continue the code after closing the form.

[@EverydayElevateHub]

Thanks for this wonderful tutorial, I'm learning a lot . Is there any tutorial on "Transactions" ?. Another Q , the term 'Stored procedures' are the queries that you are building up Or ... ?

[@ProgrammingMadeEZ]

+Lucien Chemaly Those would be better suited for a SQL Server series which I have no plans to work on at this time. Perhaps in the future, but I'm mostly focusing on the development side rather than the data side. Perhaps, since one of my topics in the future will be on Entity Framework, I will also need to cover stored procedures at that time. However it will be quite some time before I can get to that subject. I"m still working on the basic C# series.

[@markcunningham2862]

I would like to cast a vote for a tutorial on transactions in Access as well. My interest is as follows: I have three tables that have new data added with append queries. Data must exist in table 1 in order for the table 2 append query to run successfully, and the table 3 append query requires data in table 2. I do this using three blocks of code each block with it's own error handling (one block is shown). On Error GoTo TableAppend_Err DBEngine(0)(0).Execute "qryAppend", dbFailOnError MsgBox DBEngine(0)(0).RecordsAffected & " record(s) added to Table." If a block fails then there is a rollback of that block (inherent with the DBEngine(0)(0) function I guess) - I'm not sure. I would like to run all three appends within one transaction so that if any one of them fails, the whole thing rolls back to the original state. So as you see I know enough to kind of slosh my way through, but I would like to learn more.

[@jhunjonerodriguez4158]

thankyou very much!!! well explained! thanks alot!

[@ProgrammingMadeEZ]

You are most welcome jhun!

[@supersmash6058]

I keep getting a compile error: function call on left hand side of assignment must return variant or object

[@bigbubba6293]

Question: I was told that it was not the best practice to use GoTo. Can you show another way to do this? Thanks

[@ProgrammingMadeEZ]

This is the only way with VBA. Other languages like VB.NET, C#, Javascript, and Java we have a try/catch/finally... but in VBA we only have Goto.

[@DittoGlen]

A single line with the keyword "Stop" will also stop execution of the code. Handy in some instances. Edited for clarity

[@ProgrammingMadeEZ]

DittoGlen Yeah the Stop keyword is basically a programmed break point.

[@martinniewiall]

Hi Steve, i want to ask you in which event do i have to program the "On Error Code" to validate the data before Access do it, when for example a user wants to delete a field that it's required and don't permit zero characters. I tried it in a lot of Events, even in one called "on error" (or something like that because i have it in Spanish) and in that particular case the msgbox that i created in the label (from the On Error) appears, but when you click on the Accept button then the "Access msgbox" appears too, so i couldn't pass the error. I just add the msgbox that i created before the error.

[@ProgrammingMadeEZ]

+Martin Niewial On Error is meant to only be triggered when there is some sort of problem. You typically don't want it to be triggered unless you've done something wrong in your code.

[@martinniewiall]

+Programming Thank you Steve. I'll leave the "On Error" only for VBA code. Also, i changed the properties in the fields required to " NO" and activated a validation rule "Not Null" accompanied by a "Text Validation " and this improved the alerts in the required fields that was my problem. I just wanted to commented for anyone that may have the same problem. Thank you again!

[@mikeoxhuuj1276]

Hey man! There is a title us used in the US which never fails to annoy the shit out of me as well as some of my mute friends and that is #Dumb# Dummy. or any derivative of . I know you mean no harm but not many of my friends and followers of KZbin informational for many of my colleagues. Were someone who is deaf and/or dumb they are labelled 'mutes'

[@dbdata9836]

thanks

[@laser1915]

well explained buddy

[@ProgrammingMadeEZ]

Thanks.

[@DittoGlen]

An after-thought after re-watching, If you don't want to expose your code to end users in the event of an error, password protect your project. Then in the event of an error, the VBA editor will not open with your code visible.

[@ProgrammingMadeEZ]

DittoGlen Password protecting your project won't prevent them from viewing your code. Just save your project as an ACDE file and that turns your VBA into compiled code.

[@DittoGlen]

Programming I was speaking about casual, non-programming users. What I was refering to is a run-time error being raised.It used to be if the error was raised, then it automatically opened the VBA editor. If you password protect the project then the VBA editor will not automatically open the editor showing the highlighted line where the error occured. I cut my teeth in Access 2003. If that behavior has changed, then I'm just unaware of that fact.

[@ProgrammingMadeEZ]

DittoGlen Hmmm... that may have been true for 2003 and honestly I've never really tested it for 2013. I just assumed that once you enter the password in you get unfettered access.

[@smith4718]

My login button doesnt do anything when i click on it ,HELP??

[@marcinofsky]

Wow!

[@josephkreifelsii6596]

Was it too much for Microsoft to just give us a Try..Catch block?

[@HarveyFrench]

Yet another very useful video. Thanks. Other languages and environments offer better error handling features and VBA error handling is very basic. I came across this paid for suite of classes for Access that massively enhances the way errors can be handled. Key benefits are: - It simplifies all you code - Allows you to identify the source procedure and module name where the error occurred - List the exact line number to identify the line of code that failed - Report the full callstack that led up to the exception - etc.. See [vbWatchdog](www.everythingaccess.com/vbwatchdog.asp) I've not used it but it is stable and reliable Harvey

[@ProgrammingMadeEZ]

Harvey French Indeed it would be nice if there was a Try/Catch for VBA.

[@HarveyFrench]

vbwatchdog gives you a "Try-Catch paradigm for simplifying local error handling"....

[@FrancescoMediciBruno]

Hi, thanks for your videos. I ask you for help on the vba interface. The right edge of the page where the code is written, where the icons appear: bookmark (in blue), breackpoint (red) and pause debug (in yellow). This border has disappeared and I can't restore it. Can you help me?