VBA MsgBox - How to use message boxes in MS Access

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering in this episode we return to our microsoft access playlist and we're going to talk about message boxes which are a very handy way to give some feedback to users uh whether or not you just want to tell them something and then have them click ok or whether you want them to say yes or no as a response to your message box uh and and we're going to look at how to find out um how to figure out the the return values for some of the other message boxes if you don't know uh what the vb equivalents are so without further ado let's get to our message box in microsoft access looking for coaching or one-on-one help on your project check out my patreon the link is in the description okay so this is a file that we've used many times already and i've filtered it for the word message there you can see msg in the search box and i'm just going to make a form so i've just created a new form and i'm going to go to the design view here and we're going to use this form just to demonstrate some of the message boxes that we want to show so i'm going to save it as msg form and now you can see it appears in the list on the left because it's filtered by msg and now i'm going to go to uh my form design and i'm going to just for demonstration i'll throw a text box on there i don't know i'll call it name and uh and we'll plunk a name in there and then and then we'll put a we'll put a button on there as well so that's gonna give us a nice event trigger when we click the button that we can you know see what's going to happen and we can create a message box for our users and so what i'll do is i'll change the name of our text box to something that makes sense and you know to our programming so we know what's going on so i'll put a txt name there and then i'm going to grab a button from the toolbar just like we did and uh your wizard might pop up like this and in this case we're just going to hit cancel and we'll we'll put our own uh title on there we'll call it message and uh this will be the message button and we'll do the same thing that we did with the text box i'm going to click on it go into the properties under other and i'll change the name to cmd message [Music] and that's going to be nice for our programming because when we call cmd message and when we go into here it's going to create a nice descriptive name for it so we're going to click on the ellipsis on the on click there then we'll choose the code builder and we'll go ok and then that's going to pop up our our ide window and now you can see that this form has it's got the form code in here and we have a cmd message underscore click event which is going to fire whenever somebody clicks on that message button and that's exactly what we want and at the top here i'll add a message just saying you know give the give the user a feedback message and i'm gonna put a variable in here i'll call it uh varetval which i usually use for you know sort of uh return values that could be anything if i don't specify the data type on it it will default to a variant data type which is what we want and then i'll just uh we'll start off we won't use the variable just yet um but our first example of a text box is just gonna or a message box is gonna be um you know you click the message button and uh with nothing else attached to it this is the simplest version of message box that you can that you can use and you can see now if i open this up go back to design and then open it in form view now we've got our our form open and if i click that that message button there you can see it's got microsoft access and it has our message in it and an ok button and that's very very basic and you might be wondering you know how can i spruce up my message box can't i add some formatting to it and the answer is yes there's some there's some sort of standardized ways that you can configure your message box a very simple one is just to say vb information in this case it'll put the little i icon in there for information and and you can also put a title on the text box so it doesn't say microsoft access in the title bar of the text box you can put something in there so now now you can see there's the little eye that you know got added to our message and you can see there's our message title and that makes it look a little bit nicer for for users um so you can also you know in your text boxes you can incorporate data from other parts of your application or context based information in this case i'll i just put a name in into the text box and now we can now we can you know get that data as if it was something in our application in this case we're just going to say jim you click the message button but we could change that to another name if we wanted to by changing it on the form and so we can also change the context of it so if i put vb exclamation instead of vb information then it's a little bit more aggressive you can see it has the the yellow triangle with the exclamation mark this would be if you wanted to warn somebody about something [Music] and if you use those ones more sparingly [Music] the the exclamation and critical ones then when people do see them they will react accordingly but if you make every message a critical message it's not going to be that great so in this case i used the critical um type and you can see it came up with the red x on it and that's that's you know if somebody has done something very serious or the application is crashing or something's gone terribly wrong you can use the vb critical in your application so i would again recommend that you use those sparingly the exclamation and critical use information the information type for most of your message boxes unless there is actually something critically wrong or you know you know or needs an ex sort of like an exclamation added to it then then you can use those ones now we can change our our message box and you can see i've added the uh the return value in front of the message box with the equal sign and that's basically means that we're going to ask it to return a value to us and we're going to use the vbs no plus vb question so you can add those types together so you can try try out some of those yourself and now it's going to have a yes no type and then it's going to have a vb question as part of the display of the text box and so now what we can do is now we have a return value once that line has been processed and somebody clicks yes or no then our return value variable there it it's going to have either a vbs or vb no as a return value and so we might you know have a bunch of code that runs for each of those cases depending on what what happened and so this is a nice way to to ask a user hey do you want to do do you want to do this or no you don't want to do it there's our return value and and the else in this case would be vb no i'll just put that as a comment there in case you wanted to know what the vb equivalent is there and so if i go back to our form and i open that you can see hey jim click the message button do you wish to continue there's our title and everything as we set it up and then if i say yes it says okay you can proceed and if i say no it says process aborted and you could have a bunch of other code that runs in the case that he said no and that covers our you know our yes no messages or our okay messages but there are some other kinds of messages that we might use and sometimes we don't know what the vb equivalent is that's a the vb yes we be know there and and so we might want to use a particular box and you know you can google the types and you can go and find those those not those uh those variables that you can put in there you know like vbs or vbno but some case in some cases you might not know what those are and so what you can do is you can you can find out what the message is or the return value just by outputting the value into you know either the debug window or into a message box of their own uh so that you can you know see what the you know what the user clicked and then you can just base your your code after that off of those those direct values so in this case i'm going to say message box process isn't working and we're going to use the abort retry ignore type and you can see that came up nicely and now what we can do is we can we can say well i don't know maybe i don't know what the type is uh is it vb abort maybe retrieve vb ignore it probably is but maybe you know maybe it isn't and if you're working with a different type it might not be immediately obvious and so you can just test it by you know putting the return value into a message box okay that one's a three what if i click on uh what if i check check out the retry well that returns a four and then you know check out uh ignore and that's a five so now we can run our code off of those numbers instead of using uh the descriptive uh variable and that's very handy for you know especially if you're working with other types sometimes it's not immediately obvious which one it's going to be so we could do a select case and direct our code accordingly we could say select case you know a return value case 3 message you know in this case i'll just say message box aborted case 4 message box you know retry or we can go ahead and retry it i suppose and and then for the final case we can say we will ignore that because we know that 5 a return value of 5 is ignore and so we'll say message box ignored and and so that's something that you can do is you can use a select case to direct your code after a message box has been run and that's very very handy when you do use select case you should always put a case else in there in case there's somehow there happens to be some other value that gets returned you should always put it in even if you know or you feel like you know there's no other possible case you should put put a case else in there so that you capture all possible outcomes from that scenario and so now i can click my message button i can say the process isn't working if i click abort it says abort if i click retry it says you know retry now and run some code if i say ignore then we ignored it and that is how you can use message boxes with vba in microsoft access hope you enjoyed today's discussion on how to use message boxes in microsoft access like what you saw today please give the video a thumbs up make sure to subscribe to the channel click the bell when you see the bell and if you have any questions or comments put those in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 3,617

Keywords: VBA MsgBox - How to use message boxes in MS Access, vba msgbox, ms access message box, ms access ok, vb ok, vba yes no, vba abort retry ignore, vba message box input, vba message box yes no, vba message box with variable, vba message box warning, vba message box yes no cancel, vba message box not popping up, vba message box input password, vba message box with timer, vba message box buttons custom, sean mackenzie data analytics and data engineering

Id: JIdjx9lXUcg

Length: 13min 29sec (809 seconds)

Published: Tue Oct 05 2021