ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 2010 Expert Level 2 Tutorial Normalization, Relationships, Referential Integrity, Cascade Delete, Values Across Forms
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
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  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Expert 1

Next: Access Expert 3 >

Access Expert Level 2

Expert Microsoft Access Tutorial - 1 Hour, 25 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 1 left off. This class covers database table normalization, defining explicit relationships, referential integrity, cascade updates and deletes, setting values across different forms, and more You will learn:
 
  - Database Table Normalization
  - Global Relationships
  - Working with the Relationships Window
  - Referential Integrity
  - Preventing Orphaned Records
  - Cascade Update & Delete
  - Table Subdatasheets
  - Setting Values Across Different Forms
  - Forms ! FormName ! FieldName

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access 2010 Expert Level 2
Description: Access 2010 Expert Level 2
Versions: Recorded with Microsoft Access 2010
Works fine with Access 2013 and 2007
Pre-Requisites: Access 2010 Expert Level 1
Running Time: 1 Hour, 25 Minutes
Cost: $19.99


This class picks up where Expert Level 1 left off. In Expert 1 we learned the very basics of setting up relationships between our tables. In this class we'll begin by going into more depth on database design theory. The first topic we'll cover is called normalization, which is making sure your tables are designed correctly. We'll go over the first three "normal forms." Plus, we'll talk about when it's prudent to ignore proper normalization.

 

In Expert 1, the relationships we created were "ad hoc" relationships - just set up in the queries themselves. In this class, we'll learn how to define global relationships on the database level. We'll learn how to work with the Relationships Window and how to add, edit, and delete relationships. We'll learn how to work with table subdatasheets. We'll learn how to set up Referential Integrity to prevent orphaned child records.

 

There are some times when you want to delete all of the child records when a parent record is deleted. If you delete an order, for example, you want all of the line items on that order to also be erased. This is where it's important to know how to set up cascade updates and deletes.

 

We'll create a button on our customer form to display all of the contacts just for that customer. When we go to add a new contact, however, we have to manually select the customer again. It would be nice if we could just get the value of the currently open customer. To do this, we have to learn how to get a value from another open form. Now we no longer have to select the customer from a combo box. We'll also see some additional form layout tricks, like having multiple text boxes bound to the same data field, and moving fields to the form header or footer.

 

This is the second class in the Access Expert series. If you want to take your database skills to the next level, this is the class for you. Understanding proper relational database construction is absolutely vital to the success of your projects. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access 2010 Expert Level 2

00. Intro (7:04)

01. Normalization (21:06)
What is Normalization
First Normal Form (1NF)
Flat Tables
Atomic Fields
Repeating Groups of Data
Entities (Entity)
Second Normal Form (2NF)
Update Anomalies
Third Normal Form (3NF)
Remove Derived Fields
Calculated Values
Denormalization
Improve Performance
Storing History Data

02. Explicit Relationships (14:00)
Global Relationships
Relationships Window
Add Tables
Edit Relationships Window
SubDatasheet
Subtable
Orphaned Child Records
Edit Relationship
Referential Integrity
Unable to Delete Parent
03. Cascade Update & Delete (13:16)
Cascade Updates
Why you should never need them
Cascade Deletes
Automatic Delete of Children
Backup Your Data!
Can't Delete Tables in Relationships
Arrange Tables in Relationship Window
Hide Tables
Delete Relationships
Relationships Report
Object Dependencies
Subdatasheet Table Properties
Subdatasheet Expanded
Subdatasheet Height
Subdatasheet Name
Link Child Fields
Link Master Fields

04. Values Across Forms (23:13)
Find Contacts for Current Customer
Command Button
Open Form
Find Specific Data To Display
Save Form Position
Set Values Between Forms
Forms Collection
Forms!FormName!FieldName
Default Value
Value of a Combo Box
Combo Box Columns
Bigger Notes Field in Form Footer
Move Combo Box to Form Footer
TabStop Property
Brief Intro to Expression Builder
Dirty Records Refresh Problem

05. Review (6:11)

 


 
Keywords: Normalization, Relationships, Referential Integrity, Cascade Delete, Values Across Forms, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, normalization, normalize, flat tables, entities, entity, first normal form, second normal form, third normal form, denormalization, global relationship, relationship window, subdatasheet, subtable, orphan, cascade update, cascade delete, object dependencies, forms collection, save form position, tabstop, dirty record
 
 

Student Interaction: Access 2010 Expert 2

Richard on 3/10/2013:  This is the second Microsoft Access video tutorial in the Expert series. It picks up where Expert Level 1 left off. This class is 1 hour, 25 minutes long and covers database table normalization, defining explicit relationships, referential integrity, cascade updates and deletes, setting values across different forms, and lots more. You will learn: - Database Table Normalization - Global Relationships - Working with the Relationships Window - Referential Integrity - Preventing Orphaned Records - Cascade Update & Delete - Table Subdatasheets - Setting Values Across Different Forms - Forms ! FormName ! FieldName Click here for more information on Access 2010 Expert Level 2, including a course outline, sample videos, and more.
Bruce Reynolds on 3/13/2013: This is PERFECT pre-requisite material that is going to help me IMMENSELY in preparing for the CFPS exam. Thank you!!

Reply from Richard Rost:

Glad you like it.

Natassja Mozart on 3/14/2013: You mention in this video the placement of a button, and that it doesn't matter which section of the form you place it in. Could you explain when this would have an impact on the functionality of the form, be it for a button, or a combobox, etc.?

Reply from Richard Rost:

It generally does not matter whether you place a button in the form header, footer, or detail section. If it's a continuous form, placing it in the detail section will show you multiple copes of that button. It's mostly just for aesthetics. There are some minor differences in functionality, but I'll discuss those in more advanced classes (too much to go into here).

Maurice C on 3/18/2013: I found the table normalization information extremely beneficial. I highly recommend for all Access users0

Reply from Richard Rost:

Thanks.

Richard Rost on 3/20/2013: A student has pointed out that there is a little more to 2NF than I covered in this class. It has to do with COMPOSITE KEY FIELDS, which I really haven't covered yet, but I will soon. We'll talk more about 2NF as it pertains to composite keys in a future lesson.
Robert Maddox on 3/21/2013: Question regarding Input Forms.
Can a "Parent-Form & "Sub-Form" relate to the same table? During data entry I want the "Employee" to remain unchanged, The "Date" would remain constant for a group of data entries (typ. 4-10 entries) then be changed. The other entry items would be on the "Sub-Form". I appreciate your input.


Reply from Richard Rost:

Yes, you should be able to do it. If you have a table with SELF-JOINS then you could have the parent form be "parents" and the child form "children" and both forms are bound to the PeopleT table. You would just need to specify the right join field for the subform, which would be the ParentID field. If all you want to do is set a default value for several records, you might not need to use a subform. You can probably just get away with a continuous form and a field in the form header or footer. See THIS TUTORIAL for details.

C Johnston on 3/25/2013: I went off course a bit and created an 'Actions' sub-form in my 'Contacts' form to show an actions checklist per contact with actions required by whom, date required, completed etc, if there are any. It works fine at the moment. I've also now created the TaskList form and connected Task form following the ACCESS 200 series lessons. For some reason whenever I update the Tasklist form it modifies the form reference in my 'IsInFuture' + 'AreWeHiding' columuns from [TasklistF!] to [ActionF!] and I have to manually change it back. It only changes the reference if I modify the TasklistF in some way. I'm trying to work out what causes the automatic reference update to occur so I can work out why it's happening, rather than deleting and redoing everything.

Reply from Richard Rost:

I've never seen that happen before, but I'll be getting to that lesson very soon in my updates.

Larisa Kiseleva on 3/25/2013: Why if you deleted couple records and adding a new one, the auto number gives the next number include deleted records number? For example if you deleted number 25 and 26. And add the new one record the auto number gives 27 instead of 25.
And one more question if I start brand new database can I initialize my first auto number with any number for example it will start my employee id = 400 instead of 1?


Reply from Richard Rost:

That's just how Autonumbers work. If you insert 1, 2, 3 and then delete 3, the next record is ID 4. Once you even START using an Autonumber (even if you don't save the record) then it's gone. For good. Well... there is a TRICK to get some AN's back if they're at the END of your table, but you don't need to worry about that. You shouldn't CARE what your Autonumbers say. They're ONLY useful for creating relationships between tables. You don't ever need to even see them.

Richard Shafer on 4/13/2013: I understand the purpose of normalization. But what about a situation like in my case. I track surveys with access and each survey can have multiple surveyors and multiple dates that the survey was completed on. I understand that I can pull the surveyors from a surveyor table but what about the multiple dates? Will I have date1 date2 date3 in my survey table? Is thee a way to get around this? And when I make the report to show who completed that survey, how will I show the multiple surveyors if they are contained in a table singlely. Would I just duplicate the TechID.FullName field multiple times on the report? For example, I have survey ID carson123 which was completed in 2013 on work request 123456 by Surveyor Jon Smith on 4/15/13, Alex Jones on 4/16/13 and Josh Anderson on 4/17/13. These surveyors will have other work requests (WR's) from previous years as well as several others from the current year. How do would you build the tables and forms for that situtation? I don't see how to get around making surveyor1 date1, surveyor2 date2, surveyor3 date3 etc.

Reply from Richard Rost:

You would have two tables: one for the survey itself and another for the surveyors and the dates they surveyed. Think of it like a class with multiple students. In your class table you wouldn't have fields Student1, Student2, Student3, etc.

Martin Olson on 4/23/2013: what lesson did you set up the main menu?
Thanks


Reply from Richard Rost:

Access Beginner 7. Whenever you're looking for something like this, use the SEARCH BOX in the top-right corner of the web site. All of the lesson material is indexed in there.

Daisy ONG on 4/30/2013: Dear Richard
Could you please advise me when you shall release the Expert Level for Pivot Chart & Pivot Graph? The main reason i took up your Access class is to learn in dept about those application, as my job required a tremendously usage of Progress Graph & Curve.
Thank you.
daisy

Reply from Richard Rost:

Daisy, I'm not sure exactly. I definitely want to make some new advanced Excel courses, but the demand for Access courses is still two to three times that of Excel based on the results from the Waiting List. There are a lot other projects I need to finish first before I can get back to Excel. You can see my Production Schedule for details.

Patricia Clark on 6/10/2013: I am working on Acces2010 Expert 2.4 at about 23:13. I noticed that you contacts are sorted by time, oldest to newest, but mine are sorted newest to oldest. How do I change the order?

Reply from Richard Rost:

You can just right-click on the form to change the sort. If you want it to be permanent, base the records on a query and change the sort in a query.

Matthew Janzen on 7/11/2013: Hi Richard,

I am working with a continuous form that functions as our main workflow form in our database. From this continous form, I am trying to create a command button to open an contact log of all contacts related to a specific record on the main continuous form.

The problem is, the command button opens the form, but fails to limit the results to only the open record on the main continuous form.

In your tutorial for inserting these command buttons, you mention that we must do this for a single form, but that it should work for a continuous form also. (at time: 2:33 of Access 2010 Expert 2) What could I be missing?

I'm using Access 2007.

Thanks,

Matt J.


Reply from Richard Rost:

It's impossible for me to tell what's wrong without looking at your database, however as long as you have the criteria (where condition) set up in your button to open the form, then it should work fine. I use a continuous form in my Access 2010 lessons to create the customer list form that opens a single customer form.


Brian Merrick on 7/14/2013: Does it matter which way you drag the pointer to join relationship?


Reply from Richard Rost:

It shouldn't. Access is pretty good at determining which side of the relationship is the "ONE" and which is the "MANY," assuming your tables are set up correctly.

D. Forman on 7/17/2013: Love the material. One thing that would make it easier to follow is to label the tables. The screen is very static and if the tables being discussed were labeled (Customer Table, Orders Table, etc.) it would be more clear.

Also, maybe as you're discussing which fields need to be moved you could place a box around them to highlight what you're talking about. That way we know specifically what you suggest should be put in a separate table.


Reply from Richard Rost:

Thanks for the suggestions. Alex highlights a lot of these things in the handbooks, which is why I highly recommend that you follow along with them while you're watching the video. They tend to explain things that aren't perfectly clear.

Brian Merrick on 7/26/2013: Do you cover how to use pivot charts in any of the expert lessons?

Reply from Richard Rost:

Nope. Microsoft removed PivotCharts and PivotTables from Access 2013. I cover them extensively in EXCEL Expert 7, but I won't be covering them for Access.

If ENOUGH people want to see how to AUTOMATE Excel from Access, I could cover that in a Developer-level lesson. It will require extensive VBA.

Deon R on 7/27/2013: Hi Richard,

I would gladly pay for a developer lesson or better still, a complete seminar on AUTOMATATING Excel from Access for charts, pivots and dashboards. I'm sure there are stacks of other students out there who would like to see this as well.

Cheers for now,

Deon

Reply from Richard Rost:

OK, let's see. I'll add it to the WAITING LIST.

I've had "Automating Outlook from Access" on there for a few YEARS now because people have asked me how to use Access to setup/maintain email, calendar, and tasks in Outlook, but so far that seminar has a grand total of 3 votes.

It's a very time-consuming endeavor to set up a seminar like this. I'd love to do it... but not if people aren't interested. I have HUNDREDS of people asking for "regular" Access classes, so that has to take priority for now... until I run out of topics. :)

Val Foster on 7/31/2013: Richard, I have a database that has 7 tables. All the tables have fields in a main form - some fields are required, most are not. When a user starts a new record, I am finding that unless they touch each table (even by inserting a "0" which is the default value) the children tables do not record the record. Therefore the record does not show up on the form next time it is opened. My relationships are one-to-one with the ID of a main table related to the ID of all the other tables.

Reply from Richard Rost:

It's VERY tricky when you try to get data from multiple tables in the SAME FORM. I strongly recommend using SUBFORMS, which are covered in the next class (Expert 3).

Val on 7/31/2013: I am currently using several tab controls for a fairly complicated form. I don't think the subform will work. I originally had all the data in one table until I found out that was not advised. But now - with several tables the form isn't working. I might need some dedicated help with this.

Reply from Richard Rost:

Well, I'm sure we can help you. Submit your details via our TechHelp form and one of my guys can take a look at it for you. I'm curious as to WHY you broke the data up into 7 different tables.

Val on 7/31/2013: I broke the table into 7 due to everything I heard about database integrity - and then I was also running into volume issues. I'll go through your tech help. Thanks.
Joni Moore on 9/18/2013: What if the "Referential Integrity" box is not available to check (it's greyed out)? What would cause that?

Reply from Richard Rost:

You may have data in your tables that already violate referential integrity, but I'd need to see your database to tell you for sure.

Jacob Swinney on 10/30/2013: Hey Richard,
I am still having problems with my Customer form and my contact form working properly with buttons. When I click on the button to show the notes from the customer selected in the list i am directed to the first contact in the contact form

Alex Hedley on 11/3/2013: Hi Jacob,

Have you tried adding a new button to see if that works instead?
I'd check the fields are matching.

Alex

Frans H on 1/24/2014: Hello Richard,
I like the course so far, but in Expert 2 I can not use the default value settings. In 2013 this is different. Can you show me how I can edit this item. Thanks, Frans H

Frans Hammersma on 1/24/2014: Hello Richard, I am using Access 2013. On section 4, Time 06:32 The data items are not there. Please advie how to ad the settings using Access 2013. Thanks, Frans
Patrick Verbist on 3/1/2014: In one of the lessons in Expert level 2 you mention that you advise against using socialsecurity nr as a primary keyfield. Although I understand the advantages of an autonumber, I don't see the disadvantages of using a key such as social securtiy number which is also uniquely identifying an individual (apart from privacy reasons, or database space (social security number is larger than autonumber). Related to this I have another question: suppose you backup your files by exporting them to excel. In case you loose your table and want to re-import it back from Excel, I'm assuming the autonumbers will be different. If then that key is used as a foreign key in another table, it will point to the wrong record. Does that mean backing up in Excel is a bad idea, or is this an argument pro using more 'meaningful' keys?
MICHAEL J on 5/2/2014: Hi Richard
You mentioned in your lesson how to position a form in the window and save it there for the next time you open the form. How can you do it for Reports.

MICHAEL J on 5/19/2014: Hi Richard I have just completed Expert 2 and have a question. On the Contact form you showed us how to use a combo box for the Customer Name and set the default to pick up the name from the CustomerT. This is a great way to do it but is it necessary to keep it as a combo box or could you use a text box.

Reply from Richard Rost:

Remember, the data is bound to the ID, not the name. The combo box is actually linked to the customer ID. It just displays the name.

MICHAEL J on 5/20/2014: Hi Richard
I have just completed Expert 2 and have a scenario I,ve been trying to work out. You may have one customer with many contacts. On the contact form we have a Combo box to select the the contact name, however you can not enter a new name. The only way I can think of is to create a new form for the new contact which would mean having to duplicate data. Any suggestions.

Mark E on 6/20/2014: Hello Richard - Firstly congratulations on your Microsoft MVP award - very impressive. I was wondering if you might elaborate on "VERY tricky". I have also had difficulty displaying data from more than one table on a form with the form failing to display the data then mysteriously reappearing again without (I think) any design changes being made.

Reply from Richard Rost:

Thank you. I'd really need to learn a lot more about your database, and what you're trying to do, before I could give you advice on how to set up your tables.

Lynda Chase on 7/31/2014: Hi Richard, I'm at this stage now in my Access Learning - would I have enough knowledge at this point to enrol in the WorkOrder Seminar, or would you suggest I finish the expert series before ordering.

Reply from Richard Rost:

If you've finished the Beginner series and at least the first 3 to 5 Expert classes, you could go ahead with the Work Order Seminar. There's some VBA coding in it, but I don't assume you know any VBA to start.

Raymond Kimber on 8/3/2014: Richard, I have a question about using Forms]![CustomerF]![ID] to set the default value of a combobox, as in your example (Access 2010 expert2, lesson 4, 9:40). If I wanted a button in two different forms (CustomerListF and ContractExpirationF) to open the ContactF, would I use = ([Forms]![CustomerF]![ID] Or [Forms]![ContractExpirationF]![ID]) in the Default Value property of the combobox in the ContactF?

Thanks,

Ray Kimber

Reply from Richard Rost:

If you do it this way, you'll have to decide which form you want to supply the default value. It can't be both.

Later on, when we get into programming, I'll show you how you can use VBA in the button that opens the form to supply the default value.

Tiziano Fortin on 8/17/2014: Very clear and useful lesson.
I would placed it before the lesson on "Relational Queries". Regards. FT


Reply from Richard Rost:

That's always a toss-up. I like covering ad hoc relationships first because I feel it gives a better appreciation for WHY you would then want to declare explicit relationships.

Tiziano Fortin on 8/23/2014: I'm happy to follow your course on Access. I want to include it in my life long learning program. I feel this experience as I felt my university times. Thanks. Regards. FT

Reply from Richard Rost:

Thanks. I'm glad you're enjoying the lessons.

Deybis Trejos on 8/26/2014: dear Richard i neet to ralate more than 2 tables which class explains that in this one you only did 2 tables i neet to ralate more than 5

Reply from Richard Rost:

The concept is exactly the same regardless of how many tables you have to relate.

Sonya B on 9/2/2014: I need to do this as well. When I relate more than 2 tables. I get the message "This Record Set is not updatable". I found some information referencing this on the Web, but I would like to here a response for the class/Richard.

Reply from Richard Rost:

This all depends on your query. You could easily have 10 tables in your query and still have an updateable recordset; or you could have just 2 and because of the nature of the relationships, it might not be. I'd have to see the database to tell you.

Erika Jacobsson on 9/4/2014: Hi Richard!
I am building database with 2 tables, so far. I have earlier defined explicit relations between the CustomerID-fields in both these. Now when i look there is suddenly a third table in "relations"; a copy of the main table but with the addition of "-1" in the name (tablename_1)and with the same relation marked. There is no extra table showing anywhere else but in "relations" and everything seems to work as it should but I am a bit worried. Could it have anything to do with the fact that i canged the foriegn key in my second table from a plain number into a combo box referring to the primary key in the main table, after already having constructed several questions and forms? Do you have any idea why thsi third table is showing and how can I fix this?

Ramona Woitas on 10/26/2014: Hi Richard! In Access 2010 Expert 2, at the end of Lesson 4, you mention that you will show us in a later lesson how to refresh so that you will not have a dirty record. What class and lesson was that in?

Reply from Alexander Hedley:

Hi Ramona, If you ever need to check which lesson contains something either use the Search form, REFRESH or check out the Access Index page and a Ctrl+F will help.
Expert 10 might be the first occurrence.

Shaun Marple on 10/28/2014: Hello Richard. Is there any quick way to normalize a flat file with large amounts of data? I am looking to split my flat file database into 8 tables, each with one-to-many relationships. If I just use what we have learned so far, I'm afraid it'll take much too long converting everything manually. I tried letting Access' wizard do it when I imported from Excel, but it totally butchered my data!
Thanks in advance.

Reply from Richard Rost:

Shaun, in Access Expert 22 I cover some techniques for fixing flat-file data that you've imported from another source.

Jeff Perkins on 11/19/2014: I know this is a late answer for your question, but since no one has answered it, I will go ahead.

What you are describing happens when you add a table(or query) to the relationship window that is already present in the window. So, if you add a CustomerT to the window when CustomerT is already there, Access marks it with the _1 suffix to tell the difference. I don't know why you'd want the same table on there twice, but there it is.

Jeff Perkins on 11/19/2014: Oh, and the fix for it is to just right click on the "_1" table and then click on "Hide Table".
Dale Ferg on 1/1/2015: Is there an expression that would enable me to calculate a person's current age?

Reply from Alex Hedley:

See this Forum Post

Vannak Hou on 1/17/2015: When I first updated to the Referential Integrity, it actually DELETED the selected Record of that selected. After having had played around for a few minutes, I decided to "exit" Access 2013, then go back and retry deleting another record and it works. For acknowledgement.
Vannak Hou on 1/19/2015: The "Referential Integrity" is greyed out using your downloaded file Access-2010-X1.zip once I created the join "0217-02:50).

Any, I had to delete the new Record and play a few time and it finally works. Had to close the Access file and going back in again. I'm just not sure of what happened? Just wanted to let you know about Joni Moore's comment (top of the page) is true though.

This is the same thing that Joni stated on the first topic of this video:

"Joni Moore on 9/18/2013: What if the "Referential Integrity" box is not available to check (it's greyed out)? What would cause that?"

Michael Larsen on 3/1/2015: Hi Richard
In the Contacts Form which we just made continuous, the "More Notes" that I pasted in the Form footer appears all the way at the bottom of the form.
I have the same problem with the "Open selected customer"-button on my Customer List Form.
Can I move these objects up a bit?
I'm using Access 2013

Reply from Alex Hedley:

Which do you have in Options, Overlapping Windows or Tabbed Documents.
Have you dragged your detail section to below the last element on your form?

Michael Larsen on 3/7/2015: Hi Alex

I was using Tabbed Documents.
I see now that when I change the setting to Overlapping Windows and restart my database I'm able to shrink the form so that the object in the form footer is closer to the objects in the detail section.
Thanks

Reply from Alex Hedley:

Great Stuff

consuelo Evans on 3/8/2015: When I try to put a command button into my customer form to open the contact form and the command button wizard opens, the section where I am suppose to match the fields on the "CustomerF" doesn't contain any fields at all. What am I doing wrong?

Reply from Alex Hedley:

Is your RecordSource a SQL statement instead of a Table or pre-made Query?

Do the Foreign Key Fields match, i.e. CustomerID in CustomerT and CustomerID in ContactT?

Or try the usuals, Save/Close/Reopen the Form.
Save/Close and Open Access
Backup then Compact/Repair.

consuelo E on 3/14/2015: I have tried everything you mentioned and I even took the classes al over again and I still have the same problem.

Reply from Alex Hedley:

Can you try it with a blank new database.
Another option is repair Office or reinstall.

Mohammed Ghulam on 4/5/2015: Hi richard,

I just have one small question, I am using access 2013, and when I open any Form, there is only exit button, and I do not have the minimize and Maximize buttons in the Form... when I open the property sheets for the forms, I see the min/max selection, and I enabled both of them and I still dont have it? could you tell me how to add them in the forms

thanks

Reply from Alex Hedley:

Do you have it setup to be Overlapping Windows or Tabbed Documents?

Mohammed Ghulam on 4/12/2015: Hi ALex,

thank you so Much, that helped :)

Reply from Alex Hedley:

Happy to help :)

BRIAN SHULTS on 4/14/2015: No Groups: I have many Part records that have varying number of operations for each record. How do I make a form that only includes the number of operations needed?

Reply from Alex Hedley:

I'm struggling to picture what you want.
Do you have an example of the data and what you want to get from that list.

BRIAN S on 4/14/2015: OP1 Drill
OP2 Weld
OP3 Sandblast
OP4 Paint

Operations are in drop down box.

Reply from Alex Hedley:

So you want to filter the records based on the Operations.

You can use a WHERE clause based a parameter of the Drop Down. You can use a Form Field Criteria.


Fernando Romero Galvan on 4/16/2015: Wow, I was just about to ask, "What if I was so lazy I didn't want to click a button"
and then you started talking about subforms.

Well Played Richard... Well played.

Loving the series to far!

Reply from Alex Hedley:

Aren't we all sometimes :p

Brian Merrick on 4/20/2015: Does it matter table which table you link to. Example. customer table and contact table. If i want to link them together does it matter which second id key the table is in?

Reply from Alex Hedley:

Say you have an AutoNumber field in CustomerT called CustomerID you would need to add a Number Field in ContactT called CustomerID and put the number from one into the other then JOIN it on that.

vince Hughes on 4/24/2015: I have been trying to get a Default value for customerID in my data base have went through video 4 times and do not think I'm missing a step. My problem is I have set the default value in properties to: Forms![ContactF]![CustomerID]and it does change to the right person in data base when I click on button in customerF but the new record does not have the current person in new field like it shows in your video. Thanks

Reply from Alex Hedley:

Add a new unbound textbook to the form and set the Default value to the =[Forms]![ContactF]![CustomerID]
Add a new record and does it match the CustomerID you currently have Open?

Would you not want it to be
[Forms]![CustomerF]![CustomerID]
So you are taking the CustomerID from the Current Customer that is open.

Juan Gamarra on 6/16/2015: I'm having the hardest time making my forms to work properly, I made a one to many relation between a table of costumer and their respective employees, one table for the name of the company | adress | companys info and the other for the employees (department, phone, celular...) but I can t seem to find my way to build the form, going nuts.

Reply from Alex Hedley:

What issues are you having?

Are you creating a Blank new Form or are you highlighting the Table then clicking Form so it builds one for you?

Brad Weekley on 8/10/2015: Hi Richard. I am so pleased with your courses!! I've been fumbling around with Access for a 3 years trying to make things work, reading the Step by Step and reading forums but, your courses are providing some of the missing links.
I do have a question about Normalizing tables. You show creating a seperate table for a single field that is duplicated in the existing table. Is there really any benefit to creating a seperate table (which becomes a child) if there is only one field? I'm not understanding that.
Brad

Reply from Alex Hedley:

Do you have the example given?

Security is one I can think of, say having CreditInfo in a separate Table which you then link to Customer and only let certain people see that info.

Brad Weekley on 8/10/2015: I guess I was thinking of the LeadSource and Shipping child tables that were built. They only have one field.
How does this help?

Reply from Alex Hedley:

It just keeps it separate and easier to update as you only have one place to do it if you add more.

NormaK on 9/3/2015: Which table should receive the foreign key? In the example from Alex above, could you just as easily have gone the other way and put the ContactID from the ContactT into the CustomerT - does it matter which way you put the foreign key?

Reply from Alex Hedley:

It depends on your relationship.
Is it one to one, one to many, many to many? See the Relationship Seminar for more info.
If you are going to have Many Contacts per Customer and you stored the ConactID against the Customer that would only allow one.

Alejandro Rivas on 9/9/2015: I have a data entry parent form/subform. I want my users to be able to add a new employee, & with a combobox in the parent form, be able to select the workplace. With the selection, I want that value to populate the same field in the subform so that the user doesn't have to pick it every time a new record is created. Will this video help me with this question?

Reply from Alex Hedley:

This Tip should help.

Barbara Evans on 9/18/2015: Observation: I agree that the auto number field is the best primary key for a table but I'm not sure that it is always the best ID field. If a person/thing is deleted that should not have been and they are reentered their id changes. Their customer id would no longer be the same as the id given to the customer. This is also a good reason to never delete. I have tables with a unique primary key and a unique customer id.

Reply from Alex Hedley:

You could add a boolean flag that says IsActive and just swap that, so if you ever need to bring the Customer back you can flip it again.

Joe Beniacar on 9/26/2015: Hi Richard, for Richard Shafer's question in this lesson, would it be a Many-to-Many relationship between surveyors and surveys? So, could you have a surveyor table, a survey table, and then a junction table that had the dates? Thank you for clarifying that!

Reply from Alex Hedley:

Yes

Joe Beniacar on 10/7/2015: Hi Richard, if you are going to enter data into your Tables, is it better to do so in a ONE Table's subdatasheet than in the MANY Table (ContactT)? That way, the correct CustomerID will automatically be entered, without you having to compare it to the CustomerT? Is it the same concept with Subforms (Expert 3) over the MANY Form? Thank you!

Reply from Alex Hedley:

You should really use a Form to enter the data, then that way you can control it better.
I think the Table data entry is just shown so you have some sample data to work with.

Geoff Speicher on 12/3/2015: I work for a hospital company and am in the process of designing database that will track our patient volumes (number of admissions, how long the patient stayed, along with other metrics). We have 10 hospitals and each of those hospitals has multiples lines of business, both inpatient and outpatient. I won't be tracking patient names or anything like that, just volumes (numbers). What would be the best way to set this up?

Reply from Alex Hedley:

You could create a Hospital Table to store each Hospital
You could then have a Table that stores the Patients.
You could have a combo with a choice of Inpatient/Outpatient
Then queries to count them up.

vicki Hudson on 1/19/2016: I need to track multiple horses that get trimmed multiple times a year by multiple people. I need to see at a glance what horse is due to be trimmed each month. Then print a report to give to barn mgr. What is going to be the best way to set this up? i am having trouble normalizing the TrimT with the HorseT. In my form it repeats the date in every TrimDate field (9 of them) when i enter a date. I need to see every date in a column like an excel sheet to determine which horse gets done first. not sure if this makes sense

Reply from Alex Hedley:

You can use a CrossTab Query to make a table like excel

I'd have a Horse Table, a Trimmed Table, you could then have a Foreign Key from Trimmed to Horse, you could have a Field of yes/no to say when it has been completed. Then you could add each date they're due to be and mark when complete
I take it the Horse would be linked to a Manager, that could be another relationship

Carolina Cervantes on 2/10/2016: In which lesson do we learn how to line up customer id's to relate to other table without entering the ID for each record in the other tables?

I basically have a table of "customers/clients" I want to create a few other tables of "clinic admits" and another of "client referrals" and I want them to all relate to the Custmer/Client ID in table one. I would like to import that data, but do I need to enter the Customer ID/Foreign ID for each record in each table or is there an easier way?

Tahnks..

Reply from Alex Hedley:

Do you have any matching data in both the client table and the table you wish to join too?

Zacharias Martinez on 2/24/2016: ON Expert 2, values across forms section. There is a portion where you go into dropping a command button, form operations, open form. In that section a portion of the wizard comes up which states "specify which data to display"...this does not show up in my wizard when I make this button. How do I get this function back into my access 2010 program, I need this function but I am stuck without it. Please advise.
Thank you,
Zacharias

Reply from Alex Hedley:

Did you set the record source of the form as a sql statement instead of a table/query

Elizabeth Roche on 3/11/2016: Right now if my user changes the customer combo box...he modifies who the comment is about...This is probably a programmer party foul. I'm hoping you'll show how to disable that ability (in a future class?) Thanks!! Love this tutorial series. Your explanation of Normalization and Ref. integrity was excellent!!

Reply from Alex Hedley:

You could set the locked property to YES

Elizabeth R on 3/11/2016: Ok. Thanks! I'll give it a try. (I did think that might work, but wondered if I missed something in the lecture.)
Virginia Mergl on 4/9/2016: Hi, on my database I have a customer table and a pet table.
One customer can have more than one pet
One to many relationship. I did set a referential integrity so on my database I can not have a pet without a customer.
But what I actually would like to know is how can I avoid duplicate customers
for example I can have many surnames SMITH and I can have many postcodes TW14 8EE, however I can have only ONE customer SMITH in TW14 8EE, how can I setup so the system does not accept another SMITH in TW14 8EE. Thank you

Reply from Alex Hedley:

There could be two Smiths in the same postcode so it might be worth thinking about your requirements.
Expert 23 explains about stopping duplicates.

 

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