Access 2007-2016
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  
 
NEW Release: Access Developer Level 3   dismiss
 
 
Courses - Microsoft Access 201
Description: Intermediate Microsoft Access 1
Running Time: 68 minutes
Pre-Requisites: Access 104 very strongly recommended
Previous Lesson: Access 104
Next Lesson: Access 202
Main Topics: Relational Databases, Multi-Table Queries, Global Relationships, Combo Boxes
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 7/31/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 201
Intermediate Access 1

Creating relational databases, relating customer and contacts table, relational combo boxes, much more. 68 Minutes.
 

AC201 Major Topics

  • Relational Database Concepts
  • Queries with Multiple Tables
  • Customer Combo Box
  • Global Relationships

This is the first of the Intermediate series of Access courses. We will begin by learning about relational database concepts - and why relational databases are more powerful than traditional "flat file" databases.

 

Next, we'll revisit the contacts table we constructed in the last class - this time, we'll build it properly and make it relational.

 

We'll then construct a query to relate the two tables together (customers and contacts).

 

We'll update our contact report to deal with the new changes in our database.

 

Next we'll build a contact form to display and edit this data. On this contact form, we'll create a combo box to allow us to select the customer from our list of customers. This way we don't have to know the customer's ID - we just pick them from the list.

 

We'll add the button for the new contact form onto our Main Menu.

 

Next, we'll take a look at Global Relationships between tables, why they're important, and why we're not going to use them... yet.

 

This is the big one! Don't miss this course. We cover a lot of very key concepts in this course - especially our discussion on relational database concepts, and learning how to create a combo box to look up a value from a different table. These are very important concepts, and we will build in them on most of our future Access courses.

 

 

Access 201 Outline
 
1. Introduction

2. Relational Database (RDB) Concepts
Why are RDBs important
One-to-one relationships
One-to-many relationships
Many-to-one relationships
Many-to-many relationships
Normalization

3. Relational Contacts Table
Making our contact management relational
Inserting a CustomerID in ContactT
Removing duplicated fields
What is a Foreign Key?

4. Contact Query
Manually creating query relationships
Deleting relationships
String concatenation
Dealing with duplicated fields
Correcting our contact report
Manual formatting of the phone field

5. Contact Form
Constructing a contact form
Using a combo box to pick the customer
Manually editing combo box properties

6. Miscellaneous
Add contact form button to main menu
Global relationships between tables
Creating and deleting links between fields

7. Review
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 201

Richard on 1/1/2007:  Creating relational databases, relating customer and contacts table, relational combo boxes, much more. 68 Minutes.
Josiah Whitman on 11/19/2007: I've signed up for 201, 202 et al and have found that the database files haven't appeared in the "help" folder within your downloads. Is this still a feature?
Richard Rost on 11/21/2007: Josiah, we moved the files. When you play the very first video of each course (if you're online) you should see a link to all of the video files: http://599cd.com/access/studentdatabases
 Joe on 2/12/2008: Does it matter if you use the CustomerID from the ContactT table or the CustomerT table? This is in regards to your course 201.
Richard Rost on 2/28/2008: While building a simple contact query? No it does not matter which one you use and they have the same value.
Joe on 2/28/2008: I've taken yoour database that we created in the lessons and made it a front end/back end system where only the tables are in the back end. If I have all of the forms and queries in the front end and I want to make a check box field called "approved" on a form named LeadDetailF which uses LeadDetailT but I want it to do something like this: IF the box is checked THEN 3 different fields in the LeadDetailT cannot be updated by anyone. I'm trying to lock down 3 fields in the table because there will be a dozen people in my office that will have the front end db on their PC so I can't lock down forms, or can I? If so, can you tell me the code?
Richard Rost on 3/1/2008: That can certainly be done, but you'll need a touch of programming. Starting in Access 301 I cover VBA programming. You can use an AfterUpdate event to say, "IF this field is checked, THEN make these three fields disabled." It's programming that goes right in the form field.
 vatsal sanjiv on 3/15/2008: sir i want to create a formula n i m not able to do so..
for instance i purchased a policy on 01/01/2001 n that will get mature on 01/01/2010

the amt of premium which i have to pay will be on 01/01/2001...
01/01/2002...
01/01/2003...
.
.
.
01/01/2010

i want to make such a table which automatically generates dates(containing year also) and it must have another feid of status (paid/unpaid)
eg
01/01/2001 paid
01/01/2002 paid
01/01/2003 paid
01/01/2004 unpaid
01/01/2005 unpaid
01/01/2006 unpaid
.
.
.
01/01/2010 unpaid

n its must also have a filter


Richard Rost on 3/19/2008: What you basically need to do is pre-fill a table with payment amounts for each policy. This is not easy to do, and you'll need to learn how to use something called a RECORDSET in order to do it (or you could use a loop with an Append query). I'll be covering Recordsets in an upcoming lesson. I'll keep your question on-hand and try to use it in class.
 Eva on 3/31/2008: I have one Shop called Cottage Stores I want to give it a Month's orders free how do this in a Query Please. I love this site
Richard Rost on 4/1/2008: Eva, I would need to know much more about what you're doing. What do you mean by "give it a month's orders free?" Do you mean that you have one customer who should get all of his orders free for a month? And you want to do this in a QUERY?

I would do this in the order form (later on in Access 301+) when a product is added to the order... have it's price automatically come in as ZERO if the "GiveFreeOrders" value is checked on.

In a query, you'd probably just have to use an IIF statement to check the dates, an assign the total to ZERO if you're in the right date range.

Very interesting question however.

Richard Rost on 4/24/2008: It's as if they read my mind... Access 2003 includes a step in the Combo Box Wizard that asks you what field you want to SORT your combo box by. Good addition!
Judith Koester on 5/24/2008: I have more than one client per project. When I run a query, I get duplicate project names since the project and client tables are linked. How do I eliminate the duplicate project names?
Richard Rost on 5/24/2008: Judith, what exactly do you want? Just a list of projects without the client names? If that's the case, just use the project table. If you generate a query with projects linked to clients in a one-to-many relationship, you're going to get duplicate project names. Now, if you're generating this for a REPORT, then you can use a SORTING & GROUPING LEVEL to only show the project name once and then a list of clients underneath it. Remember this from Access 104?

If I'm wrong and you're trying to do something different, please let me know.

Kevin Dore on 5/25/2008: I have created a database on my PC at home and I want to email it to my office PC or save it to disk so that I can use it at the ofice. I have tried many times to do this without success. How do I go about this please ?
Richard Rost on 6/3/2008: Kevin, all you should have to do is simple email the MDB file to your other PC (or burn it on a CD) and then open it up on the other computer. That's it. All the info you need is in that one file - unless you have external linked tables.
Walter Pohle on 8/16/2008: I do not have Company names
but word like to fill in with Last&First Name in the
Customer Field .Can this be
done on form or do you have
to use a Query

Richard Rost on 9/8/2008: Walter, you want to put the FirstName and LastName fields together in a form field without using a query? You can do it by setting the ControlSource of an UNBOUND text box to:

=FirstName & " " & LastName

But your field will NOT be editable.

Nathan Wittmann on 9/11/2008: I have created a form and have noticed that if I start to put info into the form and tab through without finishing the form it auto saves the info with out using the save record button i have placed on the form. I want to use this button to do multiple validations, but that wont work if it will auto save when you tab through. I want it to just loop through and have the user save only if they select the button. So in short is there a way to turn the autosave feature off? Thanks for your help!
Richard Rost on 9/11/2008: Nathan, if you open up the form's properties there is a CYCLE property. Change that from All Records to Current Record. That will make the TAB order return to the first field on the current record instead of moving to the next record. As soon as you close the form or move off the record, the data is saved to the table. You can control what happens then with EVENT programming. For example, you could put code in the form's BeforeUpdate property that would let you CANCEL the user saving the record if they don't enter valid data - this is much better than just handling it in a button. I cover the BeforeUpdate in my Access 305 class.
Robert Yarbrough on 11/1/2008: Two questions. Using the form developed in the class, how do you add an entry (note) for an existing customer, not just write over an existing entry? Also, how would you run a report for a 90 day follow up on contacts from only the latest entry date?
thanks

Richard Rost on 11/7/2008: Robert, you would need to create a separate, related NOTES table (very much like our Contacts table) where you could have multiple notes for each customer - with a date. In fact, you could just use the Contacts table to store notes. That's perfectly fine. Now, to make a report for a 90-day followup, just make a query where your date criteria is
David Bender on 2/3/2009: Hi, for CustomerID @ 09:04 what if you had hundreds if not thousands of data entry’s how would you identify the customers, I’m sure not one at a time.

Fran Raymond on 3/6/2009: When to use a lookup tables?? In my database, schools apply for ecoschool status each year. They achieve bronze, silver or gold. Some years we have a few schools apply, other years we have many. I'm thinking i should have a main table (tblSchoolMain) that links to a status-by-year table (tblStatusYear) and the Status field and Year field in this table should each be lookups to a lkpStatus and lkpYear. I will now have 4 tables - does this sound right?
Richard Rost on 3/9/2009: Fran, that sounds fine to me. As long as everything is properly related, it's usually BETTER to design the database with every 'thing' in a distinct table.
 Raul on 6/21/2009: Mr.Rost:
I would like to make a formula and the result be save in a field. So I can make an invoice for customers.

Maintenance Fees=(1)*65+9Lots-10*24
How can I do this?
Thank you for your attention to this matter.
Raul Pendas

Richard Rost on 6/25/2009: Raul, if you want just a normal calculated field, you can do it with a query. That's easy. We will cover that in Access 220. I also have a tutorial on it in the Tips & Tricks section.

Now, if you want that value to calculate AND be stored in a table field (perhaps so you can edit it), you have to do some more work. You need to use some VBA programming (or a macro - but I prefer the VBA). I cover it in Access 303 because it's a little more advanced, but again I have a Free Tutorial explaining how to do it.

There is a LOT more to it than I cover in the Free Tutorial, of course. Get the full Access 303 class for complete details.

Jonathon Mun on 7/14/2009: I'm having trouble linking data between two tables. First table is a Customer table and the second table is a customer issues table. Both tables will be populated monthly from an Excel table import. If I were to enter each issue for the customer manually, it would be easy to select the proper Customer. But because the tables are being imported with Customer data and not the Customer ID, I cannot easily create the relationship I need.
Richard Rost on 7/16/2009: Jonathon, without some piece of data to RELATE the two tables together, you're going to have a hard time. You need a customer ID, customer number, or even something unique like a phone number where you can piece the data together. What information in your CustomerIssues table relates the Issue back to the Customer?
 Carolyn Quinn on 7/17/2009: This is great! However, what if someone else is using the database and do not know the customer ID? I would need to always have a handy customer ID list showing so they will know what CustID goes with what ContactID. That is why a subform would be used. right? Now i need to understand subforms or should i develop a query with the info i want to show up and make my form based on that query?
 Carolyn Quinn on 7/17/2009: Okay - don't answer my first question. It was too early to ask. I need to know something else. I don't like to have the auto numbers so close to the same thing. I want to change the Contact number to start a little higher up the ladder of numbers. How do i do that? Thanks
Richard Rost on 7/21/2009: Carolyn, that's why we'll use COMBO BOXES for picking a customer. Your end user will never have to know an ID. They'll be able to pick from a combo box.
Richard Rost on 7/21/2009: Carolyn, you shouldn't have to worry about what your IDs. Your end users don't even have to see them. They're completely for Access and relating your tables together. HOWEVER, if you want to "kill" a bunch of Autonumbers, just insert a bunch of blank records and delete them. If you want to artificially inflate your Autonumbers so your first customer isn't ID 1 then just insert a record, copy and paste it 1000 times, then delete it.
 Cindy McPherson on 7/24/2009: Is there a way to change the data in a table field from the decription lookup field to the key number field? I have 800+ records & do not want to change them all manually.
Richard Rost on 7/25/2009: Cindy, I would need more information on this to help you, but you can use an UPDATE QUERY if you need to move values from one field to another.
 Gary on 7/29/2009: I changed my replaced auto numbers with new column and deleted the old column. now when I add new info i get id numbers that conflict with the old autonumbers. can i fix it?
Richard Rost on 7/29/2009: What do you mean they conflict with your old Autonumbers? Let me see if I understand you... you had some records (lets say IDs 1 to 10). You deleted that ID field and then added another one. Now you're being reassigned new IDs that just happen to relate to other records in your database (someone already has ID 3 for example). In this case, you're going to have to just WASTE a bunch of Autonumbers. Insert about 100 blank records, then delete them. That should waste IDs 1 to 100. Of course you SHOULD have deleted all of the related records along with those IDs in the first place, but that's OK. Does this help?
Manan Mem on 9/27/2009: hi.
i have a simple table of 3 fields (all info are filled in already) barcode, product name and price. I need to form or query where the clerk can type in (not selecting from list like combo box) the barcode and have the product name and price displayed. thanks

Richard Rost on 9/27/2009: Manan, in order to do this, you need to learn how to use the DLOOKUP function, which is a little more advanced. I cover it in my 300-level Access classes. Here's a sample tutorial.
 Bill Donegan on 11/3/2009: When I was inserting combo boxes I got them right, first pass, about 50% of time.I believed the combo was "actually" storing data. But it doesn't really.It shows the second field selected after the ID field in the "combo-design" field drop-down box. I'm wondering how it does that, and discards any other selected fields used in the combo itself. So if it doesn't work first time I just delete and build anew.
Slowly coming along!!

Richard Rost on 11/3/2009: Bill, I'm not sure I understand what you mean. The combo box wizard shouldn't "discard" any of the fields you select. I've never had this problem before. Are you sure you have all of the updates and service packs for your version of Access?
 Bill Donegan on 11/3/2009: Richard, I was just relating my experience with building various combos and I shoulda taken your advice that I've since come across .i.e. go over the stuff several times, rather than trying to apply immediately...I just wondered if anyone else was having similar difficulty grasping combos..they appear simple..my error was in believing that by selecting a value in the rows shown in combo that I was actually storing that data...not just the ID...I now know that all I'm storing is the ID....the appearance of the e.g. CompanyName field data in the combo might lend the impression that this is actually stored somewhere...Now, I know that's not so....you cover it in 202 when you demonstrate that the RowSource property is actually "CustomerID" and it's really the ID thats in the field...could be confusing....And arising from another posting, I have updated all my Office to ensure that I'm running the most up to date release of 2000. But still getting the greyed out formatting buttons in Form Design....random...maybe will discover cause sometime. Many Thanks for your help (and your courses).
Bill

 Bill H on 2/22/2010: I have an existing database with a “projects” table. I have it working with a one-to-many relationship with a client table. I have multiple projects with the same client. I need to update the projects table to include an additional one-to-many relationship to include several project managers working on the same project, i.e., architect, mech. engineer, elec. engineer, etc. Can you please point me in the right direction with the necessary videos I need to watch? (Please don’t say all) I have watched Access 201 and 203. Thanks.

Reply from Richard Rost:

All. :) Ha ha ha.

Seriously, you just need to do the exact same thing again. You already have a one-to-many from Clients to Projects. Now you need a one-to-many from Projects to Managers.

So, set up a Managers table. Add a ManagerID to your Project table. Create a subform in your Project form (or even a simple listbox) that show the Managers. The design layout is up to you.

The only problem you might run into is that you can't have a subform on a CONTINUOUS form. So if you already have a main form with your client, and then a subform showing his projects, you can't have another subform inside of that subform.

I would just have a CLIENT form with a listbox showing projects. Double-click on the listbox to open another form showing that specific project. That form could have another listbox showing the managers, etc.

Sound good? You should already know how to do all of this if you've successfully built the previous relationship OK.

 DS on 7/18/2010: Hi Richard,

I am migrating to Windows Vista. I am still going through the series 200 classes. Could you advise if I could still use the 2000-2003 versions. Please advise. tx

Reply from Richard Rost:

Sure. Your version of Windows has little to do with Office. Now, if you're upgrading to ACCESS 2007 or 2010, then you might notice a difference in the interface/menus but the CORE of the program is still the same.

Serafettin Unsal on 11/19/2010: Hi Richard,
Only comment I will make on the ContactT that FirstName and LastName fields should be kept in ContatT table instead of CustomerT table. I believe it would have made more sense. CustomerT keeps the Company, Address, CompanyPhone etc. fields and the ContatT keeps the FirstName, Last Name, PersPhone etc. what do you say?

Reply from Richard Rost:

That is certainly one way to do it. It's really all up to you. That's the beauty of building your own database. I like to build MY personal databases with ONE primary contact on the CustomerT and then have the flexibility to add additional, unlimited contacts for that customer with a second PersonT (I call it PersonT because I already have a ContactT - to me, contacts represent instances of discussion with that customer). But you could go COMPLETELY relational and have NO people listed in the CustomerT (if your customers are primarily companies). Then have a check box on your PersonT to indicate the primary contact. Again, it's completely up to you.

 Orlando on 12/31/2010: Hi Richard,
I am taking your courses and this will probably be covered in future but here goes:
I need for the result of a query (could be in a report) to perform an array calculation such as in MS Excel's formual....=Product(1+(a1:a3)-1 where a1 = the beginning period value and a3 is the ending period value. Will this be possible in access? Basically it is geometrically linking returns and performing investment performance calculations. Thanks. Love your course...Recommend to all!

Reply from Richard Rost:

I'm not exactly sure I understand what you're trying to do, but if you're trying to mimic the PRODUCT function in Excel (which multiplies all of the numbers in a range) then you would have to use something like a Recordset to loop through all of the records in your range and manually multiply their values. If you can give me an example of exactly what you're trying to do, I'll try to help you more.

Orlando on 1/9/2011: Hi Richard,
In response to your request re my query, this is how it looks in Excel (what I need to do in Access...)

The excel array formula is
{=product(1+(B1:B3)-1} and the result = 0.140275

where:

Col A Col B

Row 1) 4/30/2010 0.0035
Row 2) 5/31/2010 0.0236
Row 3) 6/30/2010 0.1101


Thank you!
Orlando


Reply from Richard Rost:

I've tried recreating your sheet using the array function, and I just get an error for that formula. Are you sure you copied it correctly? What are you trying to calculate?

Benjamin Chua on 10/1/2011: I did the exercise creating a ContactF, What happen if a new Contact was entered for the same Company? In the combobox customerID, it did not sorted I have one on the top of the list and one of the bottom of the list.
 Alyson on 3/21/2012: Hi Rick. I am trying to link our "EmployeeT" to our "HumanResourcesT"...It is making the connection between the "EmployeeID" on each table, but when I try to run a query, I get a "Type mismatch in expression" message. Help...

Reply from Richard Rost:

Make sure your FOREIGN KEY (probably the EmployeeID in your HR table) is a NUMBER of type LONG INTEGER.

 Glenda on 4/3/2012: Hi Richard, As I build different tables that I want to be relational, do I have to manually add the customer's primary key in each table? OR is there an easier way to accomplish this task?

Reply from Richard Rost:

You can copy and paste it from one table to the next, but generally, yes, you have to recreate it for each table. All you need is to type "CustomerID" and set it to type Number. That's it. The default type is Long Integer, so you don't have to set that.

Bonnie on 4/14/2012: I have gone over the material on Combo Box a number of times and really see its value, especially for data entry, eg, when adding a new contact. However, if we look at Video 5 @ 2:17 we see ContactID=1, Customer=Pharmacon and Notes starting out “Richard called..” This contact is actually Richard’s in our example and therefore customer 1, Amicron. How do we preserve data integrity when we can scroll the customer combo box and leave it as a different customer, thus inadvertently loosing the link between the true customer and this contact? Thanks for great courses! Bonnie

Reply from Richard Rost:

You bring up a very good point. Yes, at this point you can change the customer by editing the combo box. You can LOCK the combo box (one of its properties) to prevent the user from doing this, OR you could create a SUBFORM for the contacts (which is what I actually prefer). In this case, locking the combo box would provide the quickest solution.

 Tami on 5/19/2012: Hi, I would like to know how to relate a inventory item to a rental customer. ie if a customer wants 50 table cloths on a specific date to be able to see if that product is available for That Date..please
 Malcolm on 7/13/2012: i am using access 2010 and did the beginner class 1-4 there and i am continuing here with the intermediate classes from 201 . I have missed the lessons on creating a the main menu form they show here. Where can I learn how to do that?

Reply from Richard Rost:

That was covered in Access 103, and will be in the next Access 2010 lesson: Beginner 5. Sorry for the jumble. The topics got a little shuffled around. B5 should be out in a day or two.

Finola Bromley on 10/3/2012: Hi Rick. Could you advise me which Lesson I need to buy next to solve my following question:
I have a column of dates (eg date when an inital email was sent to potential clients). I want to send a reminder email 14 days from the date I sent the first one.
In Excel I was able to do this using "=" the cell in the original column, "+14". I can't see how to do this in Access but I'm sure there must be a way. Could you direct me to the best lesson that might illustrate this for me please. Many thanks!

Reply from Richard Rost:

You would do it almost the same way as in Excel. Whole days are equal to 1. So =Date+1 is tomorrow. So in a query make a calculated field and say:

NewDate: OldDate+14

That will now give you a new field (column) of values representing 14 days after OldDate.

I cover this in Access 2010 Beginner 5, Lesson 2.

Finola Bromley on 10/3/2012: Rich, thank you so much, what you have said works perfectly. Could I ask one followup question - is there Conditional Formatting in Access as there is in Excel - I've searched but I can't seem to find it.
Thanks again!

Reply from Richard Rost:

Yes, but not in queries; only in FORMS. But you can design a form to look like your query. Then, go to Design View, click on the form field you want, and then go to FORMAT > CONDITIONAL FORMATTING.

Finola Bromley on 10/4/2012: Dear Rick,
Thank you so much for your reply, I'm very grateful.

I have only just started using Access, so am very much a novice, but I have found your lessons to be so helpful and easy to follow, and they have made me realise that Access has enormous potential, once you know what you are doing.

A lot more learning is in store for me, but I am very much enjoying discovering new thimgs through your lessons.
Kind regards Finola

Reply from Richard Rost:

Thanks!

Robert R M on 10/4/2012: I am working on a job Entry form that is similar to your contact form. I have a CustomerT that has an autonumber customerID field, a customer name field and a division field. I started my JobEntryF as you did with your ContactF in Access 2003 video 4. I brought in my CustomerID field, the CustomerName field and the Division field. I tested the function of these fields and they cycle through my customers as they should with the autonumber, the corresponding customer and division.

I then created a combo box basing the values on my CustomerT and selecting the fields CustomerID (autonumber) first, then CustomerName and finally Division. I sorted the records by CustomerName ascending. I chose "Store that value in this field" and chose CustomerID.

I saved and went to form view, but when I tried to use the combo box I got a bell sound and a message in the status bar that says, "control can't be edited, it's bound to autonumber field 'CustomerID'.

What can I do to correct this? My intention is to have this form as a basis to enter my new jobs in our database. So ultimately this information will identify the customer in my JobsT.

Reply from Richard Rost:

Make sure that the CustomerID in your JOB ENTRY TABLE is NOT an AutoNumber. It should be a NUMBER of type LONG INTEGER. It's the FOREIGN key.

Russell A on 11/9/2012: I am working on a form and would like to use a combo box to auto fill the company name first name and last name and all of the phone numbers. Can this be done only using one combo box?

Reply from Richard Rost:

When you say "all of the phone numbers" do you have multiple phone number fields on the same form, or is this a subform with separate, related records?

Russell A on 11/9/2012: Ofiice phone, cell phone, home phone

Reply from Richard Rost:

This would use an AfterUpdate event with either a DLOOKUP or by pulling all the details out of hidden columns in the combo box, as I cover in Access 302.

Russell A on 11/15/2012: I have watched the video multiple times and reconstructed my form just like yours but I can not get it to fill in. I do not know if I bought course 302 if it would be helpful or not.

Reply from Richard Rost:

Cannot get what to fill in? Can you be more specific? This course is OLD and there haven't been any other complaints about the steps I take in the video... so make SURE you're following everything I do, exactly.

Russell A on 11/15/2012: I am trying to get the FirstName LastName OfficePhone CellPhone & HomePhone to autofill on a form. You suggested that I see a video that you sent. I did and followed instructions but it still will not fill in. Does your work order form that you are selling do that or should I look at Course 302?

Reply from Richard Rost:

I'm pretty sure that I do cover that in the Work Order Seminar, but buying that would be overkill if this is all you need to do. Did you watch the DLOOKUP tutorial? That should explain it.

Russell A on 11/16/2012: Yes I watched it several times. I redid my table and form just like the video and still cannot get it to work. Is there a setting in access that I am overlooking?

Reply from Richard Rost:

Not really. That lesson is pretty straight-forward. Let me see your code (post it here).

Russell A on 11/16/2012: Private Sub CustomerIDcombo_Change()
CustomerID=DLookup(CustomerID, "CustomerT","CustomerID=" & CustomerID)
CompanyName=DLookup(CompanyName, "CustomerT","CustomerID=" & CustomerID)
"FirstName"=DLookup(FirstName, "CustomerT","CustomerID=" & CustomerID)

ect. ect

Reply from Richard Rost:

OK... it looks like your combo box is named [CustomerIDcombo] and you're trying to read the value [CustomerID] in your code. These have to be the SAME THING.

CompanyName=DLookup(CompanyName, "CustomerT","CustomerID=" & CustomerIDcombo)

Russell A on 11/16/2012: made the changes that you recommended still not working.

Reply from Richard Rost:

What's happening? Nothing at all? Error message? Check the names of your other fields?

Russell A on 11/16/2012: nothing at all. no error mess.

Reply from Richard Rost:

You've got me then. Without being able to SEE your database, I can't tell what the problem is. If you want to submit it to me via the TechHelp page, I can try to look at it when I get some time.

Mubeezi M on 11/17/2012: Dear Rusell,

Please place the code this code in the AfterUpdate event of the combobox.
CompanyName=DLookup("CompanyName", "CustomerT","CustomerID=" & CustomerIDcombo)

Take note of the quotes around the CompanyName

Good luck!

MICAH

Vannak Hou on 12/6/2012: where can I download the file for this Inermediate course. I am currently finished Access 2010 course #9. I just don't want to create a table from scratch. I only have the PCResale Customer files only.

Reply from Richard Rost:

ALL of the database files are on the Student Database Page.

Vannak Hou on 12/22/2012: Time Index: 04:45-05:10:... Thanks for the explaination of the Global Relationship. You know how to teach Richard. I was thinking to "jump" right onto the Global Relationship. There are more than just a global relationship after all to understand. So far no book has ever taught that kind of lesson. Your teaching really inspires me a lot in term of the design of a database.

Reply from Richard Rost:

They have their place... but you don't ALWAYS want to use them.

Take CUSTOMERS and ORDERS for example. If you set up a relationship with CASCADE DELETE set to ON, then any time you delete a customer, you delete all of his orders, which would totally mess up your accounting.

However, if you have ORDERS and LINE ITEMS (details) then you would definitely WANT to delete all of those items if the order itself is deleted. So it all depends on context.

vannak on 12/22/2012: Thanks it took me about 4 times or so to understand what the Cascade Delete is all about it. I had to listen and try to comprehend what you're saying. It took me a while but I got it. Thanks.
Basil on 6/14/2015: access student. Pls. notify me. thanks.
Richard Lanoue on 9/27/2015: Here is where I'm lost let's say I have two tables in my query. Employees and Contacts. I can't seem to see the files that include null values in the contacts. I want to see all the records of employees even the ones that have no contact data... how do I do that?

Reply from Alex Hedley:

Have you taken Expert 1?

You'll need to look at your JOIN type, you probably have it as the default one which says where data matches, you'll want to choose the one that says where data exists in your Employees but not necessarily in Contacts.
Double click on the join line and take a look at the options.

Emad on 12/8/2015: I have Access 2013, I have CustomerT One-Many linked to ContactT. I have connect CustomerID_PK to CustomerID_FK, but the combobox it does not work with me?!Kindly, help me to figure out the problem.

Reply from Alex Hedley:

What isn't working?

Emad on 12/9/2015: In the Video you use 2 Combo Box that is not join with relationship. So what about if I want to make Combo Box with Filed that is already relationed with other table. Why I ask that, because If I add student under Representative and in the future I want to change that representative I have to change the number from for example repristivave 1 to 10 to link it with other Representative. so I want want to make it as Combo box

Reply from Alex Hedley:

You can create a combo that is filled with data from another Table and store the choice in your record source of the Form you are entering data on.

Emad on 1/18/2016: What is the difference between creating relation in Quiry and Table? I did not see you created relation with table before how this link appear now?!

Reply from Alex Hedley:

Access is smart enough to link IDs with the same name, it could be 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