ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 202: Command Button, Tab Stop, Tab Order, SQL Primer, Subforms, Visible Property
 

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
 
 
Courses - Microsoft Access 202
Pre-Requisites: Access 201 very strongly recommended
Description: Intermediate Microsoft Access 2
Running Time: 74 minutes
Previous Lesson: Access 201
Next Lesson: Access 203
Main Topics: Command Button, Tab Stop, Tab Order, SQL Primer, Subforms, Visible Property
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 4/27/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 202
Intermediate Access 2

More command buttons, setting values across forms, sorted combo boxes, form/subforms, and more. 74 Minutes.
 

AC202 Major Topics

  • Button to Show Specific Record
  • Tab Stop, Tab Order Properties
  • Sorted Combo Boxes
  • SQL Primer
  • Form / Subform Relationships
  • Visible Property

This class follows Access 201, which we strongly recommend before taking this class. Access 201 covers all of the basic fundamentals and relational database concepts that are key for this course.

We'll begin by placing a command button on our customer form to show just the contacts for the current customer. This will allow us to see all of - say - Joe Smith's contacts.

 

Next, we'll learn how to make the combo box on our contact form get its default value from the currently open customer record. This will teach us how to get a value from a different form using a new kind of naming notation. The benefit - we can create new contacts without having to fill in the customer value.

 

We'll learn about the Tab Stop property - so we can make our Tab Order skip over the first few fields and go right to the Notes field when opened.

 

Next, we'll learn how to sort the company names in our customer combo box by creating a sorted query to do the job...

 

...and then manually changing the combo box Row Source property to get the values from the new query.

 

The result? A sorted list.

 

We will also teach you just a little bit of SQL (Structured Query Language) and show you how to modify the row source property without making a query to change that sort. We'll get into more SQL in future courses - we just wanted to give you a taste today.

 

We'll learn about more combo box properties, such as the List Rows and List Width properties.

 

Next, we'll enhance our contact management system by integrating a Callback feature into it. This way we can generate a listing of any customers we have to call back. We'll start by adding the necessary fields to our table and form.

 

We'll create a Callback List Form which is a continuous form showing all of our callbacks - sorted by date. We can click on one of them and then click on a button to open up either the contact itself or the customer's record.

 

In the following lesson, we'll learn about Subforms, which are forms that can go inside of other forms to show related data (like showing the customer's last couple of contacts right on the customer form). We'll start by designing a mini contact subform.

 

Then, we'll use the Subform Control to place the subform inside of our main parent form.

 

We'll teach you how to hide certain fields (like those ID numbers we don't need to see) with the Visible property.

 

In this lesson, we'll also give our Main Menu a bit of a face lift. What we've had going so far was nice, but it's starting to get cluttered, and lacks a certain professional tone. Let's upgrade it a bit...

 

We will also see how to use the Phone Autodialer so we can just click on a button and have our computer dial the customer's phone number for us!

 

We learn several key concepts in this course. Learning how to get values across multiple forms is a very important concept. Also, we will be building many more form/subform pairs in the future - especially when we get into our Order Entry System. Don't miss this course.


 

 

Access 202 Outline
 
0. Introduction

1. Contact Button
Command button to open contacts for current customer
Find specific data to display
Viewing filtered results

2. Setting Values Across Two Forms
Using Forms!FormName!Field notation
Get CustomerID for Combo Box on ContactF
Setting the Tab Stop property
Skipping fields in the Tab Order

3. Sorted Customer Combo
Creating a sorted query with customer info
Manually editing the Row Source property
Modifying the Column Count property
Column Widths property
Column Heads property
SQL Primer
Modifying the row source with an SQL statement
List Rows and List Width properties

Lesson 4. Callbacks
Adding callback and date to table/form
Creating a callback query
Creating a callback form
Buttons on the form to open customer/contact

Lesson 5. Contacts Subform
Learning about subforms
Creating the contact subform
Using the subform control and wizard
Format > Send to Back
Hiding text boxes with the Visible property

Lesson 6. Miscellaneous
Editing our Main Menu
Replacing button images with text
Coloring text on buttons
Phone AutoDialer

Lesson 7. Review
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 202

Richard on 1/1/2007:  More command buttons, setting values across forms, sorted combo boxes, form/subforms, and more. 74 Minutes.
Richard Rost on 1/1/2007: ATTENTION ACCESS 2007 USERS: There is a BUG in the original version of Access which causes the command button to NOT work. See this post in my blog for details.
Richard on 1/2/2007: NOTE: If you add a new customer and IMMEDIATELY try to add a contact for him without first saving the customer record (moving off of it, or closing the customer form) you will find that the customer doesn't show up in the combo box on the contact form. This is because the customer record hasn't been saved to the table yet.

You can fix this problem by creating a REFRESH button using the command button wizard: Form Operations > Refresh Form Data. You just have to click on this button before trying to make a contact.

In my Access 300+ Advanced series when we get into programming, I'll teach you how you can add the refresh code (Me.Refresh) to the VBA of your OPEN CONTACTS button and do all of this in one step.

Josiah Whitman on 11/19/2007: pls. disregard the last question as you have subsequently answered it. Basic question now: I have long since lost my Access CD and the subform lesson in 202 requires it. I need the CD but also have three additional computers in my company that don't currently have Access. Is there an enterprise license for Access or what do your recommend. Thanks in advance for your advice. JW
Richard Rost on 11/21/2007: Access might want to install the subform wizard by prompting you to insert your Access CD, but you really don't the subform wizard. Just drop a subform on your form and set the Master and Child properties to link the subform to your main form. However, to answer your question, there is an Access Runtime edition available if you only need to USE an Access database (and not design with it). It depends on what version of Access you have.
nigel kearton on 2/10/2008: Richard,
I'm trying to set a default value (a name)on a form which is taken from a combo box on another form.

However, all I can get is the Unique ID, not the name.
Can You help?
Thanks,Nigel.

Richard Rost on 2/28/2008: If you just read the value of the combo box, you're getting column zero which is PROBABLY your ID field. If you need to get the name out of the second column, access it like this

=ComboBoxName.Column(1)

Remember the first column is column(0). We cover this in Access 302.

Judith Koester on 4/18/2008: Where deo you get the full name of the box? It appears on the video that you click on Input Mask and Forms!CustomerF!CustomerID pops up. That doesn't happen for me.
Glyn Hope on 4/19/2008: Reall can't get on with your new media player as it has no minimise button. Is there any way I can still access the videos through my normal media programme/
Richard Rost on 4/21/2008: Judith, I understand your confusion. This happens around time index 2:48. What you're seeing has nothing to do with the Input Mask field. That is a little screen tip that I placed in the video using my recording software. It just so happens that my cursor is blinking in the Input Mask property box. Instead of using NOTEPAD to illustrate the name, I just popped it on the screen using a "Screen Tip" from my video recording software. You just have to know the notation... Forms!FormName!FieldName. Now, you could use the Expression Builder to figure out the form name, but remembering the notation is just so much easier.
Richard Rost on 4/21/2008: You're correct. The new video player does not have a Minimize button. I should probably add one, eh? Yes, you can still access your videos the old fashioned way. If you click on the "EXTERNAL" checkbox in the player, the videos will open up in Windows Media Player, and you can resize, minimize, move them as you see fit. Or, you can always load up Windows Explorer or open My Computer and run the videos manually by going to your C:\Program Files\599CD\ folder.
Linda Gatlin on 4/29/2008: Hi Richard,
I need to have a data entry form that allows me to type in an employee's name/SSN and populate fields from the EmployeeT but store the data in TrainingT. I have approx 15,000 employees so a drop down list isn't practical and I want to keep my tables clean.
Thanks, Linda Gatlin

Richard Rost on 5/24/2008: Linda, the best thing to do would be to use a DLOOKUP statement then. This is a VBA function that requires a little bit of programming. It would allow you to type in the SSN, for example, and have everything else populated automagically. Now, I cover the DLOOKUP function in depth in my Access 302 class.

There is also a free mini-video on DLOOKUP in the Tips and Tricks section if you want to check that out first.

Hope this helps.

Anthony on 6/25/2008: I set up a form like you show in 202 that automatically displays a table of info that is related to the record that is open on another form (like your contact form in relation to the customer form). My question is, what if there is no related record (i.e. a record in ContactsT does not exist for the current customer). If a contact doesn't exist yet, I'd like it to begin a new contact record for that customer. Instead it seems to bug out a little and just show any customer's contact record.
Richard Rost on 6/26/2008: Anthony, it shouldn't "bug out" at all. If you click on a customer who has no contact records, you should just see a blank form.
Isaac Moses on 6/29/2008: When you delete a combo box do you delete all information you entered or automatically entered like SQL for this combo box or does that information reside in the database as garbage data?
Richard Rost on 7/1/2008: Isaac, when you delete a combo box, any SQL statement stored in the box's Control Source is deleted. Now, as with almost everything in computers, is that information REALLY deleted? Well, it's marked as DELETED, but as with files I'm sure a super-geek could resurrect it somehow. For the average user, however, it's gone.
Tonya on 4/2/2009: Is it possible to disable the ability to add a record to the subform?
Richard Rost on 4/2/2009: Tonya, you can set the AllowAdditions property of the subform to NO.
ADP on 7/13/2009: I have a subform on a main form. The subform has a button to open an external form. I would like to have a value from the subform be the default in the external form. I can not make this happen using the Default Value as shown in this lesson.
Richard Rost on 7/16/2009: Accessing the value of a field on a SUBFORM is tricky. Normally, getting a value off just one form is:

=Forms!FormNameF!FieldName

If you're dealing with a SUBFORM then it becomes:

=Forms!FormNameF!SubFormName.Form!FieldName

You can put this value in the Default Value property of your new form, OR it might be easier to go the other way. In the VBA code for the button that opens this new form, you might just want to set the value manually in the new form after opening it.

DoCmd.OpenForm "NewFormNameF"
Forms!NewFormNameF!FieldName = SomeValueOrField

If the button is IN the subform of which you speak, then you wouldn't need to use any crazy notation. I cover this when we get into the 300-level series of Access courses and start learning VBA.

 Carolyn Quinn on 7/24/2009: Forms: Why does the fields in the forms automatically come in so long? I hate that. I want to resize them - i am sure i can do this somewhere in the properties - can you show me? Thanks
Richard Rost on 7/25/2009: Carolyn, I don't know of any way to change the default length of a new textbox when you add it to a form. Sorry! I'd love to know if you find it though.
David Leech on 9/25/2009: Hmm is it just me or does Access 2003 not have the option to open a form and find specific data through the command button wizard... seems to skip that step
Richard Rost on 9/25/2009: David, it has that feature. Form Operations > Open Form. NEXT. Pick your form. NEXT. "Open the form and find specific data to display" on the next screen.
Ghassan Baghdadi on 10/21/2009: Sir, I was trying so many times to open a form for a specific customer but it didn’t work with me. I don’t know what is the problem. I followed what you have said but nothing changed. So, what is your recommendation.
Richard Rost on 10/21/2009: Ghassan, I would need to know exactly what you're doing so I could tell you why it's not working. Are you following the lesson EXACTLY as it appears in the video? Are you using MY database or your own database? What version of Access are you using? Give me a step-by-step on exactly what you're doing. I need more information in order to help you.
Ghassan on 10/22/2009: Mr. Richard,
1-I was trying to make a contact button in the customer Form but it didn't work. Once I click on the button, filtration doesn't work.
2-Yes I am following what you are saying exactly in the lesson.
3-I am not using your database because I don't have it. I searched for it but I think it didn't download when I downloaded the lessons.
4-I am using Access 2007. Is there any difference between what you are using and 2007?
5-Step by Step ( I am doing now HR Database)
-I made 2 forms (Employee form) and (Attendance Form)
-I need to connect the Attendance Form with a specific Employee like what you did between (Customer and Contact)
-I have opened my Employee Form in Design View and make a button (Form Operation > Open Form > EmployeeF> Pick Open the form and find a specific data to display> ID <> ID and then Finish. Save and open the form, click on the button I did, but didn't work.
-Also, Sometimes, I got empty table in the Matching Field in the Left Hand Side. I dont Know Why?
That What I was doing and Thank you for your great teaching.
Ghassan

Richard Rost on 10/24/2009: Ghassan, it turns out that this is a bug in Access. I just posted about it in my blog here.
Judith Koester on 12/16/2009: I am trying to make a combo box with state abbreviations. I get a message that "no more than selected". I created a table with the 50 state abbreviations. That just doesn't sound right to me. Is this an evil thing of 2007?, or what am I doing wrong? Seems pretty straight forward to me.
Judith Koester on 12/16/2009: After trying the state abbreviations combo box about 20 times and asking for your help, I ditched the table and re-did it. My combo is working OK now. Sorry, but am not sure what was going on.
Richard Rost on 12/20/2009: Glad you got it working. That's always my 2nd rule of thumb: delete the object and re-create it. The 1st rule is to reboot your PC. If that doesn't work, recreate it.
Chris on 3/12/2010: I noticed in Access 201 that you corrupted one of your contact records (ContactID = 1). I also see this carrying through to this lesson (see 8:25). Is this purposeful - i.e. a future lesson we'll see data integrity issues come-up?

Reply from Richard Rost:

You know, I honestly don't remember. :) This lesson is already over seven years old, and I can't recall if I used that for other nefarious purposes in later videos or not. Anyone else recall?

Sam on 6/2/2010: In your video the full address just pops up by itself once you selected the name of the field "!Form!Customer!CustID". I can't get the full address to pop up.
How do you do it?

sam

Sam

Judith Koester on 7/21/2010: Trouble again. I just noticed when I use the drop down box for state -- California for instance, instead of CA my query says 5. ??? I'm sure it's something simple, but I'm stumped.

Reply from Richard Rost:

You probably didn't HIDE your KEY COLUMN. Remember the very first column in the box has the ID in it. The wizard gives you an option to hide it, which essentially makes the column's width equal to zero.

 cyber_hawk on 9/5/2010: I've made a student database having Sr_No as student table Primary Key, now my question
1) report in which i give Sr_No as (Between [from] And [To]
2) Each Sr_No on new page, not complete range on a single page

 Dale Sams on 9/19/2010: Is there anyway to zoom the page in the design view? It might be easier to align & size the various elements if you could get a little closer.

Thanks

Reply from Richard Rost:

Nope. Sorry. I wish you could.

 PA on 9/30/2010: In the minute 3:02 you get in a pop up the complete / full true name for a text box in a form (Example: Forms!CustomerF!CustomerID). My question is, how do you make visible that full name in that pop-up on the screen?
Fernando Rodriguez on 10/28/2010: in 2010 access when i make a form in design view and then adjust the hight it never stays the way i adjusted it when i go to preview it

Reply from Richard Rost:

Try closing the form and reopening it. Sometimes that's what you have to do to see the true saved dimensions of a form. Just switching from design view back to form view doesn't cut it.

A. David P on 10/28/2010: I have Access corses up through 329 which I have been working my way through. If you have a table that contains a persons work history for example, from say Associate, Senior Associate, Jr. VP, Sr. VP, President with dates in each position. Is there a course that covers how to make the latest position the "active" position? Thanks, Dave. Great courses BTW, I've never seen better.

Reply from Richard Rost:

I'm assuming you have a start date for each position? Just use the DMAX function to find the largest start date, then DLOOKUP the ID from that record for that employee. That will tell you what their current position is.

Or... even easier, just make an IsActive field in that position table and have the user check the current position ON.

Van Luong on 11/18/2010: Hello Richard, after viewing this lesson, I tried to replicate the lesson by entering command in the field of default value, all I am getting is the first CustomerID in my contact form. Could you please advise what I have done wrong. Thank you

Reply from Richard Rost:

I'm sorry, I don't understand what the problem is. Can you be a little more specific, please?

Van Luong on 11/18/2010: In Access 202 – Lesson #3 (Default Value), the purpose of this lesson is to have contact form open up, (after entering the following expression “=Forms!CustomerF!CustomerID” in the default value field of the customer combo box). When I open the contact form name should match with customer form. My contact form opens up with the first contact (ie ContactID #1). Please help! Thx

Reply from Richard Rost:

The purpose of the default value is to set the name to the currently open customer when you add NEW CONTACTS. Lesson one covers making a button on your customer form to click and open the contacts for the currently selected customer.

 Lou on 1/17/2011: Mr. Rost,

I am in Access 202 Lesson 2: "Set Values Across Froms". The time is 2 minutes and 42 seconds. You are discussing the "full name" of the control source. A pop up box on the screen show the full name as Forms!CustomerF!CustomerID! but there is no explanation of how you produced the pop up box. Could you please tell me how this is done.

Thank you.

Reply from Richard Rost:

See the user comments in this forum. I answered this question before. That's not an Access popup. It's just a NOTEPAD image I pulled up to show you the proper nomenclature. :)

Judith Koester on 5/3/2011: Trying to put a command button on a form but when I get to the screen to select fields and click
<-> button, there is nothing in the left form (in this case, my student form). I have info in there, but where is it now?

Reply from Richard Rost:

Judith, I don't understand the problem. Can you elaborate a little more please?

Judith Koester on 5/3/2011: I downloaded SP2 and still have the same problem trying to add the button -- no information on the first form listed to link to the second form. I have Windows 7. Is there something new re that?
Judith Koester on 5/4/2011: I am trying to create a button to link 2 forms (student form and finance form). After I select "open the form and find specific data to display", there are no fields on the left form (studentF) to link to the right form (financeF). I am using Access 2007.

Reply from Richard Rost:

OK. When you open both forms individually, do you see data in them? Make sure you have the forms properly built, and bound to their tables. What is the FIELD that links these tables together? StudentID?

Judith Koester on 5/4/2011: Both forms have the data in them. I have StudentID as the foreign key in Finances and the forms are linked.

Reply from Richard Rost:

Then you should be able to create a button using the command button wizard that will allow you to "open a form and find specific records to display" linked on that StudentID. I'd have to see your database to tell you exactly what's wrong. If you'd like, send it to me at richard.rost@amicron.com.

Judith Koester on 5/4/2011: Okay, this is weird. I decided to try the flip and create the button in the FinanceF to StudentF. All the info appears! and the button works.

Reply from Richard Rost:

That's very strange. I've only seen this happen if you have a form where the form itself or the underlying query relies on the data from the other form existing... like if you refer to Forms!StudentF!StudentID from INSIDE of your FinanceF or its query... which you shouldn't know how to do yet if you're only on Access 202.

Fabian Sanchez on 5/4/2011: Richard I'm trying to create a report log that will grey out the entries once the date, starttime and endtime is past. i.e.
startdate = 4 May 11 and starttime is 10:30 endtime 11:00 once the system clock goes over 11:00 I want that entry on my report to be greyed out. what's the best way to implement this?
Thanks

Reply from Richard Rost:

I would use a little bit of VBA code in the Build Event for the section. Something like:

If StartTime < INSERT_TIME_HERE Then
MyTextBox.BackColor = COLOR_CODE_FOR_GRAY
else
MyTextBox.BackColor = COLOR_CODE_FOR_WHITE
end if

Of course, you'll have to insert the right color codes and variables/field names in there, but that should get you started. I cover this in one of my lessons. Not sure which one. If you need it, let me know and I'll figure it out. :)

 Orlando on 6/17/2011: Hi Richard,
When I add a header and footer (with my Labels in the header and command button in the footer) I don't see them when I run or preview the form. And the header labels are set to "visible" yes.
Why can't I see my header or footer. (Access 2002-3)

Gurjeet Kaur on 8/14/2011: hi Richard
I am using MS 2007. I download SP2 as u suggested and it solved my problem of showing specific contact on Contact form. Thanks a lot for that. But now there is another problem. As I set value across forms, putting Forms!CustomerF!CustomerID! in default value of ContactF, it does not work, the value don't show up. I am following ur lessons and doing everything in exact manner u tell us to do. I would really appreciate if u answer my question personally. Thanku

 Wendell Satterwhite on 1/31/2012: If you made an entry form how could you set a field to default to the previous record field entry in the event occasionally that field would be a duplication for several records.
Rick on 9/13/2012: This is gold:
"Richard Rost on 2/28/2008: If you just read the value of the combo box, you're getting column zero which is PROBABLY your ID field. If you need to get the name out of the second column, access it like this =ComboBoxName.Column(1) Remember the first column is column(0). We cover this in Access 302."

Exactly what I wanted to know too ! Now when I enter a Town, the Area box is automatically recorded from my TownAreaT.

Thank you Thank you Thank you

Reply from Richard Rost:

Glad I could help. :)

Vannak Hou on 12/16/2012: Time Index between 14:35 and 14:55; can you just bring in the CompanyName and the FullName without the CustomerID field or is that required? I'm saying to eliminate the hidden process of CustomerID. So this in case, we would have two fields (CompanyName and FullName).

Reply from Richard Rost:

You could, but then you wouldn't be able to store the ID value or use it anywhere... which almost makes it useless. You want the CustomerID so you know which customer the Contact belongs to.

Vannak Hou on 12/17/2012: How were you able to show the Forms!CustomerF!Customer!D on the property? My Access 2010 doesn't show that at all.

Reply from Richard Rost:

What is the time index in the lesson?

Vannak Hou on 12/19/2012: How did you have Access 2003 show Forms!CustomerF!CustomerID! on the "All" tab? Time 02:45-02:50. I'm using 2010.

Reply from Richard Rost:

That's just some POPUP TEXT that I was able to put in the video using my recording software. It's not part of Access. You can use the Expression Builder to get that name, but I wanted to show you how to do it manually. I'll be covering the Expression Builder in more detail in an upcoming class.

Vannak Hou on 12/20/2012: Here is the actual SQL= to the "CustomerForComboQ"

SELECT CustomerID, CompanyName, FirstName & ", " & LastName FROM CustomerT ORDER BY CompanyName;

Make sure to change the Column Count=3 NOT 4.

Vannak Hou on 12/22/2012: Richard,

The solution/answer to his question: it sounds like it needs a VBA Script to do this right? Just curious ... This is in reference to the forum discussion below:


Microsoft Access 202
Fabian Sanchez on 5/4/2011: Richard I'm trying to create a report log that will grey out the entries once the date, starttime and endtime is past. i.e. startdate = 4 May 11 and starttime is 10:30 endtime 11:00 once the system clock goes over 11:00 I want that entry on my report to be greyed out. what's the best way to implement this? Thanks

Reply from Richard Rost:

Yes, you could use VBA to do this... or even more easily just use CONDITIONAL FORMATTING.

Vannak Hou on 12/22/2012: Time Index: 10:00. I guess it takes some practice to do the for customization or aesthetic look,huh. It has taken around 45 minutes so far. Before it took me at least 1.5 hours to do. It's getting better each time.

Reply from Richard Rost:

Practice makes [as close as you can possibly get to] perfect. :)

Vannak on 12/22/2012: I have been reviewing(watching) the videos in the 201 and 202 for over 12 times already because there are things that I can't do not understand but I am being patience as much as possible because each time I watch over and over I remember things; things are making more sense. Thanks Richard!
vannak on 12/22/2012: Yep, I noticed that too. Thanks again. I have been playing around with the property quiet a bit and see some differences when I changed from this and that.
Vannak on 12/22/2012: Wow.. awesome! I like the links. Checked already!
Vannak H on 12/22/2012: Yeah, the way you do it, looks easy but when I get my hands on a couple of weeks ago, it took me forever to do design the forms and the reports. I'm getting better than before.
vannak on 12/23/2012: Hey, I think I did that one time and got into a problem then I was able to resolve. Design View on ContactF, double Click on CustomerID Textbox (right side) not the Label box, Go down (way down) to Default Value and this in again =[Forms]![CustomerF]![CustomerID]. Save, Close It, and retry.
Vannak Hou on 12/28/2012: Question: I have the Excel file which has the full name in one column; for example, Vannak Hou instead of in First=Vannak Last=Hou in two different column. So how do I import that file into Access 2010's field of First and Last name? Instead of "manually" typing all the 100 rows to Access file.

Reply from Richard Rost:

There are a couple of ways you can do this. In Excel, you can select the column, go to DATA > TEXT TO COLUMNS > DELIMITED > Space Character. That will generally work for MOST of your names, assuming they all have just ONE space in there... and don't include middle names, middle initials, compound names (like Martin Van Buren). Then just import THAT into Access. I cover this in Excel Expert 1 in more detail.

There are other techniques that you can use to separate names if things are more complex. See THIS and THIS.

Svend Christensen on 1/9/2013: Hi Rich.

I am using Access 2007 for my 2003 Access course. Everything has been working well upto now.

However I have encountered problems with the properties for formfooters.
I am missing the opportunity to make headers and footers visible in form view, but it looks fine in design view.
It means that the buttons Open Customer and Open Contact are invisible in Form View.

202 lesson 4 Callback Time index 10:20

I have then downloaded your lesson 202 with extension .mdb saved and converted it Access 2007 with extension .accdb. The Formfooter properties consist 19 rows in this version and my installed 2007 Access does it only consist of 10 rows.
I can turn form headers and footer off and on in the converted version.

The opportunity to set headers and to footers to always visible is simply missing in my Access properties.


How do I solve this problem?

Thank you in advance

Best regards,
Svend Christensen



Reply from Richard Rost:

Yes, you should be able to see form headers and footers. Right-click on the DETAIL section bar and you'll see the option there. If you're using the sample database downloads from the web site, many times you'll find that it's BETTER to start with a blank 2007 database shell and IMPORT the objects from my 2003 databases.

Svend C on 1/10/2013: Hi Rich.
Thanks for your quick replay.
It is in fact my own 2007 database I have buildt doing the course.

In this database consist properties for header and footer of 10 rows while properties for the detail section consist of 19 rows

Brgds,
Svend

Reply from Richard Rost:

I don't understand the problem. Can you email me a screen shot of what you're talking about? Send it to amicron@gmail.com with an explanation. Some things are just easier to help you with if I can SEE them.

Sandra Bischler on 1/11/2013: Hi, I can not find a copy of your database at C:\Program Files\599CD\Access 202\Help. This file does not exist on my computer.....

Reply from Richard Rost:

I stopped including the databases with the video packages to save space. This is shown in the STUDENT FORUM notes for each class. The sample databases have all been moved to the Student Databases page. Always read through the Student Forum notes for each lesson - there's lots of valuable info in there. Notes on the course overall are included in LESSON 0 - the intro lesson that nobody ever watches. :)

James Miller on 1/26/2013: Hi Richard. I cannot see the right side of the form. My field labels for Must Call Back and Customer ID will not go up into the form header. And I never see your right side after you bring up the labels to the header. Did you just delete the other fields?
James Miller on 1/26/2013: Never mind. I figured it out. You have to select the label itself and cut it and paste it up there...
Deisy Enriquez on 4/30/2013: What if you wanted the subform to display the information for a single date. For example, if for whatever reason most customers had multiple daily contacts and you wanted to display all the contacts that happened on any given date?

Reply from Richard Rost:

You could use a PARAMETER QUERY to ask the user for a date value, or you could get it from a FORM FIELD.

Deisy Enriquez on 4/30/2013: Ok I will try that. Thank you
john Edwards on 3/5/2014: Hi Richard Magic stuff. I am at 3.25 on the Access 202, video 2. You don't show how to open up the Forms!CustomerF!CustomerID. It just appears. Can you tell me how to do it please.
john Edwards on 3/5/2014: Had a look at the answer from Judith. 2.48 You say it comes from the CustomerF form. I tried clicking on the header and properties but it does not show. So I am doing something wrong. Can you give me the routine to open the Forms!customers!customerID

john Edwards on 3/5/2014: Ops sorry richard. My query was in Access 202, video 2 time 2.48
Nicholas S on 4/4/2015: Hi, I noticed this is where a Dial button is added to get the auto dialler to call the customer, this of course uses the old style modem cards installed in you PC. Is it possible to somehow get this to work in Access 2013 using a IP telephone / VoIP?
Kind Regards

Reply from Alex Hedley:

You could try hooking into Lync or Skype and use their API

John Mason on 12/25/2015: Richard,I have a combo box that links to a table by the ID field. I would like to pull the values from other fields. Which course or courses do I need to study to learn how to do this?. I am currently using Access 16.

Reply from Alex Hedley:

Sounds like the job for DLOOKUP.

Expert 10
Expert 22
Expert 29

Tip | Tip | Glossary

 

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