quit command

MS Access Vinyl Database 3 - Creating a Menu Form

Geeks Computer Tech - 11.06

At the end of the previous article I optimistically said that in the next article (i.e. this one) we would create both a menu screen and our first data entry screen. Far too ambitious for a single article! We'll create a menu form for the music database in this article - but the only button on it will be the Quit button and therefore the only thing it will do until the next article (probably the next couple) will be to close both itself (the form) and Access itself. But think of the fun you can have with that!!! This is going to take a while - I'd suggest you have a good hour set aside. Did I mention we are going to totally ignore Wizards in this series of articles? We're going to be doing some whizzy stuff alright, but far easier to do it from scratch than have Microsoft Wizards thinking they know what you want and then you having to change it all about... Ok, the first thing we have to do is open our database either by double-clicking the database file icon or by opening MS Access and then opening it from the list on the right of the opening screen. Once it is open we'll create a blank form. <center><a href="http://2.bp.blogspot.com/-ACMbcqkRn-s/U6GuioC687I/AAAAAAAANNI/Rr79GGyLPFM/s1600/20140618a.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-ACMbcqkRn-s/U6GuioC687I/AAAAAAAANNI/Rr79GGyLPFM/s1600/20140618a.jpg" /></a></center> Start off by clicking on the word "Create" and then click on the icon labelled "Form Design". <center><a href="http://4.bp.blogspot.com/-9KYBV-ibTbo/U6GvXrE_WmI/AAAAAAAANNQ/jrotXDfq9ko/s1600/20140618b.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-9KYBV-ibTbo/U6GvXrE_WmI/AAAAAAAANNQ/jrotXDfq9ko/s1600/20140618b.jpg" /></a></center> This will create a blank form with the default name "Form 1" and we'll get round to changing the name in a moment. First of all let's take a look at the screen. The bits of our form that we want ourselves or other users to see once it's working is under the label Form 1 currently highlighted in orange. There is a single block under the heading "Detail" which is the main blank canvas that we can work with. Over on the right hand side is the Property Sheet. This has a range of headings and values relevant to what is shown in the first box under the heading "Property Sheet". It is currently showing "Form" so we are seeing the properties for the entire form. Later once we start to add items to the form we can view their individual properties in this column. There is currently no caption given, so by default if the form was in normal display (rather than the Design view that we are currently looking at) the tab description would show the form's name. We want it to show the text "Music Menu" so click into the cell to the right of the one saying "Caption" and type "Music Menu". <center><a href="http://1.bp.blogspot.com/-iwXNDYm9_vo/U6GxQTfjFqI/AAAAAAAANNc/ktbtu9F6odo/s1600/20140618c.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-iwXNDYm9_vo/U6GxQTfjFqI/AAAAAAAANNc/ktbtu9F6odo/s1600/20140618c.jpg" /></a></center> I know - you've just done something really great and want to see it in action, right? So move the mouse cursor top left and click to drop down the list of possible views as shown. We want the Form View but as you can see, there are a few possible different views. Design View is highlighted because that's the view we are currently in. Click Form View. <center><a href="http://4.bp.blogspot.com/-GqGlN4OAMts/U6Gx8RIw-TI/AAAAAAAANNk/iaNcv1e0yRA/s1600/20140618d.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-GqGlN4OAMts/U6Gx8RIw-TI/AAAAAAAANNk/iaNcv1e0yRA/s1600/20140618d.jpg" /></a></center> Fantastic! The tab heading says "Music Menu" or whatever you thought you could sneakily call it without me knowing! However there are a couple of bits present by default that we won't need to have showing on the form. No.1 is the record selector. Usually a form is for showing records from the database and clicking in the record selector on the left will allow you to do something - like delete it... We won't have any need for that because this is going to be a menu screen and won't have any table or records associated with it. No.2 is the navigation buttons, also used to move forwards or backwards through a set of records. We don't need those either on this form. <center><a href="http://2.bp.blogspot.com/-saZ7vDEYsdU/U6GzKdsCcwI/AAAAAAAANNw/OlUJ-FoqlBQ/s1600/20140618e.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-saZ7vDEYsdU/U6GzKdsCcwI/AAAAAAAANNw/OlUJ-FoqlBQ/s1600/20140618e.jpg" /></a></center> Go back top left and return to the Design View. We now need to make a few changes to the Property Sheet for the form. I've altered a few and the diagram above shows what we want them to read. First off I've changed a few of the possible views from Yes to No, only leaving the Form View. Further down I have changed both Record Selectors and Navigation Buttons to No. Because we are not going to design a menu screen that is too big to be seen all at once on your monitor I've also turned Scroll Bars to the option Neither. <center><a href="http://2.bp.blogspot.com/-uTZOZvQJkGY/U6G0mIKQv9I/AAAAAAAANN4/_kC6v7rLx5Q/s1600/20140618f.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-uTZOZvQJkGY/U6G0mIKQv9I/AAAAAAAANN4/_kC6v7rLx5Q/s1600/20140618f.jpg" /></a></center> Test things out by going back top left and clicking for the Form View. When you have had enough of looking at a blank form without record selectors, navigation or scroll bars, come back to the Design View. Ok. Having got back to the Design View, I want to show you how Access reacts to the context of what you last clicked on. We'll do this by right-clicking in a few different places. <center><a href="http://2.bp.blogspot.com/-qcxOfRY0A1s/U6G1lkLSuBI/AAAAAAAANOA/6wXA1NqLCws/s1600/20140618g.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-qcxOfRY0A1s/U6G1lkLSuBI/AAAAAAAANOA/6wXA1NqLCws/s1600/20140618g.jpg" /></a></center> Right-clicking the tab or the blue bar to the right of it allows you to select options based on the entire form. We can save it (don't yet...), close it, close all open objects, or move between the different views. Because we just turned some of them off in the Property Sheet, the only ones now available are the Form or Design Views. <center><a href="http://4.bp.blogspot.com/-gKIm-4GQQIE/U6G2fO2pB1I/AAAAAAAANOM/kIXzIaq85aA/s1600/20140618h.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-gKIm-4GQQIE/U6G2fO2pB1I/AAAAAAAANOM/kIXzIaq85aA/s1600/20140618h.jpg" /></a></center> Right-clicking the box with the black square (a handy box to select the form properties into the Property Sheet) or the horizontal or vertical rulers gives a second set of options. We'll look at events later in this article and at Tab Order once we create a form based on a table. The Properties option toggles the Property Sheet on and off. In my experience I always want it to be visible. <center><a href="http://3.bp.blogspot.com/-QO7etOUApZU/U6G4mJ9YboI/AAAAAAAANOY/oA-IoLpygqc/s1600/20140618i.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-QO7etOUApZU/U6G4mJ9YboI/AAAAAAAANOY/oA-IoLpygqc/s1600/20140618i.jpg" /></a></center> Finally I've put the mouse cursor into the blank block representing our form's canvas. A right-click here opens a list with some of the options we've already seen but there are a few new ones and we're going to test out a couple of them next. Note that we can toggle on or off the ruler and grid lines. Both are highlighted because both by default are on. I'd strongly suggest leaving them on. Particularly on forms with lots of objects, the facility to have them snap to the grid lines is very useful. <center><a href="http://2.bp.blogspot.com/-UaMjvmh6_5I/U6G6M55D_cI/AAAAAAAANOk/UeDjSRfQhxM/s1600/20140618j.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-UaMjvmh6_5I/U6G6M55D_cI/AAAAAAAANOk/UeDjSRfQhxM/s1600/20140618j.jpg" /></a></center> Feeling artistic, I now want to change the colour of the canvas and therefore I'm going to change the Fill/Back Color and choose a medium grey (always the adventurer!). We will use colour - some of it quite soon, but database screens should never look like a 1970s shirt... This canvas is where all the buttons to open more useful forms and reports will go. Note, now that you can see all the bottom options that there are two header/footer options and we can again toggle the Property Sheet on or off. The Form Header/Footer puts a separate bit of canvas up at the top and down at the bottom of the form. The Page Header/Footer does the same sort of thing, but if you had a table with so many fields that it wouldn't fit on a single screen, you can then split it onto more than one page instead of scrolling down and have the same header/footer on all of them. <center><a href="http://1.bp.blogspot.com/-IZf0TwNsNoA/U6G8JP6TUCI/AAAAAAAANOw/n-p_HXw5aIY/s1600/20140618k.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-IZf0TwNsNoA/U6G8JP6TUCI/AAAAAAAANOw/n-p_HXw5aIY/s1600/20140618k.jpg" /></a></center> In fact we'll enter a Form Header/Footer right now. The canvas has changed colour to the one we selected, I've right-clicked the canvas again and now I choose the option for Form Header/Footer. <center><a href="http://1.bp.blogspot.com/-qswSMDcJU94/U6G8gL2Fz9I/AAAAAAAANO4/mwYEkRRoKjE/s1600/20140618l.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-qswSMDcJU94/U6G8gL2Fz9I/AAAAAAAANO4/mwYEkRRoKjE/s1600/20140618l.jpg" /></a></center> I'm actually only going to use the footer on this menu, so I can position the mouse cursor on the top of the Detail bar and click-and-drag it all the way up to the top of the Form Header. The heading bar for the Form Header will still be there in the Design View, but in the Form View it simply won't appear. Go and have a look if you like! Come back to the Design View when you're done. <center><a href="http://3.bp.blogspot.com/-cOtKRkDFtCw/U6G9UbZChBI/AAAAAAAANPA/LW2K0PeVtVE/s1600/20140618m.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-cOtKRkDFtCw/U6G9UbZChBI/AAAAAAAANPA/LW2K0PeVtVE/s1600/20140618m.jpg" /></a></center> I'm going to change the colour of the footer area now by right-clicking with the mouse cursor in the footer area and I'll change it to a dark blue. Use what colour you want, but remember it - if a database is to look professional it will use colours consistently, so you are choosing the colour of every single form here... It will also have light grey buttons on it so it should contrast nicely with those and not clash with the darker grey of the main canvas. We are doing well. There's only a couple of things left to do - create a button to close the database, and save the form. That's going to take as much instruction as you've already waded through... <center><a href="http://4.bp.blogspot.com/-m5wJ0wOO8OE/U6HAJ-7-y1I/AAAAAAAANPU/ivYHDl2DZC4/s1600/20140618o.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-m5wJ0wOO8OE/U6HAJ-7-y1I/AAAAAAAANPU/ivYHDl2DZC4/s1600/20140618o.jpg" /></a></center> Click the Design tab on the Menu Bar at the top of the screen and then click the option for a new Button (both highlighted with a pointing finger above). Having clicked to create a button the next click will become the top-left corner of your button and then keeping the mouse button down you will click-and-drag to the bottom-right of where you want your button to be. Remember to start this in the footer element of the canvas. A Wizard window will open, as shown, as you finish click-and-dragging to size your button. You are allowed to shudder before clicking its Cancel button! Notice that by default the button you created will have the snazzy caption "Command1". If you look in the Property Sheet, this title also appears in the text box at the top of the Property Sheet, so the properties now being displayed are those of the button, not the entire form... <center><a href="http://2.bp.blogspot.com/-zoCkQ-lV9eg/U6HDCQpLH7I/AAAAAAAANPg/oUWlVPOM-Oc/s1600/20140618p.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-zoCkQ-lV9eg/U6HDCQpLH7I/AAAAAAAANPg/oUWlVPOM-Oc/s1600/20140618p.jpg" /></a></center> If you have inadvertently clicked elsewhere and the Property Sheet is not showing the button's properties, just click back on the button before doing anything else. (See the pointy finger) Amend the Caption to read "Quit Database". There's actually two ways to do this. You can type in the cell next to the word "Caption" in the Property Sheet, or you can simply single-click over the text on the button until it lets you type directly onto the button. Your choice, they both do the same job. Back in the Property Sheet I've changed the Font Weight to Bold and the Fore Color to bright red by typing a hash (#) followed by the Hexadecimal value "FF0000". We'll use a few of these colour codes in later articles. I'll do an article to explain them in the next couple of days and then link to it here. One other thing to do now. The "name" of our button as far as Access is concerned is still the less-than-meaningful "Command1". It will be easier when we have lots of buttons on this menu to be able to see what they are instead of remembering what Command4 and Command5 do. <center><a href="http://1.bp.blogspot.com/-8FGa7t6D2dk/U6HI212f9DI/AAAAAAAANP4/TmpwuXIAQ50/s1600/20140618q.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-8FGa7t6D2dk/U6HI212f9DI/AAAAAAAANP4/TmpwuXIAQ50/s1600/20140618q.jpg" /></a></center> We've not touched the row of options in the Property Sheet as yet as the Format tab has done all we needed so far. Now though we need to click the Other tab which will change the list of properties below the tab row. In the top row - against the heading "Name" we can now type a meaningful name: "btn_quit". All of our buttons will have the prefix "btn" and an underscore. Our button now looks the biz (go and check the Form View if you like) but clicking it will do absolutely nothing just yet. <center><a href="http://4.bp.blogspot.com/-F5peX632LH4/U6HIpRgPIPI/AAAAAAAANPw/UOBRgCaQxfA/s1600/20140618r.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-F5peX632LH4/U6HIpRgPIPI/AAAAAAAANPw/UOBRgCaQxfA/s1600/20140618r.jpg" /></a></center> We now need to build an event for the button. This will be a Visual Basic procedure that instructs Access what to do when the button is clicked. To do this we need to select the Property Sheet tab for Event and then drop down the On Click property and choose the [Event Procedure] option. Now click the button with the three dots (...) next to the drop down box. A new window will open for Visual Basic. <center><a href="http://4.bp.blogspot.com/-f3LlF9ZgTeY/U6HK88eeWaI/AAAAAAAANQE/-mj9dj4Gdr0/s1600/20140618s.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-f3LlF9ZgTeY/U6HK88eeWaI/AAAAAAAANQE/-mj9dj4Gdr0/s1600/20140618s.jpg" /></a></center> Now this could get complicated, but I'm going to ask you to take me at face value for now and I'll keep it as simple as I can. Visual Basic is a programming language. It will do much more than manipulate things in Access. It operates as a collection of routines, each one labelled a Sub (sub-routine). Each sub has a name. The name in Access is the name of the control (in this case a button) and the action. In this case the action we have specified is a mouse click. We could specify a double-click, a right-click, the mouse cursor rolling over the button.... and so on. Each line of the sub is read and actioned in turn and each sub-routine ends when it reaches a line that says "End Sub". <center><a href="http://4.bp.blogspot.com/-r3YW-AbuNgI/U6HMq9w5NcI/AAAAAAAANQQ/zBdFDOV6ptY/s1600/20140618t.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-r3YW-AbuNgI/U6HMq9w5NcI/AAAAAAAANQQ/zBdFDOV6ptY/s1600/20140618t.jpg" /></a></center> A closer look at the same screen. The sub is named "btn_quit_click()". The vertical bar is actually the mouse cursor which takes this form in Visual Basic. The beginning and end of the sub are created but no actual instructions have been entered because Access has no idea what we want the button to do yet. <center><a href="http://4.bp.blogspot.com/-zf5mExIVTBs/U6HNZMckw5I/AAAAAAAANQY/OlGACi7cOvk/s1600/20140618u.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-zf5mExIVTBs/U6HNZMckw5I/AAAAAAAANQY/OlGACi7cOvk/s1600/20140618u.jpg" /></a></center> We only need a single command in this sub which tells Access to close down the database and itself. The syntax "DoCmd" tells Access that a command is coming. It can be typed "docmd" and the fact that it will change automatically to the version with capital or upper case characters tells you that you have typed a command that Visual Basic recognises. As you type the following dot (full stop) a list of possible commands will open as a drop down list. You can either scroll through these and bore yourself, or just type the command "quit". It's at the point where you move the cursor onto another line, either with a mouse click, carriage return or arrow key, that the text will capitalise if the command is recognised. The indentation of the command text was intentional. It helps quickly identify where the start and end of a routine is and also I'll use indentation when we come to use If...then statements. It helps no end when you come up against a sub not doing what you expected and you have to figure out why! Before anything else we now need to save the Visual Basic routine. Click the Save icon as shown above. This will require the form itself to be saved also and a window will open asking for a name. <center><a href="http://2.bp.blogspot.com/-BpaGp6nnqi8/U6HPr3d00yI/AAAAAAAANQk/OZqu9V-Yl6U/s1600/20140618v.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-BpaGp6nnqi8/U6HPr3d00yI/AAAAAAAANQk/OZqu9V-Yl6U/s1600/20140618v.jpg" /></a></center> The default name of Music Menu will be taken from the Caption property that we changed way back. But if you remember the previous lesson where we named all our tables "t_" followed by the name, you'll not be surprised that we are going to call the form "f_music_menu". Amend the text and click Ok. <center><a href="http://1.bp.blogspot.com/-gx-zlludeOw/U6HRKgoPaGI/AAAAAAAANQw/jly6OSKTEV4/s1600/20140618x.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-gx-zlludeOw/U6HRKgoPaGI/AAAAAAAANQw/jly6OSKTEV4/s1600/20140618x.jpg" /></a></center> And that is it for this time! Click the Form View icon to have a last lingering look at the new form in all its glory. <center><a href="http://3.bp.blogspot.com/-LwcuLqSwT1o/U6HRakCag_I/AAAAAAAANQ4/7tX2WYOU_NI/s1600/20140618y.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-LwcuLqSwT1o/U6HRakCag_I/AAAAAAAANQ4/7tX2WYOU_NI/s1600/20140618y.jpg" /></a></center> It should look like this. The buttons for opening data entry forms and reports will go in the dark grey area and the button to close everything down should work. Go on... give it a click! If everything disappears, the form has worked! Try it again by reopening the database and you can run the form by double-clicking the form name in the list of database objects. <center><a href="http://4.bp.blogspot.com/-1eOkjk72WV4/U6HS_rTOInI/AAAAAAAANRE/P8_zkgMrlf4/s1600/20140618z.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-1eOkjk72WV4/U6HS_rTOInI/AAAAAAAANRE/P8_zkgMrlf4/s1600/20140618z.jpg" /></a></center> I hope this was fairly easy to follow. Let me know! This was the easy form - once we start on the more complicated forms there will be different ways to present data fields for direct entry, drop down lists of allowed data content and look-ups to other tables to verify data. For each form there will be a button on this menu form that will open it and at the same time turn the menu invisible. An exit button on each data form will close that form and turn the menu back visible. It's going to take a few weeks/months to do everything, but I hope you stay with me! By the end you will be able to use the techniques to create any other sort of database. Just be aware that if you intend to store personal data about people you may need to be aware of the Data Protection Act if in the UK or any similar legislation elsewhere.

Geeks Computer Tech

MS Access Vinyl Database 3 - Creating a Menu Form

Popular

Blog Labels

Blog Archive

Inspirasi

Inspiratif

Join This Site

Tips