ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access Expert Level 4 Tutorial Form Controls, One-to-One Relationships
 

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 3

Next: Access Expert 5 >

Access Expert Level 4

Expert Microsoft Access Tutorial - 1 Hour, 48 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 3 left off. This class focuses primarily on new types of form controls including option groups, toggle buttons, the image control, and more. We'll also learn about one-to-one relationships, and practice more with one-to-many relationships. You will learn:
 
  - Additional Form Controls
  - Option Groups, Tab Control
  - Toggle Buttons, Enhanced Formatting
  - List Box, Image Control
  - Display Image Without Storing
  - One-to-One Relationships
  - Extended Customer Details
  - More One-to-Many Relationships
  - Track multiple family members

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 Expert Level 4
Description: Access Expert Level 4
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 3
Running Time: 1 Hour, 48 Minutes
Cost: $19.99


This class picks up where Expert Level 3 left off. We will begin by setting up a table to track additional customer information that we don't want to keep in our primary customer table. You'll learn why in class. You will learn how to force a one-to-one relationship so that each customer can have one and only one related record in the extended customer table. We'll also set up some additional tables to track multiple family members per customer.

 

In Lesson 2 we will build the extended customer form and learn a bunch of new form controls. We'll build an option group, a list box, and some toggle buttons. We'll learn some advanced formatting techniques and how to make the toggle buttons change color when they're pressed or you hover the mouse over them.

 

In Lesson 3 we will continue work on our extended customer form. You will learn about the tab controls, and you will learn how to display images in your forms and reports without actually saving that image in your database. You'll learn why this is important to keep your database running efficiently.

 

Next we will build a form to track the additional family members per customer. We will embed that as a subform inside of the extended customer form. We'll also create a combo box to pick the relationship to the customer, and a helper form so we can edit/add those on the fly.

 

Finally, we'll take a walk through all of the tools in the form design toolbox. I'll go over the ones we've covered so far, explain which ones you'll probably never use, and give you a brief overview of the controls we're going to cover in future upcoming lessons.

 

This is the fourth 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 how to use a variety of different form controls and relationships is important 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 Expert Level 4

00. Intro (7:37)

01. Extended Info Tables (19:26)
Create CustomerExtendedT
ShortText and LongText Fields in 2013
Yes/No NULL Value
Password Input Mask
Family Member Table
Relative Type Table
Force One-to-One Relationship
Indexed No Duplicates

02. Extended Customer Form 1 (23:55)
Create Extended Customer Form
Option Group
Yes as -1, No as 0
Unspecified option
Change y/n to number
Create List Box from Scratch
Create Toggle Buttons
Manipulate Toggle Colors
Lighter Darker 50%
Hover Color
Pressed Color

03. Extended Customer Form 2 (26:05)
Command Button Open Ext Form
Default Value
Forms!CustomerF!CustomerID
Locked Property
Change Form Properties
Modal, Popup
Navigation Buttons
Record Selectors
Caption & Trick
Tab Controls
Image Control
Save Path Filename in Table
Set Image Control Source
Customer With Image Query
Display Image on Report
Customer Labels with Image
04. Family Member Form (14:15)
Description Indexed No Duplicates
Relative Type Helper Form
SQL Recordsource
Family Member Form
Relative Type Combo Box
Link Master Fields
Link Child Fields
List Items Edit Form

05. Toolbox Control Overview (11:20)
Hyperlink Control
Label with Hyperlink Address
Show File Extensions
Brief Discussion Of...
Web Browser Control
Page Break Control
Chart Control
Line & Rectangle Controls
Attachment Control
ActiveX Controls

06. Review (5:12)

 


 
Keywords: Form Controls, One-to-One Relationships, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, short text, long text, password, input mask, indexed, no duplicates, option group, list box, listbox, hover, pressed, locked, modal, popup, image control, recordsource, sql, link child fields, link master fields, hyperlink control
 
 

Student Interaction: Microsoft Access Expert 4

Richard on 4/29/2013:  This is the fourth Microsoft Access video tutorial in the Expert series. It picks up where Expert Level 3 left off. This class is 1 hour, 48 minutes long and focuses primarily on new types of form controls including option groups, toggle buttons, the image control, and more. We'll also learn about one-to-one relationships, and practice more with one-to-many relationships. You will learn: - Additional Form Controls - Option Groups, Tab Control - Toggle Buttons, Enhanced Formatting - List Box, Image Control - Display Image Without Storing - One-to-One Relationships - Extended Customer Details - More One-to-Many Relationships - Track multiple family members This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. Click here for more information on Access Expert Level 4, including a course outline, sample videos, and more.
Bruce Reynolds on 5/2/2013: I have Microsoft Office 2013 Home Premium, 365 version. Is this the version of Access you are using in your videos? If not, are there any features in in the full version of Access 2013 that do not exist in the Office 365 version?

Thank you.

Reply from Richard Rost:

They're exactly the same. Office 365 is just Microsoft's fancy way of labeling it as a "subscription service." Access is Access. You get the same program.

Brauna Rosen on 5/10/2013: Hi Richard,
To be able to insert a path, rather than the image itself, do you have to go back to the table and change the field property from OLE object to attchment?
Brauna
Thanks

Reply from Richard Rost:

Yes. You don't want an OLE object. You just want a TEXT field (generally Short Text) to store the path and filename. Then display that on your forms and reports with an Image control (not an OLE Object Frame).

Bruce Reynolds on 5/14/2013: I know it's not specifically relevant to this particular class, but did Microsoft improve the graph object from Access 2010 to Access 2013?

Reply from Richard Rost:

I did not see any notes about this on Microsoft's list of improvements, but they made a lot of little tweaks that they didn't advertise... so it's possible. I really haven't played with it much yet in 2013. I'll be getting to it soon.

Bruce Reynolds on 5/17/2013: I am assuming that you may have had clients that you consulted with that had perhaps too many tab controls, and you had to spend a lot of time undoing the mess that they created with these tab controls, especially when they got to the point to where they wanted a certain degree of automation.

Reply from Richard Rost:

Yes, I have definitely had this before. People like to go crazy with tab controls.

Richard Rost on 5/26/2013: One of my students, Leo, sent me an email saying that his option buttons were missing the OPTION VALUE property after creating a new one. See this tip video for an explanation: OPTION BUTTON MISSING OPTION VALUE PROPERTY.

Thanks to Leo for bringing this to my attention.

Maggie on 6/10/2013: I have a question on combo box to select multi value in one field. For example, a book classified to more than one category (ie information, nutrient). Then I need to make a search box base on the inputs to find all the books under one specific category (ie information). Would appreciate to show me how to make it.

Reply from Richard Rost:

You can't select multiple items in a COMBO box. You would need to use a LIST box. Saving that information in your table isn't terribly difficult, but PARSING that data in VBA to create search criteria is. I cover it in my Access 321 class.

Wayne Ayotte on 8/7/2013: In expert 4 lesson 2 you go though color changes on the toggle buttons, but I'm useing access 2010 how do I go about it in 2010? I'm guessing by going to properties and "On Key Press" put in an expression. But what would that expression be? Time Index 17:22

Reply from Richard Rost:

I don't still have 2010 installed, so I can't check, but I don't believe that 2010 supports the color changes that 2013 does. It's a minor feature. In older versions of Access, you have to use a little VBA in the AfterUpdate event. I will cover this in the advanced lessons when we get to macros and programming. In 2003 and earlier, you can't change the background color of the button - only the text color. That's done with:

ButtonName.FontColor = 0

That will change the color to black, for example.

Joni Moore on 8/8/2013: Are you able to use a button to "Apply Form Filter" on an option group? Using your scenario, if you wanted the user to be able to click a button to see all the "female" records.

Reply from Richard Rost:

I know you can right-click on an option group and apply a filter.

Laurie on 8/20/2013: At 10:10 mark, you show Insert OLE. Then on 10:15 mark, you show Short Text. Are you showing 2 different ways of displaying a picture in the same lesson?

Reply from Richard Rost:

Looks like there's actually a glitch in the video there. I probably recorded the part about "OLE Object" and then I remembered that I wanted to show you the Text method (because we covered OLE Objects in the Beginner lessons). I probably FORGOT to delete that segment and then continued recording. So leave it as Short Text in your table. Oops.

Beth Mettle on 9/9/2013: In Access 2013, I have a main form with several subforms (set as Single Form) and it seems the style of toggle buttons does not transfer to subforms. They look correct in Design View, but in View, the buttons convert back to the older gray, square cornered look. I have tried various ways of creating the toggle buttons, short of programming, with the same results. Any way to work around this?? Or am I missing something?

Reply from Richard Rost:

What version of Access are you using? In versions before 2013 they were unreliable.

Beth Mettle on 9/16/2013: As stated, I'm using Access 2013.

Reply from Richard Rost:

Sorry, didn't catch that. That's what I get for responding to posts at 5am. I haven't noticed that behavior. In fact, I just tested it myself and the buttons kept their theme settings even in a subform. Perhaps make sure you have the latest version of Access. Some of those original releases had lots of bugs. I used the "preview" version of Office 2013 for a few months after it was released and there were bugs GALORE.

Beth Mettle on 9/18/2013: After further research, I found the toggle button style being inconsistent, or 'unreliable' as you have stated. When the application first runs, the toggle buttons that are located in subforms display correctly (rounded, two colors, etc). If I switch to Design View and then switch back to Form View, the toggles buttons revert back to the old style (squared, no color, etc). So in the end, the toggle buttons will display correctly each time the user runs the application. And that is fine with me.

Thank you

Reply from Richard Rost:

Sounds good. Like I said, I haven't noticed this behavior, but what you're saying makes sense.

John Miller on 10/29/2013: Hello, love the lessons. Have a problem with displaying the picture in video 3 I was running Access 2010 and could not get it to work so I uninstalled 2010 and installed 2013 with the same results. Watched the video six or seven time and followed along step by step but still would not work. Time Index Video 3 18:30 to 20:00.
John Miller on 10/29/2013: I apologize for the post about the image not showing up. I finally figured out what I did wrong. I have been running Access on 2 different machines one at work one at work I would save the DB I was working onto a jump drive to use between the 2 locations forget to allow for different paths to pics. Sorry I wasted your time on this silly mistake
cheryline elliott on 12/5/2013: I tried to use the check box field when updating a table, but every time I press it it updates the table. Is it a yes or no field. When remove the check mark, it shouldn't update the table, correct?

Sorry I don't total understand the check mark field

Reply from Richard Rost:

Check marks are the default object that Access links to Y/N fields when you put them on a form. Generally they're linked to the table field, yes, unless you create an unbound check box.

Christine White on 1/3/2014: If you copy a combo box from another form and apply the lock and default value, will that affect the other form? Thanks!
Christine White on 1/3/2014: My default value doesn't seem to be working on new records. The extended form comes up blank. Since the Employee ID field is locked, I had to go to the table, add the ID # and then the default name would show. Thanks for your help!
Christine White on 1/3/2014: Figured out both my questions. Thanks
Robert Maddox on 1/5/2014: Why write the SQL order statement in the relation form if in setting up the subform it allows you to define the order for the subform? (I like it that you do introduce use to SQL )
Miric on 1/11/2014: Hi Rick

I followed the rules you suggested to insert the image using the Picture text field but the picture is not appearing in the form. Do you have any suggestion why is this happening?

In addition to this, I would like to use the picture in my junction table which is a connection between Customers and Addresses tables. Would this be a reason?

Thanks in advance
Miric

vicki Hudson on 2/1/2014: If I have multiple horses (72 actually) that have 9 different rides and dates, 5 different distances, how can I display those multiple named rides on the same form connecting to one horse. If I change one ride name it changes all the ride names and ditto with the distances. I'm stumped
Sonia Ferreira on 3/3/2014: I'm having a problem with subforms. I've gone back to Expert 3 and tried again from scratch and I still don't get the Master and Child Links in the Properties Sheet. Whether I use the Subform from the Tool Box or just drag my subform onto my form, when I click on Property Sheet, Data shows the source and below it "Dynaset". Sorry, I'm also having trouble with field names because I'm using a Portuguese version of Access 2013. On the other hand, the subforms are working correctly, so...could this be a change from Access 2010 to Access 2013?
John Bell on 3/22/2014: Why do you store the Relative ID in the table instead of the Relative name? I have watched your videos on Combo boxes. You stored the state names in the customer table and not an ID. I understand there are only 50 states, but there are also only so many relative names as well, so I was thinking that cannot be the reason you use the ID instead of the actual name.

Reply from Richard Rost:

As a rule of thumb, it is better to store IDs in a table as a foreign key instead of names. Why? That could take me 20 minutes to explain, and I'm pretty sure I covered it thoroughly in Expert 1.

Kerra Cameron on 6/3/2014: Rick -

When you are in the design view or any table or query and entering the parameters is there a way to enlarge the text?

Reply from Richard Rost:

Nope. There is no ZOOM in design view. I know... that sucks.

Jonathan M on 6/9/2014: At 18:18 you say you're going to enforce referential integrity so that you can't delete a customer if there is extended data. Then you say you will cascade delete so that if you delete the customer you will delete the extended data. Is this a mistake or is there a distinction that I'm missing?
David on 6/28/2014: You mention around the 20:30 mark that there are automatic ways for resizing. I understand that it's often better to do it manually but what ways are you referring to?

Reply from Richard Rost:

It's been a while, but if memory serves I was referring to the fact that you CAN resize controls like text boxes when the user resizes the form, but that requires VBA coding.

Robert Taylor on 7/2/2014: Rick,

At about 14 min on Expert 4 Lesson 3 you mentioned that you can get a lot of fields on a form especially if you have a big monitor.

When building a project for a customer, if my monitor is larger/smaller, does the program adjust the size of the screen form so it fills the customer screen size?

Great Lessons,

Bob

Reply from Richard Rost:

No, unfortunately it does not. That's one of the problems I have with Access is that forms don't automatically zoom in/out based on monitor resolution. You need to build your forms for the smallest screen size you think will be possible, and that's one of the reasons I prefer Overlapping Windows over Tabbed Forms.

Chris Thompson on 7/29/2014: Access Expert 4 Lesson 4 TI 3:44 - Perhaps I have learned this tip through your classes Rick.

When I have a large list of information I need listed in a combo box, I sometimes add an additional Number field in the helper table called "SortBy".

I can then create combo boxes are sorted based on the value of this hidden field. We can then assign and control the sort that will bring the more popular entries to the top of the list within the combo box. Simple editing using the helper form for this table can then change how the info is presented in the combo boxes.

For example, if I have 25 entries and I use 10 of them 50% of the time, I would assign a number in the SortBy field for each entry so that these 10 entries I want at the top have values of say 10, 20, 30, 40, ..., 100. The balance of the entries would have some values that are higher arbitrary values - still allowing the list to be in the order as I would like to see it.

I use non-sequential numbers in the field so that later I can squeeze an entry in between those already in the list. That is, if I want to add / change an entry that I now want to be third most popular, I would enter a number between 20 and 30 (most likely 25) assigned to the entry.

Then when we open up a form with a dialog box using this helper table, the end user would then see the new entry in the third position in the list.


Reply from Richard Rost:

Yep. I use a "custom sort" field all the time. Lets me control HOW the data is sorted. For example, I might want "Access Beginner 9" to be sorted before "Access Beginner 10".

Melanie Bloch on 7/30/2014: Why not setup a lookup drop down menu directly with the CustomerID and RelativeTypeID tables?

Reply from Richard Rost:

I don't understand the question. Can you elaborate?

Melanie Bloch on 7/30/2014: What lesson was it where you showed how to build the MainMenuF?

Reply from Richard Rost:

When looking for a topic, always try the SEARCH BOX at the top of the web site: MAIN MENU. Access Beginner 7.

Steven Mitchell on 7/31/2014: Not sure if tabs work the same in Access as in Excel VBA, but they are sweet if you build your tabbed form as a wizard that you have buttons on to progress to the next or previous page....just my opinion :)

Reply from Richard Rost:

I really, REALLY hate the tab control in Access.

Brian Farley on 8/29/2014: Just for clarification, Option Groups can only set numeric values?
I'm migrating from Lotus Approach and the option groups there could have any value. I was going to ask, "Why not just have the value as Male, Female, Unspecified." But after experimenting, I think its impossible to have anything but a numeric value. Correct?

No wonder you don't like using option groups if this is true..
Thanks

Reply from Richard Rost:

You are correct. Option Groups have to be numeric. Combo and List Boxes are better.

Tareq Salloum on 10/2/2014: Please refer to Time 14:35 in Video # 1 of Expret Class #4. I am still not clear why would you always have to open up two tables and type the ID corresponding to the custormer type and then the relative type ID. Is this a practical way to populate the table? Why not just use a combobox in the cutomer form and do that in there?

Reply from Alexander Hedley:

This is just a quick way of adding relational data.
You are correct the best method is via Forms and Combos, which I'm sure is explained later, this way was just to have sample data to work with in our db and to give an understanding of how the relationship is made using numbers.

BEN CHUA on 10/19/2014: AC2013 Expert 4 9:56 video - ActiveX is this where I can get the Compose Form of Outlook I could use for email? I know by using email address as hyperlink it will bring the default mail - but what I would like to do is to customize the Outlook Compose Form to its specific topic.

Reply from Alexander Hedley:

Ben, if you want to email from Access I'd strongly recommend the Email Seminar.

You can also see Access Expert 20 for sending Emails too.

Wally Weaver on 10/20/2014: The inserting picture techniques are great! However, I want to take it one step further. I need to include various images in a person table including pictures, pdf's, word files, etc. A small display of the image is okay but I also want to be able to launch the attached file in its original program, like Acrobat for the pdf's. A button would be fine for this. Additionally I wonder if more than one image can be handled with the "link" method. Thanks for any help you can give me on this!!!

Reply from Alexander Hedley:

Hi Wally, I think Rich needs to make this lesson quickly as yet again this has been asked.

Here's his answer from a previous question:

You can use the SHELL command to launch an external program, like Acrobat, to view files. I do cover that in the Imaging Seminar. You do have to know what program to launch, however, when using SHELL.

I'll be making a lesson on how to launch ANY type of attachment soon.

Michael Hrynewich on 10/28/2014: Question - How this is done maybe covered in later lessons however, I would like to know if multiple users can use the same database at the same time?? I ask because with all the relationships, data is updated in several tables. So can one user edit an existing record while another is entering a new customer which will also update across the same records as another user is updating??

Reply from Alexander Hedley:

Hi Michael,
Record locking takes place to prevent this from happening.

Michael Hrynewich on 10/30/2014: Need some HELP with linking the Family Member SubForm with the CustomerID. I have verified in the Family Member form properties the source object is FamilyMemberF, the Master & Child fields are CustomerID (everything spelled correctly).
I typed in some sample data with a customer ID in the FamilyMemberT to use and it will not return on the customer form.

Reply from Richard Rost:

Did you make sure you have a record in the parent form?

This is one of those things that's very difficult to troubleshoot without seeing the database. If you want one of my Techs to look at it for you, you can submit it HERE.

Richard R on 11/9/2014: Yep. I'll be recording this one very soon.
Abbi Johnson on 12/26/2014: You mentioned how to link an attachment to the form if it's just a single document but how do you link a document that changes for each record (where the control source selects a specific field in a table). I have tried using the OLE object but then it displays a long messy path. I would love to have an icon display that can be clicked on

Reply from Alex Hedley:

For attachments check out either the Open Other Programs Seminar or Imaging Seminar.

motty green on 1/14/2015: hi all
I would like know how do I see the passwords ??

why you have to setup in the table the = like 1= sliver

thank you for the help

Reply from Alex Hedley:

Which password is this and why do you need it?
Can you elaborate?

Christy Osterkamp on 3/9/2015: What do I do if I get this error message (time frame 17:50) (The changes you requested to the table were not successful because they would create duplicate valures in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.) Can you refer me to where I can troubleshoot why that is happening?


Reply from Alex Hedley:

Have you set a Key that isn't an AutoNumber
Have you set an Index of No Duplicates?

Check your Table
Are you entering another record where a value matches?

Jenise Martin on 3/19/2015: I really like the idea of just having the link to a picture, however I would like to display an actual pdf form. When I add the path then the name of the form it's blank. Will this not work to display pdf's or am I missing the obvious. Thxs

Reply from Alex Hedley:

You could add a WebBrowser control to the Form then use the Navigate method to show that url.

Or you could use the Access Open Other Programs Seminar to have a button which opens the PDF in Adobe or Program of choice.
The Imaging Seminar might be a handy one to watch too.

Chris Needham on 3/24/2015: I am unable to display image. I have the path name correct and the image name correct, but it won't display. I checked the table, (access 2010) and it is a text field. I'm not sure what I am missing. I was able to insert a picture directly by clicking on a picture when the image control opens, but not through the path.

Reply from Alex Hedley:

See this tip.

Bob Field on 4/6/2015: I am designing along with you the database you are building. I have noticed a problem that came up again in this course. In regard to the button design. My buttons are very hard to work with. The size of the button seems to be only controlled by sizing the text box in the button. I watch you and you seem to control the buttons size by sizing the actual button and the text box just floats in the center of that particular button. I had to stop your lesson and work awkwardly hard to get the buttons to size up and arrange because of this problem. Is there a control in the buttons properties that I need to change? Any help with this would be appreciated...Aloha

Reply from Alex Hedley:

Sounds like a possible bug in access.
Is this happening in all your db?
Can you try the usual, reboot, backup, compact repair, new db, import the form into that.

EMMANUEL SEY on 4/14/2015: HELO SIR
i can't set my customerid combo in my extended form to a default

Reply from Alex Hedley:

Are you trying to set the Control Source or the Default Value?
Does this combo have 1 Field or multiple?

EMMANUEL S on 4/15/2015: set a default value ,the combo has multiple field
ie if i type =form![customerf]![customerid]
is not working

Reply from Alex Hedley:

What is the record source of the combo, what is the order of the fields used for it?
Can you set it to 1 (or know value) instead of a Form Field and see if that works first.
If you add a button that MsgBoxs the default value you want does it return the expected value.

Angelika Gutenberger on 6/13/2015: I am using command buttons to open up other forms. When I open these forms with the button I can no longer do the special things the form was designed for. My data entry form works by itself but not when opened by the command button. Do I just need to make a navigation page or am I missing something? My customer doesn't really want a menu page or anything.

Reply from Alex Hedley:

Are you using a Macro or VBA?
What is your code?

What can't you do, have you set the Form to Edit Mode?

Angelika G on 6/14/2015: I haven't gotten far enough to know how to use a Macro or a VBA. What Courses do I need to have to get that information? The form is set to allow data entry, editing, additional info, and deletion. The form that has the command button on it is set up as view only. Is the command button applying the view only features to the form being opened?


Reply from Alex Hedley:

The button wizard will create a Macro, editing this is shown in Expert 3 and then in later ones.

Angelika G on 6/17/2015: I have turned on the edits allowed, deletion allowed, data entry allowed, and basically alow everything.

Reply from Alex Hedley:

Can you try creating the Form again.

EMMANUEL SEY on 8/4/2015: I try to set one to one relationship between the customer form and extended customer form but it did not work in access 2007 but it does work 2010 .so please check and tell me why

Joe F on 9/28/2015: In lesson 4 I created the sub form and inserted it in CustomerExtendedF. Although the stand alone form showed three records, the sub form , when inserted, showed only the first record.
I have downloaded the student database file for this lesson, and I am unable to find any substantial difference between it and my version in the form design.
I must be missing something but I can't find it.
Any suggestions?

Reply from Alex Hedley:

The Link Master/Child Fields, what are they set to? Do they contain the correct ID Fields?

Joe Beniacar on 10/4/2015: Hi Richard,
If you don't know what the Yes/No checkbox result means at first glance (Ex: Gender field), should you just write what value check =, and uncheck =, in the Description section of Table Design view (like we did for the Reward Level field, although that was a Number field with 3 values) - or should we change it's data type to some other type? Thanks for your help with this!

Reply from Alex Hedley:

That's one option, if it's likely to be more than two values I'd used another Table to store the options.

Joe Beniacar on 10/6/2015: Hi Richard, at the end of this lesson: Why don t we establish the relationship between the RelativeType ( HelperTable ) and FamilyMemberT in the Relationship Window , too?

Reply from Alex Hedley:

Access is quite good at spotting relationships, it usually links them if they have the same name.
The Relationship Window just makes it explicit and will show when you build a Query.

Joe Beniacar on 10/6/2015: Hi Richard, I just had 2 quick questions about this lesson:

1)Why are Option Groups not as good as List Boxes and Combo Boxes? Don t all of them store values?

2)Would you use the Option Groups CHECKBOX option for a) a single field where multiple values can be checked and stored in the corresponding Table field or b) multiple related Yes/No fields (like the Mailing Label fields in this lesson) in one Option Group, where each result can be checked or unchecked, and each checkbox has a separate Control Source field to refer back to it's correct Table field?

Thank you very much for your help!

Reply from Alex Hedley:

It's probably just personal preference or that the control didn't work very well in a previous version so stayed well clear.

I'd keep them separate

Joe Beniacar on 10/6/2015: Hi Richard, If the Picture Image Control + Picture Text Box Control (on the CustomerExtendedF) are both bound to the Picture Text field (in the CustomerExtendedT), how does that not create a conflict, where both the link and the picture get stored in the Table field and therefore in the database which would slow it down? Thanks!

Reply from Alex Hedley:

Isn't the Image Control just showing the value from the Textbox?

Joe Beniacar on 10/6/2015: Hi Richard,
I have a couple of related questions about this lesson:

1)a)Do we make the RelativeTypeID field a Combo Box (in FamilyMemberF) - because it is a Foreign Key field in the FamilyMemberT based on the fact that it has repeating values, that we can show ONCE each on a Helper Table? b)If so, then why don t we make the CustomerID field the main Foreign Key field in the FamilyMemberT a Combo Box, as well (like we did when we had the ContactT (MANY) to CustomerT (ONE) relationship, and CustomerID was the Foreign Key there, too - but we made it a ComboBox)? c)Why don t we just make every field with repeating values (Ex: FirstName, LastName, City) a Combo Box? Does the potential # of options have to be limited?

2)a)If you don t leave an AutoNumber field on a Form, will new records on the corresponding Table always update with the correct AutoNumber? b)If so, why do you need to add the AutoNumber field to Combo Boxes, at all (if their correct value would also just transfer anyway to the Table)?

Thank you very much! - You are a very good teacher.

Reply from Alex Hedley:

The Combobox has to contain the Foreign Key, usually the first field, and is bound to the Control so that it gets added to the Table when a choice is made.
If you have a list of Customers
And you create an Order.
You choose a Customer from a Combo
1 | Alex
2 | Rick

Say me, and store 1 in the OrderT as the FK of CustomerID.
It won't transfer unless you tell it too.

Joe Beniacar on 10/6/2015: Hi Alex, thank you very much for answering my questions.
Does the Customer ID field not get a Combo Box on the FamilyMemberF, because adding that info would actually confuse what the record was about - the family member or the customer?
Thanks for clarifying this!

Reply from Alex Hedley:

You are linking it with the Parent/Child keys since it's a subform so Access can handle the relationship, there is no need to have it on the Form and make the user see or know about it.

Cheryl Hokanson on 10/8/2015: I have carefully followed the image path directions and my table is set to text but the image doesn't come up. I get an #Name? and brackets keep being placed at the beginning of the path name and at the end just before the final extention type of jpg. Do you have any ideas to help me?

Reply from Alex Hedley:

Does you path name have spaces in it?
i.e. C:\Program Files\599CD\ etc

Cheryl Hokanson on 10/9/2015: No spaces
Cheryl Hokanson on 10/15/2015: OK, so the name did have spaces in it...but the thing that I missed,the path name was put in the form view...not the design view. I'm sure glad that this is on a video format so that you can review in slow forward!!
Nicholas Messinese on 2/13/2016: Richard, thank you for the very helpful tutorials. My question is why do you always use Autonumbers when creating new tables when you could use Indexed (Yes, No Duplicates) from your primary description instead? Autonumbers can be hard to connect to multiple tables and allows for duplicate entries. Thanks.

Reply from Alex Hedley:

Why are they hard to connect to multiple tables? If you set up your relationships correctly and the Forms are configured properly Access handles all of that for you.
When you set the Foreign Key on another Table you can set up No Duplicates in the table so you wouldn't create more than one entry for that

Justin Bimbiga on 3/5/2016: In accounting, there is a concept of Debit and Credit. Which button from the Toolbox control do you use to capture those accounting figures?

Reply from Alex Hedley:

A text box can be used for data entry

Elizabeth Roche on 3/16/2016: (Video 6:21 on discussion about pop up and modal): I'm having 2 issues. 1st when I set "pop up" to yes for the customer extended form I cannot see the form and my system freezes up and I have to close access and re-open (form is not visible) and I hear a (Beep) when I try to navigate to anything. 2nd when I activate "Modal" for the customer extended form, my side shutter box (side shutter box open/close pane to the left of the programmer view) closes until I close the customer extended f...help? Thanks, Elizabeth

Reply from Alex Hedley:

Can you alt tab to the window?

Elizabeth R on 3/18/2016: Nope. I did figure out a work around. I have multiple displays (3). I found a site that discussed issues that Access has with multiple display setups. They recommended that I set (form property) Auto Center = yes. That fixed the shift of the left shutter bar and the form doesn't disappear off screen (which is why I could see it, or access it.) Not sure if that is the correct work around because I am new and I don't know what auto center is supposed to do. Thanks. Elizabeth

Reply from Alex Hedley:

AutoCenter is exactly what it says, auto centre it or put the form in the middle of the screen.

Vannak Hou on 3/26/2016: Richard, i want to get start learning VBA; do you have anything like this? I have no knowledge in programming at all. Please advise.

Reply from Alex Hedley:

The advanced courses from 301 onwards cover VBA, they are Access 2003 but it hasn't changed much in that respect.

Vannak Hou on 3/26/2016: at 26:05 -- you really confused. some how I kept typing C:\photos\myfilename but it didn't take b/c I realized that the property is a Textbox not a OLEObject box. It took me a while. I was so confused for 30 minutes. Later I found out how to do it.
 

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