Bound vs. Unbound Forms and Controls in Microsoft Access

i'm your host sean mackenzie thanks for joining me once again on my channel on data engineering in this episode we go back to our microsoft access playlist and i want to talk about bound versus unbound controls and bound versus unbound forms because if you've just started developing in microsoft access you're probably wondering what this means and how it can be used and why you might change the way that it's used and so i thought i'd get into a discussion about it today i won't be able to cover all of the aspects of bound versus unbound because there's lots of different ways that you can do it but we can certainly make a start so without further ado let's get to our bound versus unbound field and form controls in microsoft access need help for coaching on your project make sure to check out my patreon the link is in the description okay so this is a pretty uh fun discussion i'm gonna go back and use our candy tables here that we used in some previous episodes as you can see i've got this simple candy table with some prices on it and what i'm going to do is first i'm going to create a form using the form wizard and that's sort of like the easiest way that you can create a form i'll create a columnar form and i'll take the you know default options and then i'll just change the name and say you know candy bound as the form name and i won't change anything on it i'll just say finish and this is going to give us a bound form and what that means is that you can see here down at the bottom that when we chose candy table as sort of like the source for this form we can scroll through and we can look at all the different candy in this form and you might be wondering well you know uh how can i do things you can also add a new a new uh candy into there and you can scroll backwards and forwards and you can change the data and this is a pretty like standard you know way that access you know deals with a bound form it's using what's called a dao record set underneath and and it's just opening this form and as you can see if i click down here in the gray it'll give me the form properties also you can choose it from the list at the top but if i click on the data tab in the properties you can see just as i chose in the wizard it is using the candy table as the record source and that means that this form is bound to the candy table now you can also put in you know like a select statement in there so long as the record set is updatable you can actually put just about anything in into the control source and so as you can see as i go through you can also see that the individual controls like candy name here and candy price those are also bound they're bound to the fields in in the forms record source and so when we open the form and we scroll through it those fields are bound to the data that's in the field in the record set which is kind of underneath everything as you can see in the record source in the top right there the candy table is the record source for this form but what if we wanted to have a control that didn't have you know a binding to a field in our in our table and we can do that by just grabbing a text control from the controls on the ribbon there and we can drop this text box onto our our form and then as you can see the default sort of settings there um you know it's got a label and i might call this candy price again because we'll i'll show you how to load a text control into an unbound form [Music] where you know if you change the value in the unbound form it won't change you know the field values in the table unless you specifically tell it to so now we have an unbound text box and i'll give it the name txt candy price and what this can do is uh this will give you something that is separate from the candy price field it this text box is its own thing i'll give it a i'll give it a currency format and and now we have uh an unbound uh text box which you know we can put anything in there and it means absolutely nothing to the record source record set underneath at this point so you can see i can type in a dollar into this text box and as i scroll through the records here you know it doesn't pay attention to what the record set is doing it doesn't really care because it's kind of like this this dummy control and you know you can put any value in it and it doesn't really change because it is unbound so it's not actually bound to that record set whereas the other fields are bound and so when you change the the record when you move to the next one next record it will you know change the values in those fields accordingly and so this is where you can start to see how the unbound sort of context works and so you can see up here we've got our text box and it has its own set of properties and everything and so what we could do is we could you know use the on current event of the form so i've switched over to the form to the form properties now and when i click on the ellipsis by you know form current it gives me the vba window here the ide for vba and it gives me an event called form current which happens when each time that the form moves to another record um it fires this event so we could say hey you know hey me text box candy price is equal to me candy price and the form knows when you execute that in vba the candy price it knows that that is a field and it knows that txt candy price is a text box because those are the objects that are associated with the form and so now when we open this form if i open it you can see now as i scroll through that event gets fired and now the candy price again field is showing the candy price again and so this is one way that you can do this when you when you do have a bound form because as you have probably already guessed this form is still bound to the record set but the control is not bound the txt candy price uh control text box control is not bound um so if i wanted to i could also you know after i update the value in txt candy price i can also i use that to update the actual records value and you can do that by going you know doing the exact opposite of what we did before so now i can do candy price is equal to my txt candy price value and it'll take the value from the text control and it will update the actual value in the in the field as you can see as i scroll through here it's firing that first event to load it with the current value but if i update it it will also update the the real value in the uh in the record source for the form so that's very handy so you can imagine you could actually not have that candy price field in there at all at all or the the text control for the candy price uh field the the upper one there that has you know the bound one you could remove that and just have an unbound control and you could have all kinds of logic around that just you know depending on your situation how that might work and this is essentially how you can have unbound controls on your form like text boxes and check boxes and you know different kinds of controls you can have unbound ones on your form that update you know real uh field values in the record source of the form because as we mentioned before as you can see this entire form is bound to the candy table and we have one unbound field in there or one unbound control which is updating and you could remove the bound candy price field if you you know put enough event you know events around the updating and all that stuff to make it to make it work and that can be very beneficial in cases where you have a very slowly loading form or or you know those kinds of situations you might find that using an unbound control set might work for you and so you might be wondering you know what if i just created a completely unbound form well then you'd have an unbound form with no controls on it and any controls that you put on it will be unbound as well because the form does not have a record source and so uh you know we can grab a text box just like we did before so this form has nothing attached you know no data attached to it in terms of bindings and so i could create a candy price field on this you know completely empty form and uh you know i could go and do just like we did before and put you know txt candy price and you know that kind of stuff and the thing is if i you know if i look at this form you know i i can click in the gray area there too to get the form controls you can see there's no record source for the form so it really doesn't mean anything and it's not connected to any tables or anything like that so it's not going to update anything yet you could you know put the logic in here in order to to bind it to a record set but you can also bind it other ways like so if i if i save this and open it i'll call it candy unbound and you could just put equals one two three and if i you know go try to change that you can see down at the bottom it says control cannot be edited edited it is bound to the expression one two three um and so that's how binding can work as well you can have it bound to something that's not even a record set a record value but you could also do something like hey i'll put a dlookup in there then or you know to to grab a value from a table some unrelated thing to whatever my current you know table my current context is or maybe you know you want to just look something up so this if i just grab a d look up here it'll grab the first value that it finds in that table and um you can see it grabs 15. so now you can see this form does not have a record source but it's getting you know the the first candy price out of the candy table and you could do this for all the other you know values as well if you wanted to and then you would have an unbound form that's displaying the first record of the candy table even though it does not have a record source there are other ways that you can do it you know you can there's ways that you can load up and i'll look at my previous videos on you know how to open record sets and in and create a record set because that's how you would probably load your unbound um your unbound form but in this case what i'll do is i'll just say you know on load you know my candy price is equal to the same d lookup that i did before just to demonstrate that we can load it during the uh the form load process check out my video on form load uh form and form current that i did previously to show you how that stuff works but now now what i can do is now it's an unbound form and it doesn't it's completely unbound it doesn't have an equals and you know equals something in there but when the form loads up it's going to go get that d lookup value that first value and it's going to put it into that field and so that unbound field gets a value and so you could do the same thing as what we did before where you know after update if you update it it'll go and do an update as well but you need some more logic for that and i will show that in a future video if you guys are interested in seeing how a completely unbound form might work we could do a video on that i don't think we have enough time for that today but we would use you know a whole bunch of different events that are in here after update events for particular you know controls and things like that or you might have an update button that would grab all the values in the unbound fields and then put you know update a specific record that might be chosen but i will also note that the bonding works in wonderful ways so this is a linked table to an azure sql database and the great thing is is that if you create a bound form on a linked table just like this one it operates almost identically to how your bound form on an access table worked and so that's very very handy because if you have or if you've upsized or if you're migrating or if you just want to have some some data in azure or on sql server or another database if you're using that dao bound form on a linked table you're getting a whole bunch of functionality including the record locking and all kinds of things to make sure that users don't update things at the same time and resolving conflicts and all those things that's all handled through that that odbc connection that we created for our linked table and you can use bound or unbound methods between each of those are your programmer looking for your next contract make sure to check out my additional links in the description hope you enjoyed today's discussion on bound versus unbound forms and controls in microsoft access if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel if you didn't subscribe yet and click the bell when you see the bell and if you have any questions or comments please 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: 321

Keywords: bound vs unbound forms and controls in microsoft access, access bound form, access bound control, access unbound form, access unbound control, access form record source, access control source, microsoft access tutorial, understanding binding in ms access, sean mackenzie data engineering

Id: l75GsjduJuA

Length: 15min 35sec (935 seconds)

Published: Tue Nov 23 2021