41. VBA - CurrentProject (Programming In Access 2013)

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop and today we're going to be continuing our series on VBA or Visual Basic for applications or continuing our discussion about some of those magical objects that are in access and those magical properties and methods and such that are in access that we will find ourselves using pretty consistently and today's subject is going to be the current project so let's go into our database here and I've got a few of the different functions that are available for current project set up here there are more than it's available and we'll go through and see that there's a pretty significant list of different properties collections and methods that are available on the current project but here's just some of the more useful ones add shared image this is a very fun one essentially you've got in your database you've got different resources which we can find right here if we go into the all-access objects and we right-click here we go to the navigation options there is an option here to display hidden objects and system objects you don't want to release any of your databases with this available okay I just want to show you that this is how you can get at some of those system databases and in particular we have one called M sis resources okay if I open this table up you'll see that there are attachments actually inside of your database which are actual files that's what this attachment file is and it's indicating that there's one attached file it's extension is THM X has an ID of one has an office theme as the name of it and the type is THM X we can add resources to our databases in the form of files and one of those is if we want to add an image so the add shared image which we have here with current project dot add shared image we give it a an easy name that will be placed in this resources table this name here as well as we want to give it a path to where we can find that file and I've got an image here in my test folder called good jpg which we're going to go ahead and add to our database the next thing I've done is I have an image frame here that I'm going to go ahead and assign to the frame the picture property is going to be good image which is that name that we named our image in our resources and when I do that when I go back to our form here you can see there's our image in the frame that I have created so that's great we've got an image now actually inside of our resources and if I look at it now I got to close our resources you're temporarily and reopen it you'll see now we have good image is a name in there it's extension as jpg we have the actual file right here that we can double click and open and take a look at it inside the table and that's the image that we have still there's a lot of neat things that's going on there we're kind of being able to just go ahead and throw an image in the resources for the database now suppose you want to remove that well we have a collection of objects which are called let me drop down here - it's the resources collection all of the current project so we can access essentially this table here pop back open here this sis resources table we can actually access it because it's currently a collection inside of the current project so I can go ahead and actually look inside of this by doing a for each loop I'm setting an item as an object and for each item inside of my resources collection I'm going to check to see if the item name is equal to that good image which is what I named it up here right then if it is equal to good image then I'm going to head go ahead and delete that item all right now the first one the item name is actually nothing there is no name second time through the name is oh come on now I don't think that's actually quite right let me try that again I think some happen my intellisense here let me try that again for each item in current good okay so the first time through it should be that office theme there we go and then the second time through is when it's going to say good image and since it does equal good image it's going to go ahead and delete it and then we're going to go ahead and update that picture property to be blank because it no longer has a name here we're passing it a string and the first time we were naming it the string that we gave our image that we stored in our resources but now we're going to leave it blank so we're just going to go ahead and put an empty string in there and when we do that now when we look back we can see that the image is gone I'll just go ahead and comment you out this so you can see that this is essentially a seamless way of integrating an image into our form okay so we can add a picture we can remove it and we're actually essentially doing it right on the fly you can see that that image is now gone this is not necessarily the best way to go about doing that and I just want to also show you that we can actually do an insert image here if we go into the design view of the form we can do the insert of an image in our image is going to be there you can browse and find an image and this is essentially going to do what we're doing through vba code so we can go through and find that image which we have on our C Drive under test and there's our good image okay so that is how you can use the add shared image method and then you can also access the resources collection to go ahead and delete a resource right out of your MSS resources table all right go ahead and open this back up remove that all right so now our resources should have it's gone all right perfect all right next up is the all forms now what will commonly happen is that you will want to be able to go through and find a particular form to do something on so we've got our customer addresses employees form and login and you may want to pick a particular one to perform an action action on so far we've used the keyboard of me and me just points to essentially the form or the report that is currently open but suppose we want to specify a very particular form but we could go through and we could do form forms and then we could pass it the name of the form like this okay but one of the other things we can do is we can actually go through the whole collection of forms and see what's available so we can do once again a very similar thing to what we did with the resources we're going to create an item as an object we're going to say for each item that we find in all forms I want you to go ahead and print out to our debug window our immediate window the name of the form and the last time it was modified so you could see that might be very handy for determining which forms we need to update on this particular form so we can see the last time the form customer addresses was updated was at 8:01 p.m. on March the 1st of 2014 ok and we can continue on through here and it's just going to continue like this ok so we have essentially that functionality to go through all the forms but you can also iterate through using instead of for each method you can actually use the four method which is very similar in syntax as you already know we're just going to create an I is an integer and then we're going to look through all the modules and I'm going to go for I equals 0 because we're starting out all the all modules collection starts out with a zero base 0 is the first item listed ok so for I equals 0 to the count of all modules minus 1 because if we if we left off the minus 1 we would error on the last one it would go ahead and try to iterate through again but there wouldn't be a module there and we would get we would get an error so we've got to make sure we have the minus 1 there and then we're going to go ahead and print out essentially the full name and the date modified just like we did for the all forms but now - now notice we're talking about all the modules so we have in the current project we have lots of different collections of objects that are in our projects - current project I have all forms macros modules and reports that I can access in a similar way okay let's go ahead and clear all this so for our first one we have CLS customer which if we look in here CLS customer is our first module next one is mod test which is there and then our third one is CLS test ok there's no particular order a matter of fact I think that they're ordered in the date that they were created is probably the most likely case alright so that's how you can iterate through different collections of both forms and modules and you can also look at reports and I believe macros was the other one all right next up let's go ahead and take a look at the file format a file format is going to tell us what the current projects file format is which as you know there are different versions of access so if we look here we get an a return value of 12 well that's not very helpful but that's because it's returning an enumeration of AC file format and I have an article listed here that we can go to just go ahead and open up my planet Explorer here and show you what the AC is the AC file format enumeration is so we can see the value is 12 which returns back the AC file format acts as 12 enumeration which is Microsoft Access 2007 format all right now even though we're running 2013 I know this may be a little confusing the format which is accdb is actually a 2007 file version and they haven't changed that okay it's the format of the database all right so that is the file format and that could become very helpful if you want to make sure that you know that the file is not and is not antiquated it's not too old to run the code because sometimes you may try to do a conversion of a file back to a different type so we can go up here we can save as and then we can change to an MDB file but if we try to go here and save this file as an MDB file notice we're changing it to access 2000 to 2013 which means some of the things that we were using in the accdb format some of the VBA code may not be workable in an MDB format because some of the different functionality has been deprecated and is no longer available so you need to make sure and and one of the ways you can make sure is to take a look at the current project file format and make sure it is returning the correct value before you continue running some of your VBA code all right moving on let's go back into our database here we've got a full name now full name gives us the entire Access database location and file name so we can see it just simply returns the full path and file name so we got both the directory this all the sub directories that drill down to where our file is located as well as the file name we'll see that there are other that there are other properties of the current project that actually gives us this part of the current project and this part of the current project and that can become very very handy when we want to start storing information or picking a particular location that we want to store our files in all right so that's what full name is we also have this is connected which just basically says you know is my database is the current database that I have open is it actually connected and I'll just go ahead and f1 this and show you we're looking in the current project is connected to determine if the current project object is currently connected it's read-only bullion doesn't really give us a whole heck of a lot of information but what I can tell you is that it tells us whether or not the current project is if there is some sort of network disconnection so if your database that you're currently working on exists somewhere on the network and you current and you're having trouble or difficulty connecting to it this can be kind of a quick little check to see hey now I currently able to connect to my data all right then we have the name which is going to go ahead and return just the file name which is what I was talking about before when we were looking at the full name up above here we can just return the name of the file and this can become very handy especially if you want to make a copy of the current database so let's say or let's say we wanted to change something let's let's do output Abdul command output AC output report and it's for port 1 and now put form as AC format PDF and output file now this is where we need where we want to specify the location that we want to put this report file in right so what we can do is we can say well I want to put this in C colon backslash test but I want to give it the name of my current file let me see here so current project name and let's say I want to change I need to change the extension here don't I because I want to make it PDF so I'm going to use the replace function to replace on here the extension dot accdb with dot PDF wrap finish wrapping that up do I want to auto start it now I'm going to turn this to false for right now so now what will happen is hopefully didn't fat-finger anything export our our report here to the test folder so let's look at what's in our test folder and there's our Service Inc PDF so it has the name of the actual current project as the name of the PDF file and that cool so that determine what the what the file name is of the current file of the current project alright the last one we're going to go ahead and look at is the path and this one I use pretty regularly because a lot of times I need to look at where the where the current application is running at I don't want to make a subfolder underneath that so let me go ahead and run it here this is a similar example to what I just showed you before we're going to go ahead and print it out to our window and you'll see let me go ahead and clear this out so that we can review this you'll see that it is the full directory but notice that we are a month we are missing that backward slash so you need to make sure that you add it to whatever you're going to be naming it notice here we have the do command output 2 I'm going to output that same report but now I'm going to add it I'm going to output it to the current path of wherever my project is which happens to be here and I'm going to output it as our PES report dot PDF so we'll go ahead and continue on here once again we output that report but now if I look in My Documents folder which is where this location is here we'll see that there is my report PDF file that I just stored now that becomes very very handy when you need to create a series of subfolders underneath the location of wherever your current project is so you know if I wanted to make a PD PDFs folder I couldn't really do it here I actually have to do something looks like this and I'm getting a little ahead here but well then FSO as file system object you know what I got to do it as an object first and I got to set my FS o equal to create object scripting file system object do to do and then I can do FSO create folder and then here is where I would do current project dot path and I need to do back slash PDFs back slash is fine here and close that up and actually I don't need don't need parentheses around that alright we comment this out here so that this runs properly and we'll see as long as I mentally got everything right here create myself the folder and now I should have a people see the baby because now I have a subfolder underneath where my service Inc location is running I have a subfolder running underneath that and that will become very handy for what you want to organize and put files inside of there so now I can do something along these lines here go ahead and comment that out yes now my PDF file will go into this PDF folder there's our report okay so clearly that's a very handy utility a handy ability to go through and find out what the current path is of where your project is currently running and then you can create subfolders underneath there and start storing your data inside of that and we'll cover the FSO object a little bit later that's a really neat ability that you have to interact with the file system there just want to show you just like before when we were talking about the do command you can go current project like this and you have all of the intelligence that will give you all of the different options of all the different properties collections methods etc that are available on the current project object alright so I hope that that helps you out if you have any questions as always please please feel free to put one down in the comments or send me a message on YouTube and I'll be happy to answer as best as I can

Channel: Programming Made EZ

Views: 51,054

Rating: 4.9865322 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, currentproject

Id: DJhJfrPs4CQ

Length: 21min 34sec (1294 seconds)

Published: Sun Mar 09 2014