18. (Advanced Programming In Access 2013) Managing Subform Interactions With VBA

hello everyone and welcome to programming in Access 2013 the Advanced Course my name is Steve Bishop and in this video we're going to be talking about managing your subform interactions with VBA so in our last video we actually had an error pop-up here that was a runtime error 3075 we can see that there's a syntax error in the code that is the actual sequel query that's being put on the active order subform so let's go ahead and hit on the debug button here and you'll see that it drops to where we're applying the sequel query to the record source so let's go ahead and actually look at the sequel query itself by just doing the question mark SQL and that will return back the value that's currently in the sequel variable so let's go ahead and copy this and I'm just gonna throw this up here on it on a notepad here so we can see it just a little bit better and we're actually gonna diagnose what's wrong with this query so let's break this down a little bit I'm gonna take this and look at our select statement just drop this down here okay there's our from statement let's separate that up by a couple lines there's our on statement let's drop that down there's our where Clause and our order by okay so when we look at this we can actually see okay my select section here looks pretty good I don't see anything wrong there my from looks pretty good my where Clause though I can see that the orders equals is missing any sort of value here so the SQL variable doesn't look like it's getting any value in here and this query doesn't make any logical sense because there's no value to compare for the employee ID now that's obviously because we have taken out any value of the employee ID and we haven't really said that there is some sort of default value if somebody selects no employee so we need to fix this to make sure that this sequel query here has some sort of way of if there's no value for this combo box let's go ahead and return a value of some sorts some sort of default value that at least causes the error to go away and produce the results we want now there's one of two ways you can do this you can either have it return all of the results of this of everything here or you can just basically make it return no results I'm gonna go with the no results okay I want to make sure that the only way that somebody can see any data within the subform is if they select a proper employee and the way I'm gonna do that is I'm just gonna use the null to zero function and I'm just gonna say if there is no employee selected then the default value is just going to be zero because no employee is going to have the employee ID of zero and we can go ahead and double check that if we just go into the employees table and just pop this sucker open and we can see there's no employee zero in here okay so I'm just gonna use that as my default so NZ is gonna change this employee combo box to a zero if no employee was selected let's go ahead and save that compile it and let's hop out of the employees ok there we go let's go and go to the design view so we can have another crack at this form okay so we've got no active orders currently we've got the you know the hash tag name if I select an employee say Jan ok I've got no active orders for Jan if I delete the employee which is what I did the last video and after doing that and hitting Enter we got the error message and lo and behold we don't have it this month this time so really this was a logical error and I just went through a process of determining what the problem is when there is a logical problem now there's one other thing that's on here that you guys probably want to know how to fix and that is of course when you go in and you first open the form and there's no employees selected how can we make it so that the active orders subform doesn't show these hashtag names we just maybe you want it to also show up blank well the way you can do that is well there's several different ways you could in fact if you wanted to make it so that the form itself doesn't even show up if there's no employee selected so we could say you know when the form loads here we could say disable the form right and actually that's not the route that I'm gonna go I'm gonna actually take the query that fills in the active orders and I'm just gonna do essentially the same thing that you know when you select zero employees I'm just gonna essentially take that same query and apply it when the form is first opened up so I'm gonna go up here to the active orders form I'm gonna go to the onload event which happens it's triggered when this main active orders form is loaded I'm just gonna hit the ellipsis here and go to the code builder and I'm just gonna go ahead and copy everything that's in here and I'm just gonna go ahead and paste it right into this form load and I'm going to just keep it that way I don't think that there's any reason to change this I could certainly you know set this to just zero if I wanted to I could take out this combo box altogether and just put in a zero if I wanted that would accomplish essentially the same thing but I just wanted to show you how easy it is to just solve these problems sometimes and perhaps maybe somebody presets the value of this combo box before the form loads you know maybe somebody passes in a variable you know passes on a value and say like the tenth VARs or something and sets the value of that combo box before the form loads or as the form is loading and so this would still be a valuable thing to do here to check the value of the combo box before doing the reek weary so it's go ahead and save that compile it and once one more time let's go ahead and view it we've got no employee notice that there's no hash tag name and it won't work until I actually select somebody and let's just select somebody that has there we go andrew has a couple of orders now if I delete the employee out of here hit enter we can see that the active orders goes back to blank so there you go there is a functioning fully functioning filtering of the subform that is working off of the two different events that happen one when the forum when this active forum orders it loads up and the other one is triggered when the combo box is has some sort of selection so if you guys have any questions about this please feel free to drop me a line in the comments section I'll be happy to answer any questions about this video if you don't mind could you please like favorite and subscribe to my videos so that I can know who you are and and kind of keep track of how many people are enjoying these videos so thank you so much and I hope you guys enjoy the next video

Channel: Programming Made EZ

Views: 32,537

Rating: 4.966805 out of 5

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

Id: C83P6doYG44

Length: 7min 31sec (451 seconds)

Published: Sat Jun 27 2015