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 102
Description: Using Microsoft Access
Running Time: 129 minutes
Pre-Requisites: Access 101 very strongly recommended
Previous Lesson: Access 101
Next Lesson: Access 103
Main Topics: Field Properties, Search, Sort, Filter, Parameter Queries, Command Buttons, Combo Boxes, Reports, Compact, Repair
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 10/24/2014 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 102
Using Microsoft Access

Field properties, searching, sorting, filtering, more query tricks, parameters, combo boxes, reports, compact, repair. 129 Minutes.
 

AC102 Major Topics

  • Table Field Properties
  • Indexing Tables
  • Search, Sort, Filter Data
  • Parameter Queries
  • Combo Boxes
  • Command Buttons

We'll begin by briefly reviewing the database we built in Access 101. Then, we'll start off this course by going over all of the field properties in your tables (formats, input masks, validation rules, etc.), and learn what indexing does and how it can improve your database's performance.

 

We will learn how to create custom date and time formats for our fields, as well as different formats for Yes/No and text values.

 

You will learn how to use Input Masks control the way in which data is entered into your tables.

 

You will also learn about the Required property - how to make certain values in your table required, so the user has to enter something. You'll also learn about the Default Value property, so you can start a field with a particular value - like starting the State field off with "NY." We'll also teach you about Validation Rules to verify that data is good, and how to pop up warnings if the user enters in an incorrect value.

 

We'll learn about searching, sorting, and filtering in both tables and forms. We'll start out learning how to search through our tables for values with the Find button.

 

You'll learn how to Sort your records in ascending or descending order. We will also see how to Filter results in both tables and forms.

 

Next we will examine the use of multiple criteria in queries (e.g. show me all of the customers from NY or CA who are Active).

 

We'll also learn how to use parameters in our queries, which allow users to type in criteria at runtime (so your boss can run the query and just type in whatever state she wants).

 

We'll see how to use wildcard characters in queries, using the LIKE keyword.

 

Next we'll learn how to do much more with forms. We'll create an Employee table and form. We'll see how to add a picture to your employee's records (stored in the table and displayed on the form). We'll show you how to manipulate the properties for the picture - to set it to Zoom, Stretch or Clip in the window.

 

We'll work with a basic combo box and list box, to select a value from a list of options.

 

You will see how to manipulate the Tab Order on your forms so you can control what happens when the user hits the TAB key to move between fields.

 

We'll also see how to create a basic command button to close the form and return to the main database window.

 

We'll learn how to do much more with reports in this course. We'll make a set of mailing labels, but exclude customers who are missing address information (street or ZIP code, for example).

 

We'll also make a report showing which customers are missing data, so we can print out the list to call them.

 

At the end of this lesson, we'll teach you how to compact your database to keep it running fast. We'll also show you how to repair your database in the event that it becomes corrupted.

 

Again, this class picks up where Access 101 left off. If you're serious about learning how to develop databases using Access, don't miss this course. It's an excellent stepping stone to the more advanced courses.

 

Access 102 Outline
 
1. Welcome
Objectives
Pre-requisites
Versions used
How to learn

2. Review
How to find sample database files
Review topics from Access 101

3. Field Properties
Text field sizes
Number field sizes
Format property
Date formats
Text formats
Number formats
Input masks
Required property
Default value
Validation rule
Validation text
Indexing

4. Search Sort Filter
Find & Replace
Sorting
Filtering data
Both tables and forms

5. Queries
Multiple OR criteria (NY or CA)
AND from different fields (NY and Active)
AND across, OR down
Saving a query with a different name
OR from different fields (NY or 200 credit)
Between keyword
Date criteria (>#1/1/1998#)
Wildcards
LIKE keyword
Parameter Query

6. Forms
Creating an Employee table
Using OLE Objects
Creating the employee form
Adding a picture field to your form
Resizing your form
Intro to object properties
picture size mode: stretch, zoom, clip
The toolbox
Combo box with static data (M/F)
Using the combo box wizard.
Change to: Combo box into a List Box
Selecting and copying a record
Tab Order
Form fields: align left
Zoom with shift-F2
Command button: Close Form

7. Reports
Query for people not missing address data
Is Not Null: AND condition
People who are missing address data
Is Null: OR condition
New Mailing Labels
Building a report with design view
Building a report based on a query
Page header & footer

8. Compact & Repair
Why compact your database?
How to compact & repair

9. Review
Review topics
Skills check
What's Next?
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 102

Richard on 1/1/2007:  Field properties, searching, sorting, filtering, more query tricks, parameters, combo boxes, reports, compact, repair. 129 Minutes.
Richard Rost on 1/1/2001: ATTENTION STUDENTS: The sample database files are no longer installed on your hard drive like it says in the video. Due to size restrictions I've removed them from the installation package and put them on the web site. You can download them from the STUDENT DATABASES PAGE.
candy on 10/13/2007: i find access 101 very useful for me as a starter of using access.. i wanted to learn about the rest of the access tutorial for free but i can't afford to buy the cd. i hope that you'll provide free tutorials for the rest of the course. thank you.. and more power
Richard Rost on 10/13/2007: Candy, I understand your financial concerns. I've tried my best to keep my tutorials as inexpensive as possible (where else can you find an hour of Access training for six bucks?) I do try to provide a lot of free lessons - if you look through the Theater, you'll see that each course has a couple of free lessons on it. Plus, I try to post as many free Tips as I can every week. Obviously, I'm running a business, so I can't give everything away for free - but I've tried to make my courses as affordable as I can (some say they're priced TOO low).
John  Casey on 12/2/2007: I can't get the contents of a list box to sort alpha. When I click on a name in the list box, I get a form with that person’s name which is what I want. The query is sorted ascending but the contents of the list box are sorted by auto number. Can you help?
Richard Rost on 12/9/2007: Yep. We get to this in Access 202 Lesson 3 - sorting the contents of a combo box or list box. A quick solution: design a query that sorts the way you want, and then use that query as the data source for your list box.
Judith Koester on 12/12/2007: When I click on Combo Box and click on design view, I don't get the wizard, I get the label and box.
Judith Koester on 12/12/2007: On the Reports, Lesson 7, you titled the Report CustomersWithFullAddressQ.
Was there a reason it didn't end with an R?
Love these classes. Judith

Richard Rost on 1/5/2008: Make sure you have the WIZARDS set to ON on your toolbox. This is the button that looks like a magic wand. If it's not pushed down, none of the wizards will start.
Richard Rost on 1/5/2008: This was a mistake. While running the Wizard I accidentally typed in a "Q" instead of an "R". You're right. Thanks for pointing it out. For everyone else, it happens around time index 10:00. I just didn't catch it at the time.
israel  rivera on 1/14/2008: Hi. If I bought a course without the PDF handbook, but find that I may want to purchase the handbook after all, how may I do so?

Richard Rost on 1/17/2008: Just go to www.599cd.com/HandbooksOnly and you can purchase the handbook from there.
Angela on 4/2/2008: There is a section in this lesson that apparently got skipped. It's only a few seconds, but for beginners, every word can be vital. The time on the video where it omits something is right around 18:47. You are discussing the Yes/No field and then all of a sudden there is a formatting that is displayed on the screen for the phone number field. I did figure out what was omitted, but thought this should be brought to your attention.
Richard Rost on 4/2/2008: Angela, you are correct. Right around 18:47 there is about 10 seconds of video missing. I was showing you how to create a format for a phone number using the characters: @@@-@@@-@@@@. It's visible in the video, but the explanation somehow got cut off accidentally in editing. Next time I update the video, I'll have to make sure to fix that!
salvador garcia on 4/15/2008: Hi Richard,
I have some records in excel files. In order to work such data in Access is there a way to improt such data into an Access Table? tks in advance for you assistance.

Richard Rost on 4/16/2008: Salvador, you can go to FILE > GET EXTERNAL DATA > IMPORT to bring in the information from Excel into a table. I cover this in Access 308.
  on 4/28/2008: How do I find the Table for Access 102 for practice?
Richard Rost on 4/29/2008: All of the files for the Access classes have been moved to the Student Database Files web page.
salvador garcia on 4/30/2008: Hello I finished Acess 102.
I tried to presetn my test however I did not find in the list Acess 102. Is acess 220 the test to take for this level? it looks to me a higher degree. Pls comment. Tks Salvador

sony cohen on 5/1/2008: I have a field named priceFOB (currency / 2 decimals / standard ) but the system rounds it 15.20 => 15.00 , do you know why ?? any idea where to look at ??
 Chris Morgan on 5/16/2008: When I right click on the picture box I don't get an "insert object" option. When I drag a pictire into the box, it won't show the picture, just the file name. If I click on the file name, I can see the picture in a separate window.
Kevin Dore on 5/18/2008: When I try to insert a picture as an OLE into a form, only the picture file name appears in the box and not the picture. How come ?
John D on 5/20/2008: How do you apply a sort criteria that contradicts another? I have a table of customers and only certain zip codes will be included in a mailing. However, some of these customers are marked VIP - which means, a separate text field includes these three characters. How do I have mail sent to these customers, even though the zip sort has excluded them?
Thanks

Richard Rost on 5/24/2008: Salvador, the test for Access 102 is NOT available at this moment. I rebuilt the test-taking portion of the web site a little while back, and I haven't finished putting all of the tests back online yet. I'll post a note here when it's ready. Sorry for the confusion.
Richard Rost on 5/24/2008: Sony, if it's definitely a currency value at 2 decimals, it shouldn't be rounding it like that unless you have some other type of formatting established in your form... is this happening at a TABLE level too or just in your form?
Richard Rost on 5/24/2008: Chris, go to the properties for the box and make sure it's a BOUND OBJECT FRAME. I don't know why you don't see the "Insert Object" option. That should always be available. As far as the picture/filename problem goes, in the properties for the box, make sure that the DISPLAY TYPE property is set to CONTENT.
Richard Rost on 5/24/2008: Kevin, I was just researching this myself, and I found some interesting information about some changes in Access 2003. I don't know if Access 2007 behaves this way too, but here's what I found out:

Microsoft used to distribute MS Photo Editor with previous versions of Office (97, 2000, and XP). Apparently, they don't include it with Office 2003. MS Photo Editor is the "OLE Server Application" for images inside of Access. So, basically Access 2003 can't display images inside of forms that are Bound in the table like Access XP and 2000 could.

Real smart, Microsoft.

Anyhow... if you have an older version of Office, you can install JUST the MS Photo Editor component AFTER installing Access 2003, and that *should* fix the problem - although I haven't tested it myself, so I can't say for sure (this is just what I'm reading on various tech sites).

NOW... keep in mind that Access only behaves this way if you try to click and drag a file from the desktop (like I show in the lesson) or you try to Insert Object... the picture. You can, however, still display the picture properly if you COPY AND PASTE it from a graphics application.

For example, I just opened a JPG image in my web browser (either Internet Explorer or FireFox) and then I right-clicked on it and selected COPY. Then right-click on your OLE Object Frame in your Access Form and select PASTE. If came in just fine and displayed properly.

The same would work just fine if you opened up an image in MS Paint or even something like Photoshop. As long as you're copying and pasting a PICTURE and not a FILE, Access will display it just fine.

Very strange... but that's the deal.


Richard Rost on 5/24/2008: John, your SORT won't exclude anyone - just display the records in a different order. What you want are TWO criteria where you say, "include all of these ZIP codes AND all of these VIP customers." I show you how to do something like this in the next lesson (Queries) where you see how to include all customers from NY and all Active customers. Same concept.
Meena Goel on 6/24/2008: Hi,
My PCResale.Net Customer dbs are read only? I got them from the student db files.

Richard Rost on 6/26/2008: If they're set to READ ONLY that's because I pulled them off the production CD. When you burn a CD, the files usually get set to read-only because you can't write back to a CD-ROM. Anyhow, all you have to do is right-click on the file and go to PROPERTIES. Look for the READ-ONLY attribute and check the box off.
Meena on 6/26/2008: Thanks, your courses are great! The specified process worked after I extracted the files. Thank you!
 Kyle on 7/9/2008: Richard,
Do you know how I would do a report getting strickly email addresses (with commas after each one) and then export them to a file (like csv file) so that the secratary can cut and past to send many emails at once (like on microsoft outlook). Thanks!

Richard Rost on 7/22/2008: Kyle, at your stage of knowledge (Access 102) I would suggest just copying and pasting your list of email addresses from Access into a text editor like NOTEPAD. Then you could select them in groups of 20 or 30, copy and paste them right into Outlook. Yes, it's possible to come up with a fancier solution, but that would involve some programming.
Brigid Kowalczyk on 9/11/2008: In my database, i have some of my own records which include IN for Indiana residents. When I tried to put IN for a criteria, Access wouldn't let me. What to do? How do you rename a state? time is 6:30 on the access 102 video.
Richard Rost on 9/12/2008: Brigid, IN is a reserved keyword in Access. You have to make sure you put it inside of QUOTES if you want to use it as a parameter. You really should put all text inside of quotes. Even NY should be ="NY". So make sure instead of just =IN you have ="IN" and you should be fine. I just tried it and it worked OK for me.
Nathan Wittmann on 9/24/2008: Is it possible to create a criteria that will allow it to recognize part of the cell? For example if I have data that is the following:
Clif Bar
Is there a way I can use a criteria like "Clif" and allow it find Clif Bar. When I do it now it ignores it because it is not exactly the same. So I want to be able to find just part of the info. I would also like to put in the letter "b" and bring everything that has a "b" in it brought up in the query. Thanks for your help. Also I appreciate all the help in the past. Thanks,
Nathan

Richard Rost on 9/27/2008: Nathan, you use the LIKE keyword to show just part of a field. For example, in a query to show anything that begins with "Clif" you put this in your criteria:

LIKE "Clif*"

To show anything that starts with the letter B you put:

LIKE "B*"

To show anything that has the word "ball" anywhere in it, you put:

LIKE "*ball*"

And that will give you "baseball," "football," "ballgame," etc.

 Chuck crawford on 11/7/2008: I have the Access 101 CD and the 102 download but neither seems to have a "help" folder nor the practice database
Please advise
thanks

Richard Rost on 11/10/2008: All of the database files have been moved to the web site at www.599cd.com/Access/StudentDatabases.
Reilly John on 11/18/2008: I have a test retest situation. I want to select the highest test score in the series of tests for each student for each student and get a difference score between the highest score and the initial. Then I want to select students that have a difference score greater than or equal to 5 points. I presently have an Access program that compares the students last test score with there initial score. I want to change this to use the students highest score from the field of test scores versus getting the most recent score diference. I have an active Student table with initial scores and Student progress table with the scores achieved at subsequent testing periods.My query selects all of the scores of each client. I want to select the highest. Sorry for the repition but I hope it helps clarity.Any suggestions? Thanks
Gloria Permint on 12/6/2008: Good evening Richard,I'm using Access 2003.When I click on the command button,I receive a message that says Module not found.When I close the form,the Microsoft Visual Basic window opens and when I click to close a pop up message says This command will stop the Debugger.Can you please explain and help me correct this error.
Thanks,Gloria,a long time student and customer.

 Chris Judge on 3/8/2009: Hi Richard,

I can't seem to find Tests for Access 102, 103 and 104 on www.599cd.com/tests

There is a 101 test (Test 4) which I have taken but Test 5 jumps to Access 220. Have the rest of the 10- tests been removed?

Thanks

Chris

Richard Rost on 3/9/2009: Chris, I'm working on revising a lot of those tests, therefore many of them are offline. I hope to get them back up soon. Thanks.
 Michael on 7/31/2009: In the Notes field on the form you showed us how to "zoom" the Field so the user can type in a more friendlier way. How do you set the field to automatically do that. IN other words not require the user to hit the combination of shift key...
Richard Rost on 8/3/2009: Michael, this would involve some programming on your part, which we haven't gotten to yet - and certainly is well beyond Access 102. In a nutshell, you'd need to issue the zoom command using VBA code whenever the field is clicked on (the OnClick event) or even make your own zoom button.

For my beginner students, I just recommend making the field larger.

Randy Montalvo on 9/4/2009: Hi Richard. I am using microsoft office 2003 pro, if that makes a big difference. I am having the same as everyone else about the picture not showing up just the file name. I checked the display type and it does say content. What else could be the cause? Thanks in advance for the help.
Randy Montalvo on 9/4/2009: Sorry Rich forgot to add that it was in Access 102, Video 6, time 13:20.
Dave on 9/14/2009: Hi Richard - In my database, I am thinking of having as one of my fields an OLE box for a map that my field techs can refer to. The map would be drawn on Google maps or some such sources with some notes applied and then printed to an Adobe file. I am guessing my database could get big and slow quickly, as after I downloaded the first map my file size went to 7 MB. Is this going to be a big problem after I have a bunch of records? Also, I noticed I could select a bitmap but not a JPEG.
Richard Rost on 9/16/2009: Dave, yes, saving images in your database can make the tables get very large, very quickly. For your case, you might be better off saving the images as files on your hard drive, and then linking to them, instead of pasting them directly into the tables.
Dave Sterner on 9/16/2009: Thanks, Richard - is the linking to a file instead of inserting an image to be covered in a future course?
Richard Rost on 9/16/2009: Yes. I cover it in Access 203, Lesson 6: Pasting v. Linking Bound OLE Objects.
 Theo on 10/14/2009: Hi, I am looking for a copy of the database for the lessons in Access 102. I do not have a Help folder in C:\Program Files\599CD\Access 102. Is there anywhere I can download it from?

Richard Rost on 10/14/2009: All of the student databases were moved to the STUDENT DATABASES page.
Maks Kelemina on 10/14/2009: How should I go about to run overdue query
Richard Rost on 10/15/2009: By "overdue query" I assume you mean all orders that are, for example, more than 30 days late? For that just use a simple criteria in your query:

DaysLate: Now()-OrderDate

This will give you the number of days late. Now for the criteria, just say:

>=30

And that should do it. I cover a lot more about this (overdue invoices) in future lessons, especially when we get into the more advanced query types.

Bill Donegan on 10/21/2009: Richard, I have bothered you already about a problem I am having..Its intermittant...In your class 102 video 5 at approx 7 minutes you are building the new Employees form. You are able to change the background colour using your "format" toolbar.....ever before you insert any fields on form. I can only get the foramt toolbar to work when I have placed a control on form and I have selected that control. Why is that....it does not happen all the time which is frustrating...Im using Access 2000 Pro on Xp
Thanks

Richard Rost on 10/21/2009: Bill, I'm not sure. I do have one machine here running Access 2000 on a Windows 2000 machine, and I don't have that problem (in fact, I just tried it again). I will say that Access 2000 is a bit buggy. Make sure you have the latest service packs for AC2000, if you can still get them.
 mahabbas on 11/9/2009: just curiuous regarding interface in Access 2007 as I don't have a similar interface to practice in exactly the same manner. Moreover, the student file downloaded does not open in Access 2007. Certainly the foundation training is similar, but the look and feel, the navigation and enhancements are different.
Richard Rost on 11/11/2009: Mahabbas, you are correct. The foundation concepts are exactly the same, but the interface is completely different. I'll be covering the Access 2007 interface very soon in upcoming lessons.
 Mike on 11/20/2009: Richard, really enjoy the videos and feel that the test hammers home anything I might have missed. Just checking back in on the timing of the Access 102,103, and 104 tests being put back on the test archives. Thanks again!
Richard Rost on 11/25/2009: Mike, I don't know if we'll remake the tests for the old Access classes. We'll most likely have new ones for the Office 2007 classes coming up.
Judith Koester on 12/15/2009: I created a customer table and decided I didn't need some items and deleted a couple of rows. When I created the form, these items show up in for field list and I get a Enter Parameter Value box when I open. How do I get rid of these?
Richard Rost on 12/15/2009: Judith, try not to use the terms "rows" and "columns" when working with Access. Those are spreadsheet terms (like in Excel) and can confuse you in Access.

In a database, we have FIELDS (columns) and RECORDS (rows). So did you delete a couple of fields, or records?

I'm assuming you mean FIELDS, and the "rows" you're talking about are actually the TABLE DESIGN equivalent for fields (since they look like rows on the screen).

Now, if you make a table, then build a form off of that table, then go back and delete FIELDS from the table, you might get the "Enter Parameter Value" box up when you open the form because Access it trying to find fields that no longer exist.

You'll have to open the form up in Design View and remove those fields off the form too. Plus, if you have a QUERY between the form and the table, you'll need to clean that up too.

Hope this helps.

Judith Koester on 12/15/2009: Yes, I am talking about fields (doesn't help much that ACCESS calls these rows and columns in table design view). The fields don't show up in table design view, but when I am putting together the form and using the Field List the deleted fields show up on the Field List. I'll keep working on it, but am puzzled. Forgot to tell you that I renamed one of the fields rather than deleting it. Could that be a problem? Also am trying to figure out the new "ribbon" on 2007. I'll keep trying.
Richard Rost on 12/15/2009: Once you delete a field from the table, as long as you close everything down and reopen it (table, form, etc.) then those fields SHOULD disappear from the field list. Also make sure you have the latest service pack for Microsoft Office installed. There were some nasty bugs in the first version of Access 2007.
Judith Koester on 12/15/2009: I found out how to get rid of the unwanted fields on field list. I right clicked on field and selected Edit Record Source which opened SQL Statement Query Builder. The weird fields which I deleted showed up there and I got rid of them. Mistakes are a great learning tool.
 Mike on 12/19/2009: If I were to add a field in a Table, will it automatically update the Form?
Richard Rost on 12/20/2009: Mike, no. You will have to open the form in Design View and add the field from the field list or toolbox.
Dianne  Slade on 2/23/2010: In Access 102 Lesson 7, the CustomersWithFullAddressQ had 4 entries, but the CustomersWithFullAddressR (mailing labels) has only 3. I get all 4 when I run the report.

Reply from Richard Rost:

Was one of them missing some information? Remember, we excluded companies with NULL data in any address field.

 julius on 2/23/2010: Hi, this is julius, i want to ask you about field and data type. Is it possible to make an increment on text data type rather than autonumber data type? For
example I want to use a prefix on my id number let's say A123, when i go to the next page the next number should be A124 and can i edit it also coz autonumber data is not editable? Thanks and God speed.

Reply from Richard Rost:

You cannot do this with any of the built-in data types in Access. You would have to create your own text field and increment the counter yourself with VBA code, much like I do in this tutorial.

John Williams on 2/25/2010: I have been working through the Access 101 and 102 and am making good progess. I have had difficutly in following Lesson 5 (14mins in)in practice. When I right click it to insert an OLE objectI do not find an 'Insert Image' key or when I try to drag an icon of an image into the field box it will not move into the box. I am using Access 2000, please tell me what am I doing wrong? regards John

Reply from Richard Rost:

Add your OLE object field to your table and then your form. Right-click on it in FORM VIEW mode and select INSERT OBJECT. Select CREATE FROM FILE. Now pick your image file. I just tried it with Access 2000 (to refresh my memory since it's been so long) and it works fine in 2000.

 Elizabeth on 3/12/2010: When I inserted the combo box I got a -1 displayed. How can I correct it.

Reply from Richard Rost:

It sounds like you picked a TRUE/FALSE field for your first displayed field. That will show up sometimes as a -1 (true) or 0 (false). Try re-creating the combo box, following the steps from the video EXACTLY.

Robert Lewis on 3/12/2010: The content and teaching is really great. Best I have ever had and good value too. My only gripe is the sound quality. It's crackly and some spoken words jump making them almost inaudible.

Reply from Richard Rost:

Robert, very sorry about that. Access 102 was one of the first seminars I recorded... YEARS ago (2003 I believe). The sound quality has gotten MUCH better since then.

Elizabeth Ricketts on 5/6/2010: When I add a command button the command button wizard box does not pop up. The button appears on my form (in design view) but no wizard. This happens with the student database and one of my own. I have Access 2000.
Happens in the video at approximately 33:10.

Reply from Richard Rost:

Elizabeth, there is a button on your toolbar to turn the wizards on and off. I'll bet your wizards are turned OFF right now. If they are, you'll get the button, but the wizard won't start up. Just click that button to turn the wizards on and then recreate the button again.

 Lynn on 7/19/2010: Is it of much practical significance that 2 GB is max size of Access Database? Is this of much real world concern? Have you seen this occur?

Reply from Richard Rost:

It's not a big deal. 2 GB is the max size of any ONE database file, but (a) I've got some pretty big databases that are well under that size, and (b) you can always split your database and link to those tables. My main 599CD customer database, for example, consists of 3 parts... a "front end" database which has the forms, reports, queries, and VBA code which is only about 100 MB; and two databases that contain all of the tables (one for customers and orders, and a second just for my email addresses and customer service history). There is no limit to the number of external databases you can LINK to, so really there is no PRACTICAL limit to the size of your database.

william mccallum on 8/26/2010: there is no "help" folder in my download of Access 2 so I don't have the database from 101
JC ARANA on 9/19/2010: I had just purchase the Access 102 class and Im looking to download the PCResale.net Customer DataBase file but did'nt download into the C:\programfile\559CD how can I download it please..?

Reply from Richard Rost:

All of the databases were moved here: http://www.599cd.com/access/studentdatabases/

Edward Devine on 1/17/2011: Does the picture in access 102 employee table use as much memory as the memo one

Reply from Richard Rost:

Both images and memo fields will use as much space as they need to store their data. So, in theory, a tiny picture could use less space than a memo field with a few thousand characters in it. Don't forget to COMPACT & REPAIR your database so that any extraneous bloat caused by those objects is removed.

 eddy geijselaers on 4/28/2011: Dear Richard,
I made a query to select data presented in a report. I am working with Access 2000 or 2002; confusing thing, the info in Access itself says 2002, the database windows say 2000?!!
In order to get the results I wanted I sorted the ID fields of several data-fields I want to have in my report. But sorting on the ID fields doesn't give the expected results.
Reason for example; the Quality of wine in my database from low to high might be; QmP as lowest quality and Icewine as highest. In between are several others and picking the Quality from the Combobox (with NotOnList)I don't want to see them in Alphabethical order.
Also other fields in order to present the report more detailed are sorted. The sorting order is the same in the query as I want them to show up in the report.
What to do to overcome this problem?

Thankx

Reply from Richard Rost:

You might be using Access 2002, but your database might still be using the Access 2000 format. That's not a big deal. I wouldn't worry about it right now.

Any time you pull data into a REPORT, the REPORT is going to control the sorting - and will often ignore the sorting in a query. You need to set up a SORTING AND GROUPING LEVEL in your report to handle the sort there. This is covered in Access 104.

eddy geijselaers on 4/28/2011: Thankx Richard, I will wait untill 104 to solve the problem. Meanwhile I will try to pull in the ID Fields into the report. Being able to sort on them should fix the problem. I hope I can "hide" them in the report.
eddy

 Elaine on 5/23/2011: I have Access 03 and 07 when I open the Access File it has the 07 interface, when I add the command button it gives me this error message whn I try to close it
The expression on click you entered as the event property settng produced the follow error: Object or class does not support the set of events.

Reply from Richard Rost:

Something is wrong with the code in your form/button. I'd have to see it to tell you exactly what the problem is. Try recreating it again with a fresh button (or if need be, a fresh form) and see if it happens again.

 Leo S. on 6/4/2011: Hi Richard,

I love your classes. Your instruction style is clear and concise.

I wanted to know if it is possible to add an additional line on my parameter query. This is just a matter of style and I know I can work around it but if there is an alternative, I would find it useful.

So, I would want the parameter query prompt to read and appear as follows,

Enter Social Security Number
(XXX-XX-XXXX)

Gilbert on 6/18/2011: I am using Access 2007,and when I am building a report from the design view the only option that I can see is for adding the fields from a table and not from a query. what am i doing wrong?

 Wendell Satterwhite on 1/14/2012: When you use ">" for 500 wouldn't that elimintae values = 500 shouldsn't it be >= 500?
 Wendell Satterwhite on 1/14/2012: Why do you not like using form wizzard?
 Wendell Satterwhite on 1/14/2012: Compact and repair option/ Some recomend putting tables is a seperate data base and linking them back to User data base in case of corruption so no data is loss. What do you advise?
 LadyPurple on 1/17/2012: Why is it that I have purchased these, but they do not show up. Ther were purchased in July of 2009 Access 101,102,103,104. I am reviewing and i can't get to them. Thanks
 Heidi on 5/18/2012: Hi. Your videos are great but I'm stuck on trying to create a combo box using a table as the picklist. I created the table and saved it. Only has 1 column. but when I add it into the form, the dropdown does not pull up any of the selections from the list. It is only 1 blank row. What property adjustments might I be missing?
 Natalie on 6/19/2012: Hi, thanks for these videos-- they have been life altering!

I have a question for you regarding forms. Here is our scenario:
1. We are importing an excel table from an external source.
2. This table contains some, but not all, of hte information we require. I have added the extra fields into the table.
3. I have created a form that can be used to call up that record. Some fields will autopopulate from the imported table, and some are blank and ready for the additional information to be entered.

It all works quite brilliantly except... I want to allow for the scenario where a record is not already in the imported table. Ideally, if a record is not in the original table, I would like the form to blanked, allowing for ALL fields to be manually entered.

What I found was that if I entered the identifier for the record (indexed, no duplicates allowed), if it was not on the list, it CHANGED the current record to that new number. This cannot be allowed. If I made the field so it could not be edited, I couldn't enter any new numbers at all.

What I would like is- if I entered the unique identifier and it is in the table, that it calls up that record (which works), but if that unique identifier is NOT in the table, I would like a blank form to come up.

Is this possible? Thanks,
Natalie

Reply from Richard Rost:

Life altering? Wow. Thanks. :)

Sounds like you should use a DLOOKUP to determine whether or not the record exists in the other table. If so, import it. If not, load a blank page. The bad news is that this is going to involve a little programming. The good news is that you can probably do it with just a few lines of code. Check out the link for more help.

Carl Kowalski on 9/18/2012: When I dropped a picture into the OLE object the picture was not shown but an icon was shown and when I double clicked the icon the picture openned to a full screen picture. What am I doing wrong? I am running Windows 7 with Access2007.

Thanks,
Carl

Reply from Richard Rost:

Are you using a BITMAP picture? Don't use a GIF or JPG.

Carl Kowalski on 9/19/2012: Well that is probably the problem as I believe I was not using a bitmap. I thought the lesson video indicated I could use a JPG, my mistake. Thanks for the help. More questions will, with out doubt, follow.

Carl

Reply from Richard Rost:

You CAN use a JPG or GIF, but that depends on the image software you have on your PC. By default, Access can only display BMP images. It passes off everything else to whatever imaging software you have. Microsoft Photo Editor tends to work, but the support is iffy. That's why I say if you NEED to display images in your database forms and reports, stick to BMP.

Delzoria Blair on 2/2/2014: How do you filter a form showing all data records using a button. Ex: I am creating a movie collection database. I have a form showing all the titles, genre, ratings etc in sorted format. If I want to show all the titles that start with the letter "A" also in sorted format how would I do that?
 

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