record collection

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.

11.06

MS Access Vinyl Database 2 - Creating Tables

Geeks Computer Tech - 11.18

This series will take a little time to build as I decided I should start from scratch for the blog. My advantage is I can copy from what I've already done and import data, but starting from scratch will allow me to avoid some of the inconsistencies that exist in my current database. In this article we will create the database for our music collection and then create the vital tables - the "storage jars" for the data about our music. <center><a href="http://3.bp.blogspot.com/-oKvohi4c0e8/U5slbeQ3U1I/AAAAAAAANKY/29_swANrz70/s1600/20140613a.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-oKvohi4c0e8/U5slbeQ3U1I/AAAAAAAANKY/29_swANrz70/s1600/20140613a.jpg" /></a></center> Microsoft Access is the database tool that comes with Microsoft Office. If you don't have this then this series of articles is going to be a touch difficult to follow... Open Access and then create a new database based on the Blank Database option as shown - simply double-click this icon. <center><a href="http://4.bp.blogspot.com/-_Z2X5-L3YZw/U5smFDzZ7dI/AAAAAAAANKg/aZHZcAjkMTU/s1600/20140613b.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-_Z2X5-L3YZw/U5smFDzZ7dI/AAAAAAAANKg/aZHZcAjkMTU/s1600/20140613b.jpg" /></a></center> Give your project a name - I've called mine "music" and I'm storing it in a sub-folder called "ACCESS". <center><a href="http://3.bp.blogspot.com/-Aq95HL2NAIg/U5smeerVjTI/AAAAAAAANKo/8i80fdZinfo/s1600/20140613c.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-Aq95HL2NAIg/U5smeerVjTI/AAAAAAAANKo/8i80fdZinfo/s1600/20140613c.jpg" /></a></center> The first thing to do is to create our first table. Click on Create and then on the Table icon to the left. The new table will open in Datasheet View. This looks a little bit like an Excel spreadsheet (Access can be thought of as basically a series of linked spreadsheets). This method of creating a table is a little dangerous and we are not going to enter any data yet until we have defined the fields of the table. If you had a table for cars, for instance, the fields would hold the make, model, colour etc. <center><a href="http://1.bp.blogspot.com/--ljzNZ8ghRM/U5soNjKC_hI/AAAAAAAANK8/ar0hR-BZ2yY/s1600/20140613d.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/--ljzNZ8ghRM/U5soNjKC_hI/AAAAAAAANK8/ar0hR-BZ2yY/s1600/20140613d.jpg" /></a></center> So straight away click the View icon as shown. This will be currently showing the icon for the Design view and this is the one we want. You could also click the little triangle (down arrowhead) below the word View and then click the option for Design View. <center><a href="http://3.bp.blogspot.com/-um4x7gSWOXA/U5spFtWuOkI/AAAAAAAANLE/--NLil8ayPI/s1600/20140613e.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-um4x7gSWOXA/U5spFtWuOkI/AAAAAAAANLE/--NLil8ayPI/s1600/20140613e.jpg" /></a></center> A small window will open asking you to give a name to the new table. We are going to create the main table first - the one that will hold details of all the music tracks you have in your collection. Probably the most important table that the database will contain. By the time we finish we will have tables, forms, queries and possibly reports. You might want to name them all along similar lines but that carries a risk of confusion. If we simply called this table "music" and then also called the form (the window for entering data into the table) "music" and then called a query the same name as well, then it's possible we may be faced with a list of database objects where there are three objects called "music" and we don't know which is which. This may sound a bit complicated at this stage and I'm sorry if it does, but to get round this problem I'm going to name every single table with a "t" and then an underscore before the more meaningful bit of the table name. So instead of "music" we'll name this table "t_music" and when we get to creating the forms and queries they will be "f_name" and "q_name" and then in any list we can tell which is the table and which is something else. Just take my word for it at this stage. You could be glad you did at some point in the future! By the way, I'm old-fashioned - I know you could have spaces in your table name instead of using underscores, but again there's a risk somewhere that you mix up a table name with the following word or cut one short leaving out a word, so using underscores is a safe approach. Ok, I've made you read all that when all you needed to do was type t_music and click the Ok button! Moving on... <center><a href="http://1.bp.blogspot.com/-_kHLwEgBMHg/U5s2ttZxjTI/AAAAAAAANLk/qprXPpkH1n4/s1600/20140613f.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-_kHLwEgBMHg/U5s2ttZxjTI/AAAAAAAANLk/qprXPpkH1n4/s1600/20140613f.jpg" /></a></center> A few things to say here. All of the text you see in the two columns labelled Field Name and Data Type needs to be either typed in or defaults into the right hand column as you add the field name and will need amending in some cases to match the diagram above. Every table will have a main ID field at the top and for this field (and for this field only) it needs to be designated the Primary Key. By default the top field will be named ID and will be the primary key. This means it will hold an automated number and each record will always have that number and if a record is ever deleted then that number will never be allocated to a new record. Think of it this way: in a table of four records if record number 3 gets deleted then what was record number 4 becomes the third record. But the primary key will not change either for the old record 3 or old record 4. If you listed the records in the order they were entered, the ID field would read 1,2,4. A safety feature. Very necessary as that field will be the link between tables. Rather than have to type "Parlophone" in the label field for every record released by that company, we will be able to store just the number that refers to the label Parlophone. It saves space. And if you have a large record collection of thousands of tracks it will also save you a lot of time. And no... you won't have to remember, or ever see, the numerical ID. Whilst all ID fields can be numerical, you may want at some time to specify your own codes for something. I've done that for the Media ID. The media field will hold details of whether the music is on a 7 inch 45 rpm vinyl, a music cassette, an album or is a digital file on your PC, laptop, iPad, phone or whatever. So against the music_media_id you can see that this is a text field, not a number. At the bottom of the window, where the default for a text field would be 255 characters, I have made it 2 characters. This will appear in "Field Size", the top row in the bottom part of the window. Last point to make here: I've labelled every single field with the prefix word "music" and an underscore. Other tables may have a similar fieldname - the Artist table will have a field called artist_ID and to avoid confusion we don't want there to be two tables having fields with the same name. So the prefix makes it music_artist_ID and it will save us heartache in the future. We've created the first table - click the Save icon in the top left corner of the screen next to the Windows icon. Not necessary, but again it's good practice: close the table by clicking the X on the right of the window just to the left of the words "Property Sheet". <center><a href="http://3.bp.blogspot.com/-drbcuAX26Zw/U5s3vJfkvaI/AAAAAAAANLs/xhTg6nBCk9w/s1600/20140613g.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-drbcuAX26Zw/U5s3vJfkvaI/AAAAAAAANLs/xhTg6nBCk9w/s1600/20140613g.jpg" /></a></center> I've covered a lot here, but to make it worthwhile for you, let's create some of the other tables now to finish off this article. We'll start with the media table. Once again go to Create > Table, then click the Design View icon and name the new table t_media. This is the table mentioned earlier where you need to change the primary key from AutoNumber to Text. Then change the field size from "255" to "2". Make sure that the Primary Key icon is lit up as shown and against "Indexed" in the left column of the lower window it should read "Yes (No Duplicates)" in the right hand column. Click the Save icon in the top left corner of the screen next to the Windows icon and close the table by clicking the X on the right of the window just to the left of the words "Property Sheet". <center><a href="http://4.bp.blogspot.com/-v1RJC3pUu6M/U5s6r0VPqoI/AAAAAAAANL4/pyVl7r4r3cQ/s1600/20140613h.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-v1RJC3pUu6M/U5s6r0VPqoI/AAAAAAAANL4/pyVl7r4r3cQ/s1600/20140613h.jpg" /></a></center> Now for a table to hold details of record labels. Again, only two fields are needed: the code and the name. Name this table "t_label". Make sure you save it once you have defined it. Refer to instructions above if you need help remembering how to create the table. <center><a href="http://3.bp.blogspot.com/-bTopsk-KVOY/U5s7c5PN72I/AAAAAAAANMA/wgpER0rbGog/s1600/20140613i.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-bTopsk-KVOY/U5s7c5PN72I/AAAAAAAANMA/wgpER0rbGog/s1600/20140613i.jpg" /></a></center> Last table for now. This is called "t_artist" and will hold details of artists, both individual singers and groups/bands. All the fields we need for now are the ones shown. A code, the name of the artist and the name as it would be used in an alphabetical sort. Just to give an example here. An artist with the name "Elvis Presley" would have the text "presley elvis" stored in the field artist_alpha. We will use this field to sort records so Elvis Presley appears correctly with other surnames beginning with P rather than just after Elvis Costello! In the same way "The Beatles" would simply have "beatles" in the artist_alpha field so that they appear under B in the alphabetical list. Click the Save icon in the top left corner of the screen next to the Windows icon and close the table by clicking the X on the right of the window just to the left of the words "Property Sheet". Ok, I know you must be dying to start entering data into these tables but there is a risk in just entering data straight into a table. It's easy to add an extra field by mistake or delete a table instead of deleting a single record (row). If you really must do this now, I suggest you do it only in the t_media table. <center><a href="http://3.bp.blogspot.com/-Pn8dqqe1vBo/U5s-WjiU4JI/AAAAAAAANMM/C8AWLElFcac/s1600/20140613j.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-Pn8dqqe1vBo/U5s-WjiU4JI/AAAAAAAANMM/C8AWLElFcac/s1600/20140613j.jpg" /></a></center> Open the table by double-clicking it in the list of tables on the left of the screen. Add whatever records you need to enter. Some will wonder what a 78 rpm record is (read my other <a href="http://bispham2.blogspot.co.uk/search/label/78">blog</a>...) Others may have a collection of even older wax cylinders. You may want to catalogue video files... In the next article we'll create the first form based on one of the simpler tables and also create a special form that will be used as a menu screen. This menu form will be used to open all the other forms and to select report output. We'll also build buttons onto it to close the database down so that when using the database you won't need to be an expert in Access. In fact you'll be able to let people use it who have no idea what Access is!

11.18

MS Access Vinyl Database

Geeks Computer Tech - 07.31

I know it's a while since I posted here, but thought I'd start a project that made for a few entries and having retired recently and started to catalogue my vinyl and (being honest) shellac collections, I thought there must be a few other people out there with large collections of music on black discs and trying to keep track of them. <center><a href="http://1.bp.blogspot.com/-WTc6svybyjU/U5hkem5hNlI/AAAAAAAANJQ/7xMkh4osabQ/s1600/20140611a.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-WTc6svybyjU/U5hkem5hNlI/AAAAAAAANJQ/7xMkh4osabQ/s1600/20140611a.jpg" /></a></center> There's no details in this post but the intention is to get you interested by showing what I intend to do. The main menu screen as yet does not have a great many items, but they do all currently work! This will log music on all formats - 78s, 7-inch, LP albums, CDs, tapes, 8-tracks and digital files. You can have multiple tracks on albums and you can have an album artist different to track artists. <center><a href="http://2.bp.blogspot.com/-AvBhfC2E_cw/U5hlMOGCleI/AAAAAAAANJY/dLKsb5TFozc/s1600/20140611b.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-AvBhfC2E_cw/U5hlMOGCleI/AAAAAAAANJY/dLKsb5TFozc/s1600/20140611b.jpg" /></a></center> The main data entry screen currently looks like this. Artists are entered in the top box and the bottom window will only show music tracks by the displayed artist. On this page you can associate tracks with an album. Track number (if appropriate), label, format, storage box reference and the disc's own catalogue number can be stored. <center><a href="http://3.bp.blogspot.com/-GLr4tpLvD_I/U5hl_uz7boI/AAAAAAAANJg/y6e3lT3rLWA/s1600/20140611c.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-GLr4tpLvD_I/U5hl_uz7boI/AAAAAAAANJg/y6e3lT3rLWA/s1600/20140611c.jpg" /></a></center> You can also store the file path to digital music tracks (for both A and B sides) so that these can be played directly from the database. Clicking the button when a path has been specified will open your default media player. If no file path has been specified the button has different text (see B Side button above) and clicking has no effect. <center><a href="http://2.bp.blogspot.com/-AP5GMaY59-E/U5hnGAepZBI/AAAAAAAANJo/zrdG4ORshtI/s1600/20140611d.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-AP5GMaY59-E/U5hnGAepZBI/AAAAAAAANJo/zrdG4ORshtI/s1600/20140611d.jpg" /></a></center> This is the report that lists a single storage box. It can be opened as an Access report... <center><a href="http://3.bp.blogspot.com/-4NdvI3ALvxw/U5hnQ8hnBrI/AAAAAAAANJw/1g11mo05ITs/s1600/20140611e.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-4NdvI3ALvxw/U5hnQ8hnBrI/AAAAAAAANJw/1g11mo05ITs/s1600/20140611e.jpg" /></a></center> ...or as an Excel spreadsheet, in which case it will open Excel automatically. Note that this is a work in progress. I intend to create a separate Artist input screen to allow more details of the artist and also a button that will open an image of the record label/artist/record sleeve if you have scanned these. No macros are used in this. Instead we'll be looking at using a bit of Visual Basic to do the main work behind the scenes. Excited? Good! Watch out for the first instalment soon...

07.31

Geeks Computer Tech

MS Access Vinyl Database 3 - Creating a Menu Form

MS Access Vinyl Database 2 - Creating Tables

MS Access Vinyl Database

Popular

Blog Labels

Blog Archive

Inspirasi

Inspiratif

Join This Site

Tips