Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 
Courses - Microsoft Access 103
Description: Using Microsoft Access 2
Running Time: 84 minutes
Pre-Requisites: Access 102 very strongly recommended
Previous Lesson: Access 102
Next Lesson: Access 104
Main Topics: Main Menu Form, Continous Forms, Form Header, Footer, Combo Box
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 8/25/2014 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 103
Using Microsoft Access

Build a Main Menu, command buttons, form properties, customer list form, continuous forms, track lead sources. 84 Minutes.
 

AC103 Major Topics

  • Create a Main Menu Form
  • Continuous Forms
  • Form Header & Footer
  • Combo Box With Data

In this class, we'll pick up where we left off in Access 102. First, we'll start by creating a Main Menu form to provide our users with a clean and easy interface for navigating through the different forms in our database. We'll use command buttons to open up other forms. We'll learn how to adjust various form properties such as captions, navigation buttons, record selectors, scroll bars, and such.

 

We'll learn how to make this Main Menu open up automatically when our database is opened. We'll see how we can hide the main database window from our users - so they can't have direct and easy access to our tables and queries. We'll also learn how to make a shortcut to our database on the desktop.

 

Next, we'll build a Customer List Form. On this form, we'll have a sorted list of customers. Our user will be able to click on one of the customers in the list, and click on a command button to open up that specific customer's record. This makes navigating through your customer records much easier, and it will teach us how to create command buttons to open forms and display a specific set of records. In creating this form, we'll learn about single v. continuous forms, the form header/footer, and more form properties.

 

Although we don't spend a whole lot of time with them, we'll also learn how some of the Autoforms work, and we'll work briefly with the Form Wizard. Of course, we prefer to teach you how to build forms manually, but the wizards can sometimes come in handy if you're in a pinch and need a quick form built.

 

Finally, we'll track lead sources for our customers. A lead source is essentially where the customer found out about us (radio, TV, etc.) We'll create a separate table for our list of lead sources, and we'll see how we can create a combo box on the customer form to select a lead source - and how to dynamically change this list using a lead source form.

 

We'll also look briefly at another one of the form wizards to construct a quick form for our lead sources - so that our users can add and remove options from the list.

 

We'll update our Main Menu with the new buttons, and additional features.

 

In this lesson our database starts to become more of a real working database. We're starting to add features that make it real user-friendly - such as the Main Menu. You'll start to see this database evolve into something you could actually use in a real production (work) environment.

 

 

Access 103 Outline
 
l. Introduction
Objectives
Pre-requisites
Versions used
How to learn
Included files

2. Main Menu, Part 1
Creating a form with no data
Unbound form instead of a Switchboard
Command button to open a form and show all records
Form properties
Form caption
Record selectors
Navigation buttons
Scroll bars

3. Main Menu, Part 2
Form background color
Label control
Label properties (font, size, bold, effects, etc.)
Resizing a label
Putting an image on a command button
Button properties
Send to back / bring to front
Format painter
Setting a database startup form
Application title
Hiding the database window
Unhiding your database window
Creating a shortcut to your database on your desktop

4. Customer List Form
Query: sorted list of customers
Building a form based on a query
Lining fields up horizontally
Continuous forms v. single form
Optimizing form space
Form header & footer
Using the rulerbars to select across/down
Cleaning up your labels
Command button to open a form and show specific records
Opening the selected customer record
Filtered results in your form
Removing a filter
Autoform: Tabular
Autoform: Columnar

5. Tracking Lead Sources
Adding a lead source field to the customer table
Combo box with static list of lead sources
Making at table to store a dynamic list of lead sources
Creating a combo box based on a table
Adding new valves to your table
Updating our Main Menu with the new buttons
Using the Form Wizard to create a quick form
Create a form for lead sources using the wizard
Renaming your form
Create a button to close the database

6. Review
Review topics
Skills check
What's next?
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 103

Richard on 1/1/2007:  Build a Main Menu, command buttons, form properties, customer list form, continuous forms, track lead sources.
Wiley Redding on 7/3/2008: Access 103 showed a safe way for other users to look for information without hurting your tables. Great idea, well planned instructional video.
Maks Kelemina on 8/10/2008: There is a light at the end of the tunnel
Aundrae Murdock on 9/7/2008: HiRichard, In Access 103, TIME INDEX 20:53, on the CustomerF:Form, is their a easy method of just typing the last name or first name of the customer and their record information would display,instead of having to use the record selector to scroll through until you find the name.
Richard Rost on 9/8/2008: Aundrae, you can use the SEARCH button on the toolbar to quickly find a record, or you can apply a filter like I showed you in Access 102. Unfortunately developing a CUSTOM search button or any other method involves some programming. I do cover this, though in Access 307.
rj jenson on 9/11/2008: I want to give you a bit more information following my last comment regarding the button not displaying the correct record. I tried re-doing the whole database with the same result. I then tried redoing the tutorials again but starting in the database you had created. The button worked! I decided it must be a setting in access that you have set but not informed us of on your video. Or perhaps your version of access and access 2007 have different defaults. Either way I diagnosed the problem and when I create a button in access 2007 and look at the 'on click' properties it displays [embedded macro]. This button does not filter the CustomerF form correctly. When I view your button it displays [event procedure]. I tried opening this up which took me to Visual Basic. I had no idea what I was doing but I copied the code and changed my button to [event procedure] then pasted the code into visual basic, changed the names to correspond with my names and hey presto it worked! However, I don't want to have to repeat this process each time so can you please offer me any help on this?

Many thanks

Richard Rost on 9/11/2008: RJ, that's certainly messed up! I don't really like Access 2007 much. You're right - those "embedded macros" are a pain in the behind. I believe you can go into Setup and change it so that Access doesn't use them - and just uses Event Procedures (VBA).
rj jenson on 9/11/2008: Hi Richard. I tried looking for the setting you mentioned but it was nowhere to be found. I considered downgrading my version of Access so that I could follow your excellent tutorials without similar problems arising in the future. Then I had a brain wave- I saved my database as an access 2002-2003 database and voila - no more automatic enbedded macros and my database instantly has the default settings as your own version which makes it infinitely easier for me to follow along with your tutorials: it might be worth suggesting this to other newbies using Access 2007 as it took be hours to figure that one problem out!
Richard Rost on 9/12/2008: I'll be installing Access 2007 on my training machine (again) this weekend. I'll poke around and let you know what I find out. If I don't post something here by Monday, REMIND ME. I've got a million things on my plate right now. :)
Bob B on 11/22/2008: Richard
I am using Access 2007 and am in your 103 lesson, builing a customer list. When I insert the command button to display specific records,and when I highlight a record, it keeps bringing me to the start of the CustomerT and not to the customer record which I want to open. Any suggestions?

Derek Mitchell on 2/12/2009: I have Access 2003 and when using the form design window I find that I have only gridlines and no grid dots. I prefer the grid dots as it's better and more helpful in lining up objects, etc. Can I turn this option on?
Richard Rost on 2/12/2009: Check your GRID settings in the form properties. If you have it set too high (or 0 or blank), the dots will actually disappear. Set them to something like 8 or 10 and you should see them.
ian dudley on 4/13/2009: Hi Richard, why did you choose not to assign a primary key to each record in the lead source table?
Richard Rost on 4/14/2009: Ian, good question. It's not ALWAYS necessary to include an ID for every table, especially since I wasn't planning on making this table RELATIONAL to other tables. If it's just a simple little table that you're going to use to feed a combo box, you don't really need an ID. If, for example, you want to track shipping methods and you just need "US Mail, FedEx, and UPS," then you might not want to bother. It was just a matter of personal preference - for a quick little table that I wasn't planning on using for anything else, or even keeping around very long (as you'll see in future lessons). Good question, though.
Daryl Armstrong on 4/25/2009: Hello Richard as I was performing the class steps on making a Dynamic combo box, I kept getting an popup warning each time I entered a leadsource. ("Access Database can't find the macre 'Customers.' The macro(or its macro group) doesn't exist,or the macro is new but hasn't been saved......)
Any Help? Access 103 Time Index 9:58

Richard Rost on 4/27/2009: Daryl, Access 103 Lesson 4 has nothing to do with macros. I can't understand why you'd get that error message there. I'm guessing perhaps you have a value like 'Customers' in one of the Event fields by accident.
Edward Coult on 5/26/2009: Hi Richard thanks for the great tutorials on Access, but i am a little stuck on one topic.
What i am trying to do is make a database to record my Book collection.
I have made a Book table with the author and book title and various other data. But because i want to be able to pick the publisher and book topic from a list i have used the combo box to pick from a table i have made with the publisher name. Following your tutorial in Access 103 part 5 the lead source i can make a combo box and select the data from the publisher table and it displays on my main form ok with the publisher name, i have also made a field on the main book table to store this data in, but i only get the id number stored not the name book field and when i try and print the report all i get is a number how do i get the publisher name to print please.

Richard Rost on 5/26/2009: Edward, this is covered in Access 201 and 202. You have to relate the two tables together with a query. Right now, in Access 103, we're only dealing with using a single table at a time.
Judith Koester on 7/15/2009: I had the menu form working great, i.e. "Open Customer Form", but my boss wanted me to remove Foreman from the Customer Form, which I did. I also removed it from the CustomerT. Now when I click on Open Customer Form, an Enter Parameter box opens up and asks "ProjectT: Foreman". I have to strike enter to continue. Since I removed Foreman from the Form and Table, how can I get rid of this?
Richard Rost on 7/16/2009: It looks like this form might be based on a query that's still looking for the Foreman field. It's asking you for the PROJECT table, however, which is why it looks like a query. Did you perhaps JOIN together Customers and Projects in a query and then use that query to feed your form? Check the RecordSource property for the form.
Judith Koester on 7/16/2009: Thank you. I found the problem in a query. Looks like everything is OK.
Joe Nellen on 7/29/2009: Hello Richard,
I love these classes!! I am in a bind and need some quick direction.
I created a nice DB for a new customer after taking several of your Access classes. I created a Data Entry Form that has a Combo Box that allows the user to select an Employee from a list (from a Query) that displays LastName, FirstName, Soc#. When they select the correct employee from the list it only displays the LastName in the box. Now they decided that they want it to display the LastName, FirstName & Soc# just like it is displayed in the drop down list. I cannot figure it out and am wondering where I could find this in your Access Classes.
Thanks for any direction you can give.
Joe Nellen

Richard Rost on 7/29/2009: Showing all of those fields when the combo box is OPEN is easy to do. Showing them when it's CLOSED (after something has been selected) is a little tricky. You need to join all of those fields together in a query first, then use that query field in your combo box. It's basic string concatenation that I cover in Access 104, lesson 3. In this lesson, for example, I merge FirstName and LastName into one visible field.
Joe Nellen on 7/29/2009: Thanks for your quick response Richard. Actually, in looking back at notes I took... I realized that concatenation was the way to go. I fixed the problem and everyone is happy. A testimonial to the thoroughness of your classes.
Jonathon Mun on 8/27/2009: Hi Richard,
Is it possible to move a group of labels boxes independently from the text boxes? I have not found any documetation stating that this is possible.

Richard Rost on 8/27/2009: Jonathon, you can move labels independent of their text boxes by clicking on the upper-left corner box, but that's only good for one label at a time. The only way to move a GROUP of them is to CUT them out and then PASTE them somewhere else. This, however, breaks the link to the text box. That's not really a big deal, however.
Maks Kelemina on 10/4/2009: Richard
I got problems with the command buttons.It appears that none of the tool box icons work eventhough they are enabeled.The button command wizard does not open when it is dropped on disign form.
Maks

Walter Pohle on 10/4/2009: Is it possible to have a buuton open a blank Customer Form. I know you
can add new form at the bottom by using the (*)

Richard Rost on 10/4/2009: If your wizards are enabled and nothing happens when you drop a control on your form, you could be out of memory. Try rebooting your computer. If that doesn't work, you might need to reinstall Microsoft Office fresh. Something else might have damaged your install.
Richard Rost on 10/4/2009: Walter, yes it's possible, but it will involve a little VBA. It's certainly beyond the scope of Access 103.

You need to edit the code in the button that that the wizard creates to use the acFormAdd parameter for the DataMode. OR you can issue a Docmd.GotoRecord command to go to a new record after the form is opened.

Again, this is a bit more advanced. I cover stuff like this in my Access 300-series classes.

 Mark on 11/19/2009: How do I set the print button to only print the current record?
Richard Rost on 11/19/2009: Mark, this is not easy to do. Unfortunately, Microsoft didn't make "open a report and show specific data" part of the command-button wizard. They should have. Fortunately, I do cover how to do this in one of my Tips Tutorials: Open a Report and Find Specific Data.
Mark Clark on 2/22/2010: In the video "Track Lead Sources" at about 2 minutes and 36 seconds when choosing a field to store a value in I am finding that the LeadSopurce field is not in the list. The LeadSource field was added to the customer table and saved but it does not show up in the list. If I make a new form based on the customer table then the Lead Source field shows up and works OK. What did I miss, any ideas why it does not work as your example does? I also can't get it to work on a Windows 7 laptop with access 2007. I am doing the work along in Access 2003.

Reply from Richard Rost:

Mark, try recreating the Lead Source combo box using the wizard from scratch. Also, I have NOT tested these lessons with Access 2007 yet, so I can't say for sure what the problem is, but combo boxes like this should work the same.

 William Easton on 6/2/2010: Richard - Thanks for all your work. We really appreciate it out here. Im working in ACCCESS 2007. This is the first "glitch" I have been unable to figure out. I placed the command button for closing the database on MainMenuF (at the end of Access 103 Lesson 4). When I clicked the button I got the error "You have entered an expression that has an invalid reference to the property Dirty." I found the following 2 lines of code in the click() Subroutine for the button:
If Me.Dirty Then Me.Dirty = False
DoCmd.Quit

When I deleted the first line, the button worked properly and closed the DB. I went through the procedure 2 more times with the same results. Am I doing somethging wrong or is this a glitch in 2007 or what? Thanks.


Reply from Richard Rost:

William, I've never encountered that problem before. The wizard should not have placed that code in your button. Try deleting the button and create it again. Let me know how it works.

 Sandy on 6/28/2010: I understand the concept of creating a continuous form to view records and adding a button to view a single record in the main form. While it's a practical solution in many cases I need a better solution for large numbers of records. I'd like to add a lookup box to my main form that once an ID is selected from the dropdown, all fields are updated to show the selected record. Is there a lesson that shows this type of combo box?

Reply from Richard Rost:

You could very easily just make a list box or combo box that changes the current record in the form instead of opening up a separate form. This would require some kind of AfterUpdate event. It's a little more advanced than Access 103. There is also the "find a record" option in the combo box wizard. This turns your combo box into a SEARCH box.

 lewis ray on 9/24/2010: Hi Richard, i am having serious problems with the buttons, when i have followed the procedure to get the button on the form and i am ready to test it, when i click on it it keeps coming up with an error box, say... "the expression on click you entered as the event property setting produced the following error: object of class does not support the set of events."

then, underneath that it says... "*the expression may not result in the name of a macro, the name of a user-designed function, or [event procedure]
*there may have been an error in evaluating the function, event or macro."
Please can you help me as i am stuck

Linda Rider on 10/8/2010: In access 2007, the command button on a form used to open another form and select specific data only opens the second form, displays all of the data, beginning with the first record, but indicates that it's filtered. How do I change the embedded macro code to make it go to the correct data record?
 T Parker on 10/12/2010: I'm using Access 2007. In Access 103 (time marker 19:47)the Open Selected Customer button you created to open the CustomerF and display the selected customer is not working in the 2007 version. My button does open the CustomerF form, but displays all records. How can this be displayed in 2007 version. I see others have asked this question, but I don't see your response. Thanks!
Fernando Rodriguez on 10/14/2010: I'm using 2010 and when i do the continous form the footer will not stay the size that i give it it always gets bigger and so dose the right side now if i change it to single no problem why dose the form not stay the size i set it to
 Lynn Robbibns on 10/31/2010: I have tried making contact list form in Access 103 3. customer list.I am using Access 2010 and can not do the form based on query. Only wants to use tables. If I use the wizard, it works but makes the Form for me.
Help!!!

 Lynn Robbibns on 11/2/2010: Hello,
Can we do all of the Access lessons with Access 2010 without problems following your courses using the earlier version of Access. Are There many problems or differences?

Reply from Richard Rost:

Yes and no. I've heard from some people who were able to get by with most of my courses uses Access 2007 and 2010. Others have been having problems. I haven't had the time to go through ALL of them to figure out exactly where there are issues, but I'm going to be releasing a new line of Access courses specifically for Access 2010 very soon... so hang in there.

If any of you are working through my existing tutorials with Access 2010, PLEASE post a note in the Student Forums if you encounter any problems.

 Emad on 11/9/2010: Can we use Hyperlink instead of button for each records? if it yes How?
I saw it in Northwind 2007

Reply from Richard Rost:

You want to use a hyperlink to move between records on a form instead of the command buttons? You COULD, but I prefer just using regular labels (not hyperlinks). You can attach an OnClick event to them. Keep in mind, however, that this involves programming and is WAY beyond the scope of Access 103. Talk to me after you've completed Access 304.

Robert Roach on 1/12/2011: Richard, I am using Access 2010. How do I setup the Main Menu Form to open when I open the database?

Reply from Richard Rost:

File > Options > Current Database > Display Form > Pick your form from the drop-down menu.

While you're in there, also check "Overlapping Windows." I hate the Tabbed Documents look. :)

 eddy geijselaers on 5/4/2011: Richard,

I am (still) working with Access 2002/2000.
Working on my WineCellar Database, I am having a problem with reports based on a query.
Basicly it’s a report of all the wines I have tasted. I managed too sort the data the way I want using the sorting order in the report. There are a few minor items, but for now I ignore the fact that the form shows on the first page only the header.
I discovered that I had to have the ID field in the query as well as the name field to show data from a related table.
Question:
Now I am missing some wines in the report that are in fact in the table. Problem not all the fields of those wines are filled. Champaign has not always a year on the bottle so that I have to leave out. But now those and the wines with incomplete date don’t show up in the report.
I looked again into classes 101 and 102 and saw that the Customers with a missing State still are on the report.
How to handle this problem?
I made a query just for the Champaign’s without the year field, but that is not what I want for there are Champaign’s with a year and it is good to know which one.

Thanks
eddy


Reply from Richard Rost:

You mention showing records from a related table, but we haven't gotten to that yet in Access 103. I don't cover relationships and multiple tables in a query until Access 201. So if you're missing records from one or more of your tables, it could be because you're using an INNER JOIN instead of an OUTER JOIN.

For example, if you have CustomerT and ContactT and you join them together on CustomerID with an INNER JOIN (the simplest type) then you won't see any customers who have zero contacts - and vice versa. You need to use an OUTER join for this, which I cover in Access 220.

Gurjeet Kaur on 6/7/2011: Hi Richard
i am not able to find Startup feature in access 2007.

Gurjeet Kaur on 6/7/2011: hi Richard
I was able to set up the startup feature but couldn't uncheck Display Database Window. Because I couldn't find it.

Gurjeet Kaur on 8/8/2011: I'm using Access 2007. In Access 103 the Open Selected Customer button you created to open the CustomerF and display the selected customer is not working in the 2007 version. My button does open the CustomerF form, but displays all records. How can this be displayed in 2007 version. Thanks
 Lisa Seibert on 9/6/2011: Richard, I am using 2007 for lesson 102 & 103. I followed the lessons to create the Customer Form with the "Close Button" as well as the "Open Customer Form" and the buttons do not do anything. I can place them on the form but they are not followed with any action except an occasional error message for the Customer Form. Thank you.
Lisa Seibert on 9/6/2011: I am trying to redo the "CustomersSortedQ" query because when I run it the results display all the fields from the Customer Table instead of only the fields I selected for the query (ID,LastName, FirstName, CompanyName, Phone). I redid the this several times and can not get it to look like yours...Help.
 Lisa Seibert on 9/6/2011: Richard, (using 2007) I also can not get the Field List to display the Query Feilds, Only the Table Fields...any tips?
jose javier medina on 11/9/2011: I'M USEING 2007 ACCESS, I CAN'T FIND MY START UP SET UP FOR THE MAIN MENU

Reply from Richard Rost:

It's in the Access Options. Office Button > Access Options > Current Database > Display Form

 Avi on 11/22/2011: I'm using access 2010 and when I added the "Open Selected Customer" button and tried to save the form I received an error message that said that it could not be saved, I had to remove any layouts that have empty cells in them. What does this mean, and what should I do?
Cheryl Hokanson on 2/21/2012: I don't see you using the Switchboard menu type
form. Is there a reason for this? Also, does 2010 us Switchboards?
Thanks, Cheryl

Michelle on 2/25/2012: Richard, I'm using Access 2007 and I'm having trouble with the buttons on the MainMenuF. I've tried this several times and have the same problem every time. I creat the form with the Lables and Buttons and everything works fine until I close the database and reopen it. After reopening the DB, the buttons no longer open the forms. HELP!!! Please! Thanks. :-)
Michelle on 2/25/2012: Richard, (using 2007) I also have another issue. When I create the CustomerListF from the CustomerSortedQ, the form is not sorted. It only shows the fields from the query, but is is listed in ID number order instead of alphabetical order by last name.
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP