40. VBA - DoCmd (Programming In Microsoft Access 2013) 🎓

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop today we're going to be continuing our series on VBA or Visual Basic for applications we're going to start kicking off some of some videos here that are going to be covering some pretty important objects and methods and functionality in Access that you're going to find yourself using very very regularly and the one we're going to cover today is the do command or do CMD and do CMD do command is a very very handy shortcut to a lot of things you're going to find yourself wanting to do for your users within your within your database so I've got this example button here on my frm employees form here and the way you use the do command is just start off with do CMD and you hit the period here and you can see there are a lot of different methods that are available to the do command and I am NOT going to go over every one of these so I do want to show you how you can find out what each one of these do if I select one here just say for example the add menu method here I can click on the add menu so that the cursor is now inside of the add menu method here and if I press f1 I will get the MSDN article that explains information about that particular method so here's the do command add menu method for access and you'll see down here here are different parameters that we have to have there's the menu name the menu macro name and the status bar text and each one of these fields are required it takes a data type of a variant and here's a description about each one of those parameters you'll also have remarks down here which gives you a pretty good detailed explanation of what the command does and then usually you'll have something down here at the bottom that actually is an example of how to use that particular method we don't have it for this particular one but you'll find that very regularly you will have examples down at there at the bottom of the page now you can do this for the methods on do command object but you can also go right to the do command object and hit f1 and do the same thing and now you will see the do command is actually a property of the object called application okay so here's some examples of how to use the do command alright so you can see this f1 hitting f1 on any one of these keywords even private for example I can hit private and that takes that to the MSDN article on private keyword so very very highly useful thing that you can do just hitting f1 on any of these and it will take you to the MSDN article on a particular keyword alright so I don't need the away from my application to catch back up here I don't need the ad venue I'm going to go over beep now beep is something that is basically going to create a system beep to the user and just as a player I'm going to save that go back to my form here click on the example and hopefully you guys can hear that we just have this beep beep beep going on in the background there okay so that's what the do command dot beep does if I do do command browse to this is also very handy the Browse to allows us to browse back and forth from a forum or to our forum or to a report so I'm going to go to a forum here and you can see we've get the we get the intellisense of what are the parameters that it needs it needs to know the object type which is AC browse to forum and this isn't what we call an enum or an enumeration AC browse to forum is what I need passed there my next parameter is the object name which is going to be frm form which is our calculation form that we were working on I can also pass in the path to a subform control so if I have a subform on the frm form I can specify particular control to go to I can include a where condition a specific page the data mode etc etc I don't even need any of those so I'm just going to go ahead and leave off all those optional parameters and you'll see if I go to the Browse - you'll see that really the only parameters that are required here is the object type and the object name everything else is optional see it's required for those two everything else is optional and here's that a numeration here that I'm talking about a lot of these will have the link to explain to you what the different enumerations do open a forum open a report so that's what those two are different are are for you can actually even use the direct value here for enumerations value - or value three so if I want to open a form I can just instead of doing a sea browse to form I could actually just put in the value - and that'll do the same thing so I'll show you this I could just do this - and that does exactly the same thing okay but it's just helpful to actually have a sea browse to form it just gives you a little bit more a nice easy interface to understand a keyword to know what it is you're going to be doing now the Browse - function notice that we're in frm employees form and when I go ahead and click on it you'll notice that we're no longer in the frm employees it actually closes that form and opens up the frm form for us so you're closing the previous window and you're opening in the new one alright so that's a very handy tool for doing that because there's actually another method and all that I'll get to later which actually opens the new form and then closes the old one and you may or may not want to do that alright so that is the Browse to function let me go back to our code here next one I want to cover is oh and by the way with the Browse - you can you're only really able to go to the form or a report those are really the only two things that you can go back and forth - with the Browse - all right so you can all put a query you can't open a table or anything like that the next one is very very common the clothes function or the clothes method and you'll see we've got database properties we can close the whatever the default is we can close a diagram a form a function of macro module query so we're closing basically a window or a table or a forum or you know basically anything that we might have open and I can actually leave all of those blank because if I open up the MSDN article on the clothes you'll find that actually even the object type and object name are optional and what happens is if you leave this blank is it will close whatever the active form is so right now or I should say the active window so right now the active window is frm employees when I click on the example button you'll see that form closes but let's say that I have another form open I'm going to go ahead and do are I'm going to do something do command open form this is another one of the methods that are available to the do command and I'm going to open up the form F frm form now what's going to happen is because the close command is happening on whatever the active form is what will happen is the form called frm form will open and then the do command close will close it so even though I'm running the close command on the frm employees from the frm employees window because the active window is frm form it will go ahead and close it so the end result is we just do this pretty much nothing looks like nothing happens but what's actually happening is in the background the window frm form is opening and then closing so what we can do is we can provide some of those options so instead I want to specify I want to close the AC form the object name is frm employees and I'm going to leave that I'm going to leave that blank I'm going to leave that for the default so now we open up the frm forum and we close the frm employees so basically the same thing that we saw with the brows to so that is very handy to know that's something that you're going to obviously find yourself doing all the time closing one window and opening another or just simply opening one all right so that's the do command close the next one we're going to do is the go to control and you will find yourself needing to do this occasionally first name I've got a text box called txt first name that is on my form here you'll see txt first name so in the details of my continuous form I've got first name a txt first name as a control and what we're doing is we're essentially setting the focus of access to that specific control but it's the control of the active window so again if I opened up the if I open up the form frm form and then I try to run this let's see what happens notice we get a runtime error there's no field name txt first name in the current record that's because the current active form is frm form and I don't have a txt first-name there so I need to make sure that I don't open any other windows or that I've at least set the frm employees to be the active window when I run this go to control ok so now what this does is it's going to set the focus for me so let's say I am working on I type something in here let's say I I forget something and I want in my code that when I type in I left jeido blank say it's screwed something up I left something blank what you can do is run that do command go to control and you will see it goes and plops it over to that txt first name so if I forgot the first name and I just moved on over here so I'll say I forgot the first name and when I go through and do my validation I can actually automatically I am pop up a little message that says hey idiot you forgot your first name here and it automatically puts the cursor in that on that particular text box so that they know this is the text box that they need to type something in you will find yourself doing that a lot especially trying to help dummy proof a form make sure that they've got everything filled out you can kind of force them to go through and you know you need to go to this text box if it's not already been filled out all right I hope I explained that well enough here to move on that's the go to control now we got go to record and go to record you see I've got in a couple of different places here on my form I've got the go-to record with the AC new rec I'm leaving a couple of parameters blank here same thing with AC previous and AC next go ahead and delete that out so we don't get an error and what we've got here is I've got some functionality that I'm basically stipulating to my user so right now I can click on any one of these records but I can also use these buttons down here to navigate you can see how the arrow over here is going back and forth between my records and that's essentially what the the go-to record function does is it actually goes to the previous or to the next record on my continuous form I've also got this one called a Seenu rec and this is a little bit more complicated and let me explain why I have to do some gyrations here to make this work for you but a very typical setup here is let's say when I've got my form first created I'm going to allow additions and you'll see that I'm allowing somebody to just come down here to this bottom line and just start typing in something but let's say I don't want that let's say I want to make sure that in order for in order for someone to type in a new employee I want to force them to click on this new employee button to make that new employee so the way we're going to do that is first we have to turn off the allow additions property set that to no and you'll see that now it's not available I can go in here and edit so something I can still go in here and and edit the fields okay and just so you FYI if you want to you can all out you can turn off allowing deletions edits and filters okay but for right now I'm just going to go ahead and leave allow additions turned often turned off and the trouble is is that now I can't add anything right so with the new employee button does is a few things first we've got to turn the allow additions back on so we're setting the allow additions property on the form to true and then we're issuing the go-to record with the AC new rec enumeration at the end there and then we're setting the txt username value to a blank space and this is because this txt username is a required field it will not be allowed to be saved unless you put something in there that's at least of a certain length and I'm just putting in blanks there so that it's just left empty then we can set the allow additions property back to false so that we take away that functionality again and what we end up with is no employee ok so that's a way that you can provide that type of functionality and really narrow it down that somebody has to click on that button in order for that to be available alright so moving on here do command next example is the power glass our glass I can turn to true or I can turn it to false that's really the only two things you can do it's a bullion I'm going to go ahead and set this to true and then I'm going to go ahead and add another button here and call it button stop and in here I'm going to set the event the on click event it's good and create do command hourglass false so when we click on the one button for setting our glass to true when we click on the other button we're setting it to false let's see what happens oh look at that I've got the spinny wheel alright basically telling my user hey guess what the application is thinking try not to do anything right now alright now I can write now for example I can change this so Joe Lyons instead of lion and I still have full functionality of my of my computer but it's kind of a way to indicate to the user hey the applications currently working and a very common use of this is to set the hourglass true before you run a big long code block that you expect is going to take you know several seconds maybe even a minute if your application is going to do something pretty long and prolonged you're going to want to set this circle to let them know I'm working don't do anything and then you can turn it off and you see my mouse is back to just the regular pointy you know a little pointy arrow there alright so you can set the do commit or the hourglass to true and then you can set it to false later all right the next thing here we're going to cover is the maximize and then there's also the minimize and then there's the restore so these three functions do what you probably already know they should be doing whatever the current window is whatever the active window is you can either maximize it minimize it or you can restore it which are these one these buttons up here this is the minimize button this is the restore button which just basically shrinks your page down to whatever the previous size was and then of course we have maximize so whatever the active window is you can run the do commands to either maximize it minimize it or set it to restore all right all right the next one there are a series of commands that are open so we see open data access page open diagram open form open function open module open query open report and open store procedure and open table looks like there's open view also so you can open up a bunch of these different forms or queries or reports and we'll go ahead and open up a report here and this is very very handy for like if I want to open our report I'm going to go ahead and open report one and a very common thing that I do is I open the report in preview mode and this is that enumeration that I can select I can go AC view design laid out normal preview is very very common for reports because that'll allow the user to actually see what it would look like when they print it before they say that they actually want to print it I'm going to go and leave everything else a blank here and just show you so there's my report open up in preview mode in print preview and they get the toolbar up here that they can select what they want to do maybe they want to export it to PDF or they want to print it or they want to change the margins or the size or whatever they want to do they can do that from here including the context menu that I can just right click and bring that up alright so that's obviously very handy you want to provide that functionality to your pretty frequently and that's how you would do it go ahead and delete that button since we don't need it anymore don't want to get confused so you can use like I said the open report there's the open forum which we saw earlier when I was doing the open and closing and you can also open tables okay so there's a lot of open functions that are available open methods that are available in it do command next one we're going to do is the output two and this one is very very useful you will you will find yourself using this a lot when you are trying to interact or give the user something to work with AC output I'm going to take a that report that we were working on that report 1 and I am going to output it as AC format PDF all right and I'm going to change it to you know let me just make sure I've got test there you go let me get rid of these here because I didn't like those out before I went through it got to test this stuff out and make sure it works before I present it to you here all right so we're going to do test PDF and auto start we're going to set to true and what auto start does is it means that once I've saved this file because what I'm essentially doing is I'm going to output a report called ARP report 1 in the format of a PDF file and I'm going to save that to test dot PDF in my test folder and then once I'm done saving it I want to go ahead and open it I'm going to set that value to true and now how great is that huh right there is how you can save a report to PDF for your users that's some really neat functionality that I'm sure you've really wanted to know how to do an access and that's what you that's how you do it you just make a button and you can do you can set up your output to and output it as either a PDF file or another option here is let's take AC output table there it is output a table and let's do table one employees output format is AC format XLS and output file is going to be C colon backslash test test dot XLS because remember I deleted those before auto start will set to true again and again if you don't know what this AC for what the AC formats are unfortunately I don't think the help information is all that great on this particular thing here let me see and I see it an AC format constant that specifies the output format if you omit this our our argument access prompts you for the output format so you can actually not specify it and it will ask you what kind of format and that may be exactly how you want it to work so matter of fact let me go ahead and do that I will do there and now it asks me how do I want to output it on a excel a workbook Excel a binary workbook regular workbook HTML an old Excel spreadsheet PF rich text text file XO so we have a lot of different options here I'm going to go ahead and select Excel and yes think about boom there we go there is the table that I had with all the information out put as an Excel spreadsheet that's pretty cool I'm sure you probably got a boss or somebody that really wants to be able to do that somewhere down the line all right so enough with the output to I think you guys get the hint there all right next one is print out I don't really want to go too much into this but you can basically print out just about any screen you can print a page you can print a report I really don't know enough about the print out but I figured you guys might want to know it exists this would obviously be where you can actually go in and do a print again here I'm hitting f1 and I'm taking myself to the to take a look at what the parameters are what it needs print all pages yada yada yada again if you want to find out just hit that f1 key and you guys can find out there's an example of how to use it right there all right so that's print out I hardly ever find myself doing that one all right quit quit is obviously a very probably a self-explanatory one okay maybe I can just show you do command quit bye bye alright we're out of the Access database obviously if you want to have an exit button somewhere on your database you would just issue a do command quit and then there are some there is an option on here there is a parameter of whether or not you want to save everything that you've been working on you can prompt you can save all or you can save none all right so that's the new command quit pretty self-explanatory and then there is refresh record and refresh record and the next one I'm going to show you which is re query are two very similar functions refresh record let me just type this out here refresh record is going to basically refresh your form and sometimes you'll have a calculation that's in here you know like say for example a concatenation maybe the username gets automatically generated by some sort of calculation you put on this text field that takes the first initial and the full last name and combines it together to make the username um you know and you've got a calculation and some information changes on here but the calculation doesn't process if you hit that refresh you know the refresh record it's going to refresh that calculation for you it just refreshes this whole form and makes it updated now the other thing that happens though is sometimes the data behind this form will change so I've got a I've got this data in my record source which may have somebody change on it right I've got an addition to some of somebody in here but it's not showing up on the form well the way you can handle that is if you do a require whoops we go back into my subroutine here so you could do a refresh record or you could do a require II okay and you can specify a specific control so I'm just going to do me name and that would actually refresh the entire screen that wouldn't would refresh the entire form but you can also specify a particular control because sometimes combo boxes remember those combo boxes had a row row source and maybe the row source has changed and so you need to do a require so that you can show the new row source alright so that's a refresh record and where you query a really really big one is the run command and you can see that when i after i typed run command here we get a long list of commands that are available and i clearly don't have enough time to go through all of this with you but AC come in and close all right AC command closed is basically the same thing as if I did a do command close okay these are exactly identical what you're getting here with the run command function is essentially every option that's available on these menus on this ribbon is available using the AC cup run command so everything you want to do up here you can do through VBA using the run command I hope that makes sense to you again if you want to find out what each one of these are um you can just hit f1 and here you'll see we get the enumeration of all the different ones and unfortunately there's really no I wish Microsoft provided some better information about how to do all of this you could do a Bing or Google search if you wanted to on these commands and maybe get a little bit more information from maybe a site such as access forums or something like that list of Microsoft ADT you'll see run command method so here's a developer's reference for 2007 this is going to be the same thing AC command cost it's probably going to be the same thing you can like I said you can do some searching on your own find out what each one of these does if you really need to know I honestly don't use the run command very often but I know a lot of people do because it definitely provides a lot of functionality a lot of access to all those functions that are available you just kind of have to know which one it is that you want all right so moving on here we've got run SQL so our UN SQL and run SQL is going to be something we'll probably get into a little bit later because you can actually do stuff like an insert query Table one employees and you could you can i'm not going to complete this out but essentially I'm building a sequel statement here in as a string and you can pass that string on to the run SQL method and it will actually run a sequel query similar to if I run one of these queries that we made here okay so we could do a query like this if it was an append or an update or a make table query you can actually go ahead and do that using the run SQL function again I'm not going to get into that right now because we will be covering that at a later time next one is save I think this one's probably pretty self-explanatory again we're able to either work on the specific object that is currently active so if we go into close that out here so the Save button here if I'm viewing it it'd be essentially as if I went up here and click on the Save button on the current active form and then you can also specify through parameters a specific form or even a macro or anything really that you might be working on a query or report you can go ahead and save it using this do command dot save all right pretty self-explanatory what save is then there is a very handy method called send object AC send I'm sorry I sent the do command send object allows us to send things like a report so let's say I want to send a report and the object name is report one output format is AC format PDF I'm going to send this to and no carbon copies and subject is tests and messages test one two and three all right so I'm actually going to send an object which is going to open up my outlook and send a report as an attachment which is going to be report one and the format of a PDF file it's going to send it to my gmail account and use test or test and test one two three as the body in subject all right so let's see what that looks like so we got the report being saved as a PDF file got outlook I don't know if you can see that but my outlook is opening up here we got a wait a minute well like your thinks and my microphone I think it's crackling a little bit too sorry for the poor audio of your folks but so there's my report being attached as an attachment it's being sent to that particular person with that subject and that message pretty neat huh that's that's really Andy there are other ways to work with exit with Outlook other than just this I don't know if we're going to get into that at least in the basic series we'll probably get into that into some of the more advanced videos all right we're getting towards the end here yes I know it was cancelled all right make sure you've got some error handling just in case somebody decides not to actually send that object all right do command show toolbar this is a very very popular one because if I don't want to display my ribbon anymore I can use this command the do command show toolbar ribbon is the name of the menu bar and I'm saying no I don't want to show it and guess what happens bye-bye ribbon and then eat so I can actually control the look of my access window right from within the VBA code that is very very important to do especially when you want to protect the data that's in the background you don't want people going in and editing things or being able to change things we can turn off the ribbon so that you they don't have access to that ribbon I do want it to be on though for right now for demonstration purposes so I'm going to go ahead and run this again change that to yes and run it and there we've got our ribbon back pretty neat huh all right the last one we're going to talk about and I I have used this a total of my of while I've been doing database work I've used this a total of two times transfer database I thought you might this might come in handy you might like this at least know the functionalities here I'm not going to go over too much but you can export import and link tables and forms and modules and yadda yadda yadda from other databases into your current database so I can export say for example I wanted to export to an Access and I don't even remember all of the functions here I got a pull up f1 in order to remember them all here but I've got to specify a database type which the default is access so I'll go ahead and leave that and the database name I don't even really know I'm not going to go through all this if you want to learn more about how that works you're welcome to hit f1 you know type it in yourself and hit f1 but just know that what this does is it allows you to export import and link tables or reports or forms or queries from another database into your current database which can be very very handy especially if you want to you know make a backup of your tables and such if you want to export those tables you can do this AC export command so that's very very handy all right well if you have any questions about any of these or if you have if you want any more of these functions to be talked about then you know you think hey Steve you didn't cover this one and I think it's pretty important for us to understand feel free to drop me a line and I'll see what I can do

Channel: Programming Made EZ

Views: 155,047

Rating: 4.9365077 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, docmd, popular commands

Id: fsqCG6ByqVQ

Length: 37min 27sec (2247 seconds)

Published: Sun Mar 02 2014