ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 2010 Beginner Level 8 Tutorial Combo Boxes, List Boxes, Tab Order, Combo Find Records, Search
 

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 Beginner 7

Next: Access Beginner 9 >

Access Beginner Level 8

Beginner Microsoft Access Tutorial - 1 Hour, 12 Minutes
 
 
This Microsoft Access video tutorial picks up where Level 7 left off. This class covers creating different types of combo boxes (also called drop-down boxes) and list boxes, to give the user a list of options to choose from on your forms. We will also learn how to control the tab order of our forms.
 
  - Value List Combo Box
  - Table-Based Combo Box
  - Multi-Column Combo Box
  - Combo Box to Search Records
  - List Boxes
  - Limit to List, Edit List Items Properties
  - Add, Edit the Combo Box Items
  - Tab Order, Tab Stop
  - Form Cycle Property

Order Now

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




 

Access 2010 Beginner Level 8
Description: Access 2010 Beginner Level 8
Versions: Recorded with Microsoft Access 2010
Works fine with Access 2013 and 2007
Pre-Requisites: Access 2010 Beginner Level 7
Running Time: 1 Hour, 12 Minutes
Cost: $14.99


This class picks up where Level 7 left off. We will start by learning how to create a combo box from a list of values that we type directly into the box (a value-list combo box). We will track lead sources for our customers (where they found out about us) such as TV, radio, Google, etc. You will learn how to force the user to pick a value on your list, or allow them to enter their own. You will also see how easy it is now in Access 2010 to edit that list of options on the fly.

 

Next we'll create a table-based combo box. This is where the combo box gets its list of values from a table that you create. You'll learn why this is a much better method for making combo boxes. You'll also learn how to create your own custom form for editing or adding to the list of options in the combo box.

 

Next you'll learn how to create a multi-column combo box, where you can see 2 or more bits of data in the combo box when it's open. We'll make a table with a list of states and you'll be able to see the abbreviation for the state, and then the full state name when the box is opened. You'll learn how to control the list width, column widths, number of list rows, and more.

 

You will learn how to create a combo box to search for a record in your form based on a field value. We'll make a search box where we can quickly jump to a customer based on their company name.

 

You will learn how to create list boxes. A list box is a close cousin to a combo box. You'll learn about the differences between combo boxes and list boxes, and how list boxes offer different features than combo boxes do. You'll learn how to quickly convert between list boxes and combo boxes (turn one into the other, and vice versa).

 

You will learn how to control the tab order on your forms. This is the order in which Access moves between the different fields on your form when you press the TAB key. You'll learn how to skip particular fields with the Tab Stop property, and how to control where the tab order goes when you pass the last field with the Cycle property (stay on the same record or move to the next record).

 

Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access 2010 Beginner Level 8

00. Intro (6:51)

01. Value List Combo Box (15:02)
Combo Boxes provide for uniform data entry
Select an option from a list
Add LeadSource to Customer Table
Make sure LeadSource is in Customer Form
Change RecordSource to CustomerT
Add Combo Box to Customer Form
I will type in the values that I want
Store that value in this field
Selecting values
Why is it called a Combo Box?
Limit to List
Edit List Items
Adding Items to the List
Default Value
Allow Value List Edits

02. Table-Based Combo Box (12:27)
Why you should NOT use a Value List Combo
Create a LeadSource Table
Combo Box get the values from another table
Which fields do you want in combo box
What sort order do you want
Save in the LeadSource field
Row Source Type: Table/Query
List Items Edit Form
Create a LeadSource Multiple Items Form

03. Multi-Column Combo Box (8:58)
Combo Box to pick States
Create a State Table
State Abbreviation and Name
Hide Key Column
Resize Column Widths
Which column to store in CustomerT
List Width
Column Widths
List Rows
04. Tab Order (10:58)
Show the Tab Order on CustomerListF
Rearrange Controls
Tab Order button
Detail Section
Auto Order
Manually Edit Tab Order
Tab Stop Property
Cycle Property
All Records
Current Record
Fix the Customer Form Tab Order

05. List Boxes (8:55)
Preferred Shipping Method
Create ShippingT
Shipping field in CustomerT
Change To > Combo Box
Don't use Table Lookup Wizards

06. Search Combo Box (2:39)
Turn on Form Header/Footer
Combo Box Find Record

07. Review (5:54)

 


 
Keywords: Combo Boxes, List Boxes, Tab Order, Combo Find Records, Search, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, value list, limit to list, edit list items, default value, allow value list edits, multi-column combo box, hide key column, list width, column widths, list rows, tab stop, cycle property, all records, current record, form header, form footer
 
 

Student Interaction: Access 2010 Beginner 8

Richard on 11/14/2012:  This Microsoft Access video tutorial picks up where Level 7 left off. This class covers creating different types of combo boxes (also called drop-down boxes) and list boxes, to give the user a list of options to choose from on your forms. We will also learn how to control the tab order of our forms. - Value List Combo Box - Table-Based Combo Box - Multi-Column Combo Box - Combo Box to Search Records - List Boxes - Limit to List, Edit List Items Properties - Add, Edit the Combo Box Items - Tab Order, Tab Stop - Form Cycle Property Click here for more information on Access 2010 Beginner Level 8, including a course outline, sample videos, and more.
John Fass on 11/24/2012: When I add a search combo box to the sample DB that I built for the course I get the 3 options in the wizard. When I add a combo box in a DB that I have built for myself I only get the first 2 options. Can you tell me why? Thanks.

Reply from Richard Rost:

This is a quirk in Access. The third option (to create a combo box to SEARCH with) only appears if your form is bound to a TABLE or QUERY. It will NOT appear for unbound forms, or if your form's RecordSource property is an SQL statement.

Unfortunately, the default method that Access 2007 and 2010 create new forms is by making an SQL statement in the RecordSource (which I completely HATE, by the way)... so you have to manually change that to a Table or Query name for that third option to work.

Vannak Hou on 11/27/2012: My LeadSource form does not open.beside the new selected item. The item selected but the leadsourcef does not popup. What did I do wrong? I'm using Access 2010. In the Combo Box properties, I made sure the following items are as the same as yours.

Data tab:
control Source=LeadSource
Limit To List: No
Allow Value List Edits=Yes
List Item Edit From:LeadSourceF

What am I doing wrong?
Time Index: 10:24 at 12:27 under Beginner Access 2010 Beginner 8 lesson #2(Table-Based Combo Box(12:27).



Reply from Richard Rost:

From what you describe, you've set everything up correctly... as far as I can tell. Without actually being able to see your database, or your PC in action, I can't tell you what's wrong from here. Make sure you follow the steps from the video EXACTLY.

Vannak on 11/27/2012: Even the original database of your file on Beginner #8 does not work either; it does not pop either. I can send you the file that I modified in the table itself. I did follow your steps over and over again making sure that I did miss anything. Let me know; b/c I can send you the file.

Reply from Richard Rost:

I just downloaded the file and tested it with Access 2010 and 2013 on two different machines and there's nothing wrong with the database file. I think the problem is you expect the form to just POP UP if you type a value into the box that's not there. That's not quite how it works.

If you have LIMIT TO LIST set to NO, which is how I have the download database setup, then you can just type whatever values you like into the combo box and it will save them. If you want to edit the list values, you have to click on the ADD box that appears below the open combo box.

if you set the LIMIT TO LIST value to YES, then you get a prompt that asks you if you want to edit the list items. If you say YES to that prompt, THEN then your add form pops up.

Vannak on 11/27/2012: Where is the ADD box located in the Combo Box inside the CustomerF form? Because I do not see it. Maybe that is what I am missing. First of all, I have never meant to say that the "original" file does not work; I meant to say when I clicked on the LeadSource selection; the LeadSourceF does not pop as I have seen on your video.

In reference to this particular statement below:

If you have LIMIT TO LIST set to NO, which is how I have the download database setup, then you can just type whatever values you like into the combo box and it will save them. If you want to edit the list values, you have to click on the ADD box that appears below the open combo box.

Reply from Richard Rost:

The "Edit Field List" button. Looking at the video, it appears that it doesn't show up. You SHOULD see it at time index 10:33 in Lesson 2. Sometimes my screen capture software doesn't show tooltips or popup menus (like the little menubar that appears in Word after you select some text). However you can see the tooltip text there. You can see what I'm talking about in THIS IMAGE.

Vannak Hou on 11/27/2012: Richard,

I just wanted to let you know that I found the "button" of that you mentioned. It took all day to find out what's going on because the form itself was not visibly viewable (transparent) which was hard for me to see. I kept on watching that particular time index over 10 times and finally, I saw that little note or button on the screen. Once again, thanks for your prompt reply. That button was hard to see on the screen initially.

Reply from Richard Rost:

Yeah, I didn't realize the "button" didn't appear in the video until you mentioned that you couldn't find it. Like I said, I've had this problem before with those little popup transparent buttons. That's what this FORUM is for though... so we can figure out these kinds of problems together! I'm glad you got it working.

Ron Fini on 1/21/2013: Richard, In Beginner 8, Lesson 1, when I go to the Property Sheet to change the Record Source I do not get a list of all the fields in that form, only see the Form Name. I do not see any SQL select statement. Is it possible that Access has change this in one of their updates?
Ron Fini on 1/21/2013: Richard, If I set the Limit to List "Yes" and the Allow Value List Edits to "No," I seem to still be able to click on the small Edit Box and make changes. How do you keep other people from Not keying in their own information and Not be able to edit the list?
Alex Hedley on 1/22/2013: Ron, just below the top of the Property Sheet there will be a 'Selection type: ####'

What does this say?
Form
Section
Label
Text Box

There will then be a dropdown just below it that which has the name of the object you have currently selected.

This needs to be 'Form'
You can also click where the rulers meet in the top left hand corner of the Form, a small black square will show, and now you can set the Record Source of the Form to a Table/Query.

Now you mentioned "I do not get a list of all the fields in that form", this would be the case where you have a Control selected like a Text Box and then you would be setting the 'Control Source' to a Field in your Table/Query that you have but the Form would need it's Record Source first to allow the Form to know what Fields are available for that to use.

Alex

Wayne Ayotte on 2/11/2013: In lesson 2 you make a form "LeadSourceF" to allow edits to the table "LeadSourceT" then you mention that LeadSourceF is Modal. Can you have the Modal property to No? You have reasons for not doing that.
yifan zhang on 2/15/2013: Thanks, many featues i have never known how to use them. My question is where i can find that [Tab Order] command in Access 2007? It has some differences with Access2010.

Reply from Richard Rost:

It should be somewhere easy to find. I have to admit that I don't even have AC 2007 installed on any of my machines anymore, so I can't even look it up for you. I used AC 2007 for a VERY short time between 2003 and 2010 so I don't remember it off the top of my head. ANYONE ELSE know?

Kevin Robertson on 2/16/2013: I don't have AC 2007 anymore either, but pretty sure the Tab Order button was located on the Design Tab!
Kevin Robertson on 2/16/2013: ***CORRECTION***

AC 2010: Design Tab
AC 2007: Arrange Tab

Christian on 2/19/2013: Richard,

I recall in an earlier class on table properties you mentioned that you are not particularly fond of lookups for a field from within a table.

With the combo box you added for LeadSource within the CustomerF form, couldn't you set the LeadSource field in CustomerT to a combo box bound to LeadSourceT so that when it is pulled into the CustomerF form it was already set up? What I am really asking is if there is a downside to doing it this way. I know you mentioned that you don't always show us the best way up front, and I am actually thankful for that because it does help to have an understanding of all the different ways something can be done.

But I would like to know if setting up a combo box bound to a source table for a field in a separate table is something that you do not recommend and the reason why.

Thanks and great job with this series. I am learning a lot.
Christian

Reply from Richard Rost:

I don't like lookup fields in TABLES because when you get into more advanced stuff like VBA programming, recordsets, SQL, etc., they because ALMOST IMPOSSIBLE to work with.

Now... setting up the form's combo box so it pulls records off of another table IS exactly what you should be doing... and I'm going to take you through a couple of different INCORRECT iterations of this between Access Beginner 8 and Expert 1, where I show you how to do it PROPERLY.

Like you said, I'm building up your appreciation for WHY the correct way is CORRECT.


Christian on 2/19/2013: Thank you for the reply and clarification. This explanation was exactly what I was looking for

Regards,
Christian

Jennifer on 2/26/2013: Hi 1.) I reset my tab order by re arranging the list of fields, saved . Most of the rearrangement worked but 2 fields refused to move in line with the list of the tab order I had set. I dont know why. 2. ) If I choose to limit the cycle to one record for ease of checking, how does one then move onto the next record. Thanks

Reply from Richard Rost:

1. I'd have to see your database. 2. Click on the record navigation buttons on the bottom of the form.

on 2/27/2013: Again thank you so much. What an incredible service . Have sorted the tab order , was my mistake in sequence.
Bea Anderson on 3/4/2013: When I set up an unbound combo box to use with a button, I don't get the third option.

Reply from Richard Rost:

Make sure your form is bound to a TABLE, not an SQL statement.
SEE THIS.

Carrie Casto on 3/13/2013: When my Combo Box Wizard begins, I am only given the first 2 choices. And the wizard doesn't ask me where I want to store the selected value from the combo box in my data base. When I looked at the Help ? videos, they were the same way. What's different?

Reply from Richard Rost:

Check to make sure that your underlying form is BOUND to a table or query. Otherwise there's nowhere you CAN save the value.

Jeanette Schoenau on 3/18/2013: I was having trouble creating a table based combo box, and I must have hit a setting I wasn't supposed to - now I can see my Customer Form in design view but in Form view, nothing shows up. The screen is blank.
Frank P on 3/20/2013: Yep, Select the Input you want to change the tab order of then go to the Property Sheet and click on the tab "All", then scroll down to the bottom of the list and you will find "Tab Index" this is the number of tabs it takes to get to the selected input. The first Tab Index is Zero.
Dustin Nihsen on 3/29/2013: How do I type in a search in my combo box so I can use it as a customer search bar?


Reply from Richard Rost:

That's exactly what this lesson shows you... unless I misunderstand your question.

Jennifer Hull on 4/2/2013: How do I set up a list box that allows selection of multiple items?

Reply from Richard Rost:

Jennifer, there are two things you can do. Access 2010 and later have something called MULTI-VALUE FIELDS which I strongly disapprove of. They're OK for simple databases, but when you get more advanced, they're VERY hard to work with. I prefer custom-coding my own multi-select list boxes. I cover how to do this in Access 321.

Jennifer on 4/2/2013: Thank you again. Have less time now to learn,as am back at work after sick leave. However have tried to set up a database for my surgeon as a 'thank you.' Have also given him the name of your website. All thanks to you.
Bea Anderson on 4/11/2013: I created a combo box to search for a volunteer record in my data base. It worked until several days ago. Now when I click on it and pick a record, it gives me the message "Return without a Go Sub" If I manage to get it to work, as soon as I close the data base and open it again, I get the Go Sub message when I try using the combo box.

Reply from Richard Rost:

I haven't seen that error message in a VERY long time. Do you have a "return" statement anywhere in the VBA code behind this form?

Bea A on 4/13/2013: I don't have a return code. I removed a subform that I added, saved and closed the form. Then added it back and now I don't get the GOSUB message anymore. Thanks for your help.
Laurie Jones on 5/28/2013: Do you have an outline for the upcoming Access 2013 Advanced and Developer classes available? Also, when will you talk about Macros? Thank you.

Reply from Richard Rost:

I have a ROUGH outline that's not really fit for publishing. I will be basically following the same general steps that I took before in the 2003 series, but I'll be adding, moving, and expanding material as needed.

Half the time I don't know exactly what I'm going to be covering until I start recording that class. When I sit down and watch the 2003 videos while preparing to record the new ones, I sometimes go, "no... that could be better" and I rearrange everything. For example, I just pulled a bunch of macro material OUT of the Expert 4 and 5 outlines because I'm moving it to the Advanced lessons... but I added in some extra cool stuff that I didn't cover before.

I'm also going to be adding some new lessons to the Expert courses that I didn't have the first time around... like a more in-depth look at functions. So, all I can say at this point is that I don't have a set outline because the material that I cover is really pretty fluid. What I cover 3 lessons from now may be different based on how the next 2 lessons go.

I've covered a TINY bit on macros already (just modifying some of the embedded macros that Access creates for you) but the ADVANCED classes will go into MACROS and EVENTS in detail, and then the DEVELOPER lessons will go into VBA programming. It's basically a split up division of my late 200- and 300-level classes from the 2003 series... fine tuned for Access 2013.


Catherine M on 6/12/2013: In design view on your CustomerF, right click mouse. Choose Tab Order. Select Detail. Click to select a row, or click and drag to move.
Brian Merrick on 6/28/2013: Can you use layout view when changing the fields? Or is Design view better?

Reply from Richard Rost:

I personally prefer design view. Layout view is OK for moving things around, but I've noticed some weird things happen in Layout view - especially when you start getting into programming.

Brian Merrick on 6/28/2013: Why do you create a shipping field, if you are creating a list box?

Reply from Richard Rost:

You have to have a field to store the value in. The list box just lets you pick a value from a list. That value has to GO somewhere.

Brian Merrick on 6/28/2013: When I click on combo box, it does not give me a third option, Find a record on my form based on the value i selected in my combo box. Is there any way to do this?

Reply from Richard Rost:

Make sure you read through the other comments/questions here before posting one yourself. This has been answered before. Your form needs to be based on a TABLE, and not a QUERY or an SQL STATEMENT in order for that 3rd option to show up.
SEE THIS.

Adam Wray on 7/1/2013: I don't like how the text is highlighted grey as I tab through the fields in my Customer form; it's difficult to read. Can you change the highlight color to make it clearer?
Srinath Nandyal on 7/17/2013: Richard
I have created the Combo Box and have set the source as LeadSourceT. When I go to Form view and click on drop down list, it shows the sources I have established but when I select one source it does NOT populate the field. What am I doing wrong??
Srinath

Reply from Richard Rost:

Sounds like your combo box isn't BOUND to a table field properly. Check the control source property and put the right field name in there.

Srinath Nandyal on 7/17/2013: Richard
Earlier today, I had submitted for your review a problem I am having with populating the Lead Source field from drop down menu. I just realized an interesting thing. I had used the same CustomerForm to go through Lesson#7 and it worked. I had populated 4 customers only. Even with the new requirement to get the information from LeadSourceT, old information still is displayed for the four customers. Of course, I can no longer use (populate) with new information. Why??

Srinath

Reply from Richard Rost:

I'm sorry, I don't understand. Can you rephrase the question, please?

Joni Moore on 8/16/2013: What lesson did you end up covering how to get rid of duplicate values in the search combo box?

Reply from Richard Rost:

I don't believe I covered it in the Beginner series. You would want to use an AGGREGATE QUERY to create a unique list of values. I cover that in Access Expert 11.

Allison Quamina on 9/7/2013: Richard,I have a column that contains multiple items (same classification) for each record, separated by commas. It is not a Combo-Box. Is there a way to search for items within that column? Do I need to apply some specific formatting to separate each of the items? should I turn that field into some sort of table? any guidance is appreciated. Thanks in advance. AQ

Reply from Richard Rost:

You mean you have multiple items in a single field, like:

Employees: Joe, Sue, Bill

Then yes, that information should be stored in a 2nd related table, as I cover in my Expert series.

Jon Hollis-Brown on 9/20/2013: When I insert a button the button shape/image overlaps the control box making it difficult to size properly. In your videos the button is the same size as the control box. Is there a reason for this?

Reply from Richard Rost:

Common bug. Run Office Update, reboot.

Jon on 9/21/2013: Thanks. I have the Office 365 pay as you go and it supposedly updates via push. Still the same problem so Ill contact Microsoft support to see if they have a solution to the update.

Reply from Richard Rost:

I remember encountering another customer with this same problem a few months ago. Updating Office, I believe, fixed it. Office 365 is supposed to keep you up-to-date, but make sure it's set to install updates automatically.

Jon Hollis-Brown on 9/25/2013: Hi team. Am I correct is saying that if you create a Multiple Item form from the menu it is created with the row/column format embedded in it and you have to then place all your fields in these rows and columns? If you want a free layout as you describe in your lectures you have to start from a blank form design? Is there any way to convert from a tabular format to a blank format?

Reply from Richard Rost:

You can change their design if you want - and you can switch back and forth between formats, you just have to remove all of the junk that the setup wizard gives you.

John Borrelli on 9/26/2013: I created the combo box and when selected it drops down the list just the way it should. When I try to select something from the list I get the dreaded Bleap. What could cause the std combobox from not allowing me to select an item from the list?

Reply from Richard Rost:

You have it bound to a read-only field? Your underlying recordset is not updateable. The combo box is LOCKED. Could be a number of things.

John Borrelli on 9/26/2013: I have a combobox that drops down First Name and last name as seperate fields. I don't want to combined them like you did. I want the combo box to display the first name, which it does, and a seperate text box to display the last name. How would you do that.

Reply from Richard Rost:

You're SELECTING a customer on a form where CustomerID is a foreign key, correct? If so, just make two combo boxes next to each other. Have them both bound to CustomerID, but make the visible field the first name in one and the last name in the other. When you change one, it should change the other.


Michelle R on 10/21/2013: The Tab Order button In Access 2007 is on the Arrange tab at the very top (next to Design)in the Control Layout section
Edwin Jordan on 11/7/2013: how can I make a combo box that takes more than one option, example, assuming that the customer was from PA - CA - NY etc I know this is not possible but I am working in a project that I have to insert say about 10 items.

Reply from Richard Rost:

Edwin, this is something that is BEST done with a separate, related table and a subform. I cover this in the Expert series of classes. You COULD use a multi-select list box or a multi-valued field (which I also cover in different lessons) but the related table and subform is the BEST method.

Edwin Jordan on 11/7/2013: So, if you want to answered gender ( male/female ) you would use a table ? - I need to assign a value to gender, male is worse than female. Any suggestion?

Janet Gangl on 11/27/2013: Hi Richard - 8:45 Lesson 2 Beginner 8; I've been thru all of the beginner and expert videos and am now working on my own database. I have a similar table to the lead source table except that I'm using an ID field therefore my combo box has the two fields. If I change "Limit To List" to No - I get the following error - Access can't set the LimitToList property to No right now. The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first, and then set the LimitToList Property.
I played with column widths in the properties Format tab - no luck.
I've googled this error and can't seem to get a straight answer or anything that seems to work. Are you familiar with this error?

Sonia F on 1/21/2014: Can you set the Search Combo Box to a default value? My form opens with my last search inside the box even if I have moved on to another customer. And can you sort the list to make it easier to find what you're looking for?
Katheryn Hartig on 2/7/2014: Richard - why do you think Microsoft chose to use the SQL Select statement for selecting Record Source in 2010 as opposed to basing it on a table as it did in previous versions?
Katheryn Hartig on 2/7/2014: FYI - The Edit List Items button that displays when you open the combo box when Allow Edits to Value List is set to Yes doesn't appear in the video (14:11). Made the instruction a little confusing at first.
Steven S on 3/10/2014: Is there a way to set up a form so that the opening record is not record #1? I'd like it to open to a new record or a placeholder/dummy record. I'm afraid my end users will accidentally edit patient #1.
Kim Jensen on 3/16/2014: Hi mr. Rost

Great classes,

How do you remove the double values like XYZ corp.
I have made a spare part form based on the Expert 7, all Works finally, then i wanted to put in a search category combo just as a look up option for me, and i get multible of the same category in the comboSearch.

Richard Wilson on 5/4/2014: Excellent classes so far.
I have been using Act! for a number of years to keep track of several things. One is a music competition that my wife runs. For each competitor, there are such fields as teacher, school, concerto, and so forth. We like to keep a lot of data for the current year, but then delete a good bit of it for archival purposes. However, we need to keep teacher, school (as of the year the contestant entered) and other matters AS OF THAT TIME. It seems to me, and I am sure I am getting ahead of myself, that entering the data in the field using the combo field or whatever, rather than creating a link, would preserve the data as it existed in 2014, and it the teacher moved from one school to another, that data would not change, nor if the contestant returned in a following year, with a different teacher or from a different school, that information would not get confused by relationing.
Again, I know I am getting ahead of myself, but I envision two databases: on for current information, and then, moving selected information about current year contestants to a cumulative archive of all years, and deleting current contestant information for 2014 from the database and using the same forms and tables for 2014 (schools and teachers and so forth would change, but that would be all right under these circumstances). Am I making sense and am I heading in the right direction?
And is there a reasonably simple, as well as easy, way to migrate from Act to Access?

Safiyyah on 5/17/2014: Hi Richard I have created a multi column combo box. Can all value of the columns from the combo box be stored in the table? Eg. Select TX,Texas both value stored in the table as well appears in the form.
Robert Taylor on 5/21/2014: Richard,
I have restructured a table and have just 3 columns; number, teacher, and class schedule. There are 16 records. I have created a list box and can see all columns in one row and can move from record to record via the arrow keys.

The problem I have having is that I cannot select a record for the table field and then move to the next field. The record remains black instead of black on a white background. Plus, the tab function no longer works. Any thoughts??

Other than that small hiccup, I'm learning lots. Thanks for the help.

Bob

AYDIN D on 5/27/2014: Dear Sirs,

I watched expert 11 to see how can I make a seach combo box without duplicate value but did not see any comment there.

I want to make a search combo box without doplica value from a TABLE.

Is it possible ?

Regards,

Aydin D.

David Clifford on 6/13/2014: I am using Access 2013. When I go to the Combo Box Wizard I only get the first two options (I want the combo box to get values.... and I will type in the values...) I don't get the third which allows you to do the search combo box. How do I do this?

Reply from Richard Rost:

As I mention in the video, you don't get this option if your form is based on a QUERY or SQL STATEMENT. It has to be based on a TABLE.

Stephen Hendricks on 6/15/2014: Richard. GREAT classes. Using the third option in the combo box wizard does not give the option to sort ascending order? What am I missing? I would like to do a customer search from my customer form and have the drop down box sort alphabetically. Thanks, Stephen

Reply from Richard Rost:

You'll probably have to edit the SQL statement for the combo box RowSource property manually to add an "ORDER BY" clause. That's a bit more advanced, but it's covered in Access Expert Level 3.

Pete Theron on 6/16/2014: On the combo form I have selected the short code of the state to display but it still selects the long name?
Robert Taylor on 7/12/2014: Rick,
As your excellent lesson demonstrated,I constructed a "search combo box" on my form and it works perfect. The only hitch I have is that I also use this form to edit each record.

When the correct record shows up in the form, when I tab to a field I would like to edit, the record has changed to the next record in the series. Frustrating!!

Any thoughts?

Bob

Robert Taylor on 7/15/2014: Richard,
I have completed a table including 3 fields; primary ph #, secondary ph #, and emergency ph#. These are text boxes where the operator fills in the ph#. To identify the owner of each number, i have a combo box with possible owners; mom, dad, aunt, uncle, etc. each of the text boxes with the ph# are tied to this owner box. the combo box works fine for the first ph# owner id. as u enter the other ph#s, the owner in all ph#s changes to whatever is the latest value chosen. i think i have checked all possibilities but am stuck on what to do. can you help?

thanks,

bob

Brian Merrick on 7/16/2014: I have created a vehicle checklist database. On the form, i have created a tab for each shift for example, Day shift, mid shift and Evening Shift. I created Three Separate form for each shift, but they are on the Vehicle checklist form at a tab so i can view them all at once. When I input data such as the date and driver and mobile number, everything is fine. When i try to enter data into the tab and move to the mid shift tab, it give me an error message, "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field of fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. When i hit the undo button and try again its ok.
Any Thoughts why it does this?

Chris T on 7/17/2014: Tab Order on AC 2007 can be found (while in Design view on the form) on the Ribbon. It is on the design tab beside the Property Sheet button, or while on the form in design mode you right-click your mouse and select.
Chris Thompson on 7/17/2014: Please ignore my entry sent in today. Duh. :)

I was looking at Access 2010 when I answered the old question about tab order.

Susan Holbrook on 8/7/2014: I have put company names in to alphabetical order in my table. On the drop down list from the combo box on my form they still appear listed according to the ID number. How can I change this to an alphabetical list? Thank you

Sue

Reply from Richard Rost:

Beginner solution: rebuild the combo box again using the Wizard and make sure you select the COMPANY NAME as the field to sort by. It's an option in the wizard you might have missed.

Michelle Arieux on 8/9/2014: The combo box search is great and I like having them on the form. My question is if there are more than one record that meets the criteria, how do you navigate to the next record. Currently when I search for company and there are more than one students from that company access takes me to the first record it finds. How to I then navigate to the next one? Also does the combo search work on a continuous form (ie CustomerListF)?

Reply from Richard Rost:

When you use a combo box to find a record, the combo box is really just displaying for you a unique list of the records for that field. You can only jump to one at a time. If you want more powerful search capabilities, check out my Search Seminar.

Robert Maddox on 8/29/2014: Forms Question- I have a Combo-Box from which I select a desired "Project-Type-Category-Description" from a related table. Each "Project-Type-Category-Description" also has an associated GSA-Code# in the table.

How can I display the associated "GSA-Code#" in the same form field or in an adjacent field (that links to the "Project-Type-Category-Description".

Please Advise- Thanks

Reply from Richard Rost:

Sounds like a job for DLOOKUP

Megan Prosser on 9/12/2014: I have followed each of your lessons exactly and now when I try to open my clientT, I get this message:

"the expression ON Current you entered as the event property setting produced the following error: object or class does not support the set of events"...

What does this mean and now do I fix it?

Thank you so much! I really like your classes!
Megan

Reply from Richard Rost:

I really can't tell just from the error message. I'd have to see the database to tell you what the problem is. Have you tried a Compact & Repair?

Megan Prosser on 10/12/2014: How do I change the order of the items in my list box?

Reply from Alexander Hedley:

If you are using a Query to fill the List Box just add an ORDER BY clause to get it the way you want.

Craig Brown on 11/30/2014: To Ron Fini:
I had the same problem. Then I realized that the Value List Edits had no bearing, because the row source type is now Table/Query. So the only way I could do what you want was to go to the Form Properties for LeadSourceF and set "allow additions, allow deletions, and allow edits" to no. The dialogue box to edit still comes up when you try though it has no effect. As long as the List Items Edit Form is still LeadSourceF, if you need to edit the list, you can set "allow additions, allow deletions, and allow edits back to yes. Richard may have a much more elegant way, but I'm a neophyte. Any comments Richard?

andrew davis on 12/27/2014: Hi Rich, I set up a database which uses combo boxes. However, instead of setting up the Attribute table with 1 field and then adding the items to this table. I set up the attribute table with several fields (text fields), where each field is a selection in the combo box. I would like to change the table format similar to your format so that I can create a form of the table to allow adding of attributes available in the combo box. I would have to delete this combo field box and exchange it with the new one. Is there a way to do this without losing the data in the other 1800+ records? Thanks Andrew

Reply from Alex Hedley:

Does your Table contain an AutoNumber or is it just a Text value you are storing from the Combo selection?
If it had an AutoNumber which will change you could copy these attributes into your new table then do join from old to new and an UPDATE query to set the old value to the new AutoNumber.

Andrew on 12/28/2014: No - no auto number. The table named DonationTypeT has 4 fields: Adoption; Donation; Puchase; AppFee. I suppose I could create a temp field with a 1,2,3,4. Run down all the records and entering in 1 for adotpion, 2 for donation, etc.. Then delete the DonationTypeT table. Recreate it correctly, then re-enter the respective donation type, then delete the temp table with 1,2,3,4. Just thinking out loud. My original thought was exporting into excel editing the field then reimporting, but this would create more a higher probability of corrupting the data. I already have 7,000 records some of which have multiple donation activities. Thanks

Reply from Alex Hedley:

When you say DonationTypeT has 4 Fields: Adoption; Donation; Puchase; AppFee do you mean Fields or Records?

Are you wanting to relate a Donation to a specific type via a Combo?

Andrew Vandagriff on 12/31/2014: I created a value list combo box through the the lookup tab of the field properties in the design view of the table. I chose "List Box" for Display Control; "Value List" for Row Source Type; and then entered my values between parenthesis and separated by a semi-colon (ex. "referral";"email blast";"internet search") in Row Source. Is this the same thing as going through the wizard? If not, how does this compare to using the table based combo box in regards to updating it? It seems easier to create than the table based box and updating is a matter of just going into the properties and updating the "Row Source."

Hope I'm not jumping ahead, just finished all 9 levels of Beginner.

Thanks for your response

Reply from Alex Hedley:

I'd stick to creating a separate Table with its own rows that you then base a combo on.
This means it can be used in multiple places and gives you more flexibility for amendments.
Also if this has been added into the Table itself as a Lookup These can cause you issues further down the line with relationships etc

Mark Nielsen on 2/14/2015: Richard,
I have inadvertently set or deselected some option that is causing a problem. Referring to beginner 8, lesson 6 "Search Combo Box" when I create a NEW DB, table, form and Combo Box (in the header) as outlined I do not get the 3rd option "Find a record..." I only see the first two the 3rd isn't even listed.
However when I try the same function in the DB built for the class or in one I built about that same time the operation works as shown.
I can only assume that I triggered some option in my messing around but don't know what it is.
Please advise..

Thanks,
Mark Nielsen

Reply from Alex Hedley:

Check out this Blog Article.

Cheryl White on 4/21/2015: I tried to add a search combo box to an existing form that I use to enter new records but could not get it to work. I then created an new form and copied all the fields to it and the search combo box worked. I compared the form properties but could not figure out why I was not able to add the search combo box to an existing form. Is there a specific form property that might be causing me not to be able to add it to existing forms?

Reply from Alex Hedley:

See this tip for a Combo Box Search Form.

You hadn't bound the combo to a Field had you?

randy richardson on 4/29/2015: Everytime i create a listbox, It stores the ID# and not the name.e.g. I have employees that I am tracking and have a combo box based of a select group of employees (Department) but when I go into the table and look at the field they are stored at, All I see is ID#s. anyway to get it to show the names?

Reply from Alex Hedley:

The first field in your data source will be the one that is stored in your Table.
You can just use a Query to JOIN on the ID and show the related name.

Victor Solano on 5/22/2015: I have a test database and I am doing a combo box to pull the information from another table but the information is recording the result under the incorrect field name even though I select the correct storage field. In other word I have claimerName and claimerNo where claimerNo is the primary key from the table that I want to pull the information from. Any though

Reply from Alex Hedley:

SO [claimerNo] and [claimerName] are fields from a Table (let's call it ClaimerT).
You've created a combo box which contains both those Fields. I take it [claimerNo] is hidden and that's the value you want storing in your other Table as a Foreign Key.

What is the Table/Query (RecordSource) of the Form you have this Combo box on?
What is the Field name in this other Table that you are wanting to store the Claimer Info in?

West Bobby on 8/8/2015: i have a combo search box that works great for my employees table form, but i done a query just with name and phone numbers and called it list with a transparent button to take me to employee table form but of course my combo search doesn't work cause of filter, can i create a button to get rid of the filter other than using little bitty filter button at bottom cause i wont to turn that off, my hubby not computer smart, Any help thanks :)

Reply from Alex Hedley:

In VBA add this to a button event

Me.Filter = ""
Me.FilterOn = False

For a Macro look for "ShowAllRecords"

Robert Wiebel on 8/24/2015: I am working on a DB where I need a logo to change on a report header when a school is selected. What lesson do you teach how to to this?

Reply from Alex Hedley:

You could use the Addendum Tip or take the Imaging Seminar.

Michael Goodman on 9/9/2015: Hey Richard.. I've added the combo box to the form, it was working great than stopped, I'm uncertain why. I looked under form properties and made certain source was set to my Table and not SQL, it is. I tried replacing the combo box a few times, it worked for a short period of time when inside the form and not the header, now it doesn't work with either the header or form, any thoughts?

Reply from Alex Hedley:

Is the control bound to anything.
Is there still an event attached to the combo? Or are you doing it with a button.

michael spencer on 9/23/2015: I have a table based combo box. I have a company number for each company in a separate table . When I use my form the company number is showing up in my attached table instead of the company name. I choose the company name when i design the combo box? any idea what I am doing wrong?

Reply from Alex Hedley:

Usually the first column in the Combo is the one that is bound to the Field.
You're Combo is likely to have ID as the first column then Company Name as the 2nd, and ID will be hidden.
You will want to keep it like this.
Now just create a Query that joins your CompanyID to the CompanyT and pull in the Company Name to show elsewhere.

Joe Beniacar on 10/3/2015: Hi Richard, when you select a value from the Search Combo Box, why doesn t it filter the records in a way that completely removes all of the rest of the records (like a Query), to just give you all of the records you want? [You can still cycle through all of the records! with this combo box (using the navigation buttons) even those that don t have that value (bad!)]

Reply from Alex Hedley:

It applies a Filter.
If you want the record set to change you'll have to use techniques taught in later classes that changes the source of the Form to a given SQL statement or base it on a Query that is passed a parameter from the Form.

Richard Lanoue on 10/19/2015: Updating List Box problem... If I have a form where there is a list box and make any changes, the form updates everywhere else but the list box. In order for EVERYTHING to requery, I have to close the form and re-open it. Is there a better way?

Reply from Alex Hedley:

Have you tried a LISTBOX.Requery, where LISTBOX is your Listbox name.

Joe Beniacar on 10/22/2015: Hi Richard, I have the same question as Janet:

"If I change the LeadSourceID Combo Box s "Limit To List" property to No, I get the following error: 'Access can't set the LimitToList property to No right now. The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column.' Isn't that the point? - Any ideas why this might be a problem? Thank you!

Reply from Alex Hedley:

Did you change the Bound Column number?

Joe Beniacar on 10/24/2015: Hi Richard, do you make a Combo Box when a field has only a few values - that can be reused for different records?

Reply from Alex Hedley:

Can you elaborate?

Joe Beniacar on 11/18/2015: Hi Alex, my question was basically - Should you turn all F.K. fields in a Table into Combo Boxes ? Since you might make those fields in 1 Table before realizing that they could be F.K.'s from other Tables, is there a low # of values the field should generally be able to hold when you should make it a Combo Box (vs. a Text field with some repeating values)? (Ex: Should CompanyName be a Combo Box?) Thanks!

Reply from Alex Hedley:

If you mean a Lookup Field in the Table then no. Keep it as a number.
If you are going to reference these on another Form then yes a combo makes more sense as you can see their name instead of just 1.

Linda Kalis on 11/24/2015: Richard. Your Access Tutorials are awesome. I'm up to Beginner 8, and have gotten through Lesson 5. I am now attempting to set up a Company Search and I cannot, no matter what I do, get the third option -Find a record on my form based on the value I selected in my combo box in the Combo Wizard - to appear. I know that my form is formatted properly, but I'm at a loss as to what to do next???


Reply from Alex Hedley:

This blog post should help.

Linda Kalis on 11/24/2015: PS to my just posted question about the third option not appearing in the Combo Box Wizard, my form is definitely bound to my Subcontractor Table so I can't figure out what the problem is.
Linda Kalis on 11/25/2015: Please disregard my question about the Search Combo Box. I figured out where the problem was - the Record Source was pointing to the correct table, but I eliminated all of the wording after the table name and just left the tablenameT. That worked. I remember Richard doing this in an earlier lesson and a light went off that this might be my problem. Thank you so much for these tutorials. They are fantastic.


Reply from Alex Hedley:

Glad you figured it out

Guixiang Fan on 11/27/2015: I use combo box put in page Header to do search, but after I save and close it, the search bar is not showed on top of the form. What is the reason and how can I fix it? Thanks.

Reply from Alex Hedley:

So the Header is completely gone?
The form isn't scrolled down hiding it, is it?

Andrew Hite on 12/3/2015: I've followed you step by step and the wizard is selected but when I click to add the combo box to my form layout the wizard doesn't pop up. Any thoughts on what is going on?

Reply from Alex Hedley:

See this tip. The wizard probably isn't toggled on.

Joe Beniacar on 1/23/2016: Hi Alex. I was just saying it's not so obvious to me when you should change a Table's 1)Text field (with potentially the same value(s) for multiple records) with corresponding Form Text Box control, to a 2)Number field, with corresponding HelperT, and make it a Combo Box on related Forms? That seems to be a bit of a grey area... Any ideas? Thanks!

Reply from Alex Hedley:

This is where Normalisation comes into play.
Do you have examples of where you would think otherwise?
The CustomerT has lots of information about a Customer and can be used in various other Tables, like OrderT etc.
It's easier to have an ID Field i.e. CustomerID as the Foreign Key than a Name which could change over time. The ID being a AutoNumber shouldn't change and can be stored in these other Tables and joined using a Query.

cristy stout on 1/27/2016: Hi Richard, your videos are awesome. Thank you. With these combo/list boxes, can one incorporate those with a parameter query?
tks/

Reply from Alex Hedley:

You can set the source of the combo/list to a query.

Elizabeth R on 3/9/2016: Wow. That sucks. :D What a feature! Let me try it...You might want to add my issue to the forum for B8 Lesson 6 so that others don't spend 2 hours trying to figure out what we did wrong...LOL.
Virginia Mergl on 3/14/2016: Hi, is it possible to instead of tab to go the the next field press Enter to go the next field

Reply from Alex Hedley:

You could use some VBA to capture a KeyPress then use a GoTo command.

Something like

Private Sub txtSomething_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
' Ignore this key
KeyCode = 0
End If
End Sub


Kevin Gill on 5/12/2016: When creating a combo box. You have a list of options, but none may apply, thus creating an "Other." How can you make it so if a user selects other it makes them type a value in or in an "other field?"

Reply from Alex Hedley:

Have an AfterUpdate
If combo == "Other"
OtherField.SetFocus
Check for Length = "" or IsNull(Field) if combo is other when saving.

 

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