Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Access 222 Handbook
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.






Microsoft Access 222
Course Handbook Supplement

By Richard Rost


Published By
Amicron Computing
PO Box 1308, Amherst NY 14226 USA
www.599cd.com


First Printing 5/5/2006
Copyright 2006 by Amicron Computing
All Rights Reserved


Welcome

Welcome to Microsoft Access 222: Advanced Queries 3.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 222. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.

We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.


Table of Contents

Welcome 2
Table of Contents 2
Introduction 3
Lesson 1. Intro to Action Queries 4
Lesson 2: Update Query 6
Lesson 3: Another Update Query 11
Lesson 4: More Update Queries 16
Lesson 5: What!? Another Update Query? 20
Lesson 6: Append Query 26
Lesson 7: Delete Query 30
Lesson 8: Make Table Query 36
Lesson 9: Project Automated Queries 40
Review 50




Introduction

Welcome to Microsoft Access 222: Advanced Queries 3, brought to you by 599CD.com. I am your instructor, Richard Rost.





Objectives for today’s class:

· Update Queries
· Append Queries
· Make Tables
· Delete Queries
· Automating Queries With a Macro

The class follows Microsoft Access 221. We’ll be using the same database that we’ve been using since Microsoft Access 101. I strongly recommend that you watch all previous classes before you start with this class.

In this class we will be using Microsoft Access XP (2002) and Windows XP, however this course is valid for any version of Windows, you should have no problems following this course if you are using Access 97, 2000 or 2003.

If you have not been building your database yourself since Access 101, you can get a copy of my database file at the website www.AccessLearningZone.com/Access/222 . If you purchased this course on CD-ROM or downloaded it from our website, you’ll find that it’s been installed in your help folder.

Lesson 1. Intro to Action Queries

Let’s begin today by talking about Action Queries. What are Action Queries? Well, every query that we’ve learned to work with so far has been designed to show us our data in different ways. For example, we can make a simple select query that’ll show us all the customers from New York sorted by last name. That’s an example of a select query. But we really haven’t done anything so far that allows us to change data. That’s what Action Queries do. Action Queries allow us to actually modify and manipulate the data in our tables. Hence the term, Action. We can take an action on our data.

Now there are different types of Action Queries that do different things based on our goals. Let’s take a look at what different types of Action Queries there are.

First, we have an Update Query. An Update Query is used to take data in your table and change it to something else. You want to update the information. For example, let’s say you want to go through your customer table, and change every instance of the customer name “Smith” to “Jones.“ You can change data on any criteria that you select.

Next, we have an Append Query. An Append Query is good to take a certain set of records and add it to the end of a table. This is perfect for archiving records for example.

The next type is Delete Query. A Delete Query is where we’ll take a bunch of records, and we’ll delete them off of a table based on a particular criterion. So let’s say we want to take all of the contacts that are a year old, we want to pull them off of a table, and put them on a different table. First we’ll append them onto a history table, then we’ll delete them.

The last kind of an Action Query is the Make Table Query, where you can actually make a brand new table based on the data from your query. Let’s say you want to create a table that’s got a mailing list of a specific set of customers. You can use a Make Table Query, which will generate a mailing list that you can then use.

Those are the four different types of Action Queries. We can change records, add records, delete records, or we can make brand new records to a table – all using queries.

BACK UP YOUR DATABASE BEFORE RUNNING ANY ACTION QUERIES!!!

If you’ve got data in a database that you don’t want to get “toasted, lost, fried,” whatever you want to call it - back up your database before running any action queries!!! Action Queries will permanently change the data in your tables and there is no undo most of the time.












Here are my database files! I’m going to make a copy of this by pressing Control+C...




And then press Control+V to paste. That’s all you’ve got to do to copy your database to make a backup copy.




In addition to your database, I want you to also back up your tables.

BACK UP YOUR TABLES BEFORE RUNNING ANY ACTION QUERIES!!!

If you’re going to modify your customer table, back it up before you run that query. Just make a quick copy of the customer table the same way I did with the whole file.

Lesson 2: Update Query

Let’s begin today by creating an Update Query. Now again, an Update Query is used to change information in our tables. The benefit of using an Update Query versus a Find and Replace function, is that an Update Query can be saved so that you can do it over and over again. – Especially if you have other people in your office that don’t know Access to well and you don’t want them tinkering with Find and Replace (which can be dangerous). Instead, you can make them their own little Update Query.

Let’s load up our database. Here I am in my PC Resale database.




Let’s open up our CustomerT table. Here’s some information that I want to change. Let’s say the company name, “XYZ Corp” was just purchased. And they’re now, “ADF Inc.” So I need to change all the instances of “XYZ Corp” to “ADF Inc.”












Close the CustomerT table and go to Queries. We’re going to create a new query and make it look like a regular select query. So go to New – and select Design View. We just need our CustomerT table.




Now I want to change the company name. So all I need to bring into this query is the company name.




Let’s get the records that I want to change before we even think about turning this into an Update Query. What’s my criteria? Well the company name has to be “XYZ Corp”.




If you want, you can bring in other fields, like the CustomerID or State field. Now, how do I turn this into an Update Query?






On the toolbar, there’s a little box. Drop it down and select the Update Query function.




Notice we’ve got some changes here. We have a new row named “Update to:” In here, you simply put in what you want this field to change to.




I want “XYZ Corp” to be “ADF Inc.”













Before I run this, I want to back up my tables! If I go ahead and run this guy, it’s going to go ahead and do its stuff. So before I run it, I’m going to go to my Tables and back up my CustomerT table. I’m going to select it, press Control+C (copy), Control+V (paste), and call it CustomerT BACKUP. Make sure you have Structure and Data selected before you click OK.




Now you can see the backup copy is saved right next to the first copy.




Okay so I’m ready to run my query now. I’m going to click the little Run button up top.




I get a message! Once I click Yes, I can’t undo this stuff to reverse the changes. I’ll say yes.













And now, if I go back and take a look at my CustomerT table, I can see the changes.




So that’s how the Update Query works. Let’s save this Update Query as Update1Q and go to our Queries list. Scroll down and notice how its icon looks different. Your Action Queries will look different than the standard select queries in your list.



Lesson 3: Another Update Query

We built a simple Update Query in the last lesson,. In this lesson, let’s build one that’s a little bit more complicated. For this query, we’re going to use the CustomerT table again. This time, I want to change “AFD Inc.” back to “XYZ Corp”. But – I only want to do it for companies in New York. Now for practice, let’s build a new query in Design View using our CustomerT table.

I’m going to bring in CompanyName, and then State. Now let’s make this look like a select query with the information that we want in it. The criteria for the CompanyName is “ADF Inc.” State should be “NY”.




Let’s now change this into an Update Query. Select Update Query from the toolbar. Notice the Update To: row. This row says, “What do you want to change?”




Remember the View button on the toolbar? We can use this button to flip back and forth between Design View and Regular View.



If I just click this View button, it gives me a preview of the data that’s going to change.




To run the query, and actually make the changes, hit the run button. When I go back to my table, and take a look, I’ve got two “XYZ Corp’s” and they’re not from New York.




Let’s close this but we don’t need to save the changes.

Here’s another one. Remember our Tasks subsystem that we made?


















We click on it, and we get a list of tasks? Look at the dates – we built this in November of 2004! Remember how we did this? It’s been a while! Why don’t we update all of these to today? We can do that with a simple Update Query.




Let’s go back to our tables and find out TaskT table.




Why don’t we say that if a task in the table isn’t completed, change the due date to today. How’s that sound? That shouldn’t be too hard to do, right?








Let’s close that and make a new query in Design View using the TaskT table. We’re going to change the DueDate and we need to know whether or not it’s completed. The criteria is for the Completed field is going to be false (or you could put No in there, or 0.)




Now let’s change this to an Update Query from the toolbar. I want to update DueDate to today’s date.




Let’s run it, find our TaskT table, and go take a peek. The completed tasks have been left alone. Where Completed was false, update DueDate to today’s date using the Date() function.




I’m going to save this query as UpdateChangeTaskDateQ. Now when I go back to my Task System, I can see all my due dates are for today.




Lesson 4: More Update Queries

Update queries are lots of fun, aren’t they? Let’s make some more. Let’s open up our CustomerT table again. Let’s say someone typed in some states that aren’t in all caps. But we want to change this so that all states are in all caps. An Update Query would fix this lickety split.




So let’s close this and go to our queries. We’ll make a new query in Design View using the CustomerT table. At this point, all I need is the State field.




Let’s change this query to an Update Query from the toolbar. I want to update the State field to uppercase. Remember – whenever you refer to a field inside of a function, like Ucase() for example, put brackets around the field name.






Let’s go ahead and run that query now and agree to the update. When we take a look at what it did, we see that all the states are in capital letters. The Ucase() function changes text to its uppercase version.




You can do that with anything that you want to, like FirstName and LastName. Let’s give it a try. Back in Design View, get rid of the State field and add the FirstName and LastName fields. In the Update To: row, enter Ucase([FirstName]) and Ucase([LastName]).




Run it and agree to the update. When you take a look at the table, you’ll see the following.



And yes in case you’re wondering, there is a Lcase() function to convert text to lower case.




When you run it and agree to the update, you’ll see the following after opening the CustomerT table.




There isn’t a “proper case” or Pcase() function. But there is a workaround. Proper case is where the first letter is capitalized and the rest are not. If you have a long string of words, it would be the first letter of each word capitalized. You can use the string convert function, or Strconv(). That will convert the first letter of each name from lowercase to uppercase.


















Run the query and agree to the update. Open up your CustomerT table, and you will see the names converted to proper case.




String convert is a Visual Basic function. 1 converts text to upper case, 2 converts text to lower case, and 3 converts text to proper case.

Let’s save this one as UpdateStringConvertQ.

Lesson 5: What!? Another Update Query?

Back in our CustomerT table, we put an input mask on our phone number field but we never put one on our fax number field. I want to say, “If the fax number is exactly seven digits long, and a customer is from New York, assume a user forgot to put the area code on it and put 716 as its area code.” Let’s give that a try.

Let’s make a new query in Design View using the CustomerT table.




We need to add in the Fax number and the State. In the Criteria row, I want to put in New York in the State field, and the length of the fax number in the Fax field.




You can also make a new field for it. I’ll call this new field FaxLength: Len([Fax]).










When I run it, Access tells me the length of the Fax number field.




But I want the criteria to be just seven.




When I run that, Access shows me the fax numbers in New York that are seven digits long.




Now let’s change this to an Update Query from the toolbar. I want to update the Fax field to “716” & [Fax].












Run it and agree to the update. Then open your CustomerT table to see the following. The New York fax numbers were reformatted to include the area code.




Let’s save this query as UpdateFax7DigitQ.

Now let’s say that a group of your customers have one Internet provider that they’re working with and that Internet provider just got bought. So everyone at xyz.com now as to become the same thing at the new domain. Now our customer table doesn’t have an email field. So let’s modify our CustomerT table and add an email address field.

Come down to the bottom and add an email field as text. Give it a field size of 100.





















Let’s save it and go to Form View. We need to put some email addresses in there. But here’s the problem – xyzcorp.com has been purchased. We need to change the xyzcorp.com email addresses to adfinc.net addresses.




Let’s close this and save our changes. We’re going to create a new query in Design View using our CustomerT table. And we need our email address field. The email addresses have to include xyzcorp.com somewhere in them, so we need to enter LIKE “*xyzcorp.com*” as it’s criteria.




Now I have to try to isolate the beginning part of the email address and I can do that because it’s everything before the @ symbol. I need to find the position of the @ symbol and I can use the INSTR() function to do that.

I’m going to create a new field called AtPos: InStr([Email],”@”).







When we run it, we see the following. I get an 11 and a 13. The @ symbol is at the 11th position in the first email address, and it’s at the 13th position in the second email address.




Now that I know where the @ symbol is, I can grab everything before it using the Left() function. The Left() function says, “Give me everything to the left of the string.” So I’m going to make a new field called Username: Left({Email],[AtPos]). Let’s see if it works.




Click run and look – perfect!




Now that I know the username and I know what the new domain name has to be, I need to make a new field called NewEmail: UserName & “adf.net”.




If I run it, I can see the new email addresses, but I don’t want to put it there. I want to change the email address to the new one. I need the NewEmail: UserName & “adf.net” part to update my data.






So let’s save this query as CustomerWithEmailUsernameQ. We’ll need to make a new query in Design View using the CustomerWithEmailUsernameQ query. Now this query already has everything figured out for us! So by the time it runs, it should be easy to change. Bring in the Email address and Username.




Let’s make an Update Query now. I should be able to update the Email field to [Username] & “adf.net”.




Let’s run it and agree to the update. When we open up our CustomerT table, we can see the new email addresses!




Why did it work this way? Because the first query ran, figured out what the username was, and then was able to feed it to the second query and make the change. By the time the second query ran, it already figured what the username was. If I try to do it all in one query, it wouldn’t know what to make the change to. Let’s save this one as UpdateChangeEmailDomainsQ.


Lesson 6: Append Query

If you’re wondering so far why I spent the majority of the time on Update Queries, it’s because out of the four different types of Action Queries, you’re probably going to spend 80% of your time working with Update Queries. They are used more frequently than anything else is and they’re so versatile.

Append Queries, again, are very handy for adding some records onto an existing table. The example I like to use is archiving data. For example, we’ve got a bunch of records in our Task and Contact system that we don’t necessarily need to keep permanently. Every time we talk to someone, we put him or her in our ContactT table. Well over time, if you’ve got lots of different customers, you’ll eventually get thousands and thousands of records. Eventually, your contact table will get very big and when tables get big, they tend to get slow. So you might only want to keep let’s say, the last year or two worth of contacts.

Before we do anything, let’s make a copy of our ContactT table. Copy and paste it as ContactArchiveT with the Structure Only option selected.




When I open up the ContactArchiveT table, it’s got the same structure as the ContactT table, but no data.




Now I want a query that’s going to take all the stuff in the ContactT table that’s older than a year and append it onto the end of the ContactArchiveT table. But first we need to make a back up copy of the ContactT table.








Next, we can make a new query in Design View using the ContactT table. I want to make a list of contacts that are more than a year old. So let’s bring in the DateTime field. What’s my criteria? < Date()-356.




Run it. All the dates should be more than a year old. And they are!





Now I want to take these records and I want to append them onto that other table. So let’s change this to an Append Query from the toolbar.










We’re asked where we want to append to. I want to add them to the ContactArchiveT table.




Let’s also bring in the * field (that’s all the records, right?)




Now the DateTime field is going to be appended to the DateTime field in the other table. Let’s run it and see what happens. Agree to the append. Let’s take a peek at the ContactArchiveT table. Notice that the ContactIDs are different for this database. That’s okay. These ContactIDs just uniquely identify each record. We could change this. Let’s get rid of these records.













Let’s change it so that in the Archive table, the ContactID field isn’t an AutoNumber. This will be a regular number.




And let’s turn the key field off.




Save it, close the table, and run it. Agree to the append and let’s open up the ContactArchiveT table see what happened. Look at that. It brought over the ContactIDs from the other table.




So that’s how the Append Query works. Now, if we run this a second time and agree to the append, the ContactArchiveT will show two copies of everything. If you run the query more than once, you’re going to get all those records again. Now we need to take these records out of the other table. Append them here – delete them out of the other. So let’s close this now and save it as AppendContactQ.

Lesson 7: Delete Query

In the last lesson, we used an Append Query to take some customer records that were old and append them onto a archive table. Now we need to delete those records out of the original contact table. Now before we run a Delete Query, you’d better make sure your stuff is backed up! So back up your ContactT table again.

Let’s make that Delete Query now. Make a new query in Design View using the ContactT table. Bring in the DateTime field. This field has to be more than a year old.




We need to make this a Delete Query from the toolbar.




Unlike the Append Query, where you’ve got to make sure that you bring in all the fields to append information, the Delete Query says, “Give me one of the criteria for any field and I’ll nuke the whole record based on that criteria.” Below, it says, “Delete the contacts from the contact table where the DateTime is less than Date()-365. Let’s save it as DeleteContactQ.










Let’s run it and agree to the deletion. Close this window and open up the ContactT table. The contact table has all the new stuff in it.




So that’s that – delete queries are pretty straightforward. But I want to talk for a second about that cascade delete that we learned about earlier when we talked about referential integrity. If I’ve got a customer named Anna Picore and she’s got a bunch of contacts in the system – and I come along and delete Anna, all her contacts are still in the system. If I’ve got referential integrity set up in the system with cascade deletes, all her contacts will be deleted too.

I’m going to back up my customer table again. Now let’s make a relationship. Select Relationships from the Tools menu. Click on the Add Table button and bring in the CustomerT and ContactT table.














Drag CustomerID in the CustomerT table to CustomerID in the ContactT table. Check Enforce Referential Integrity, Cascade Update Related Fields, and Cascade Delete Related Records in the Relationships window.




Click Create so that they’re globally linked together in the database.




Save it and close it. Now when I open the CustomerT table, I can click a plus sign next to a customer and see all the contacts for that person.





If I delete Anna Picore, all of her contacts should also be gone. Let’s take a peek into the ContactT table. Look at that – all her contacts are gone. I can’t undo that.





I’m going to now show you that if I delete all the customers from California, all their contacts should also be deleted. I’m going to make a new query in Design View. Since I’m going to delete customers, I’m bringing in the CustomerT table. Next, I’ll bring in the State field and set it equal to California.




I’ll change this to a Delete Query from the toolbar. Access is going to then delete customers from California.












Run it and agree to the deletion. When I open my CustomerT table, all my Californians are gone.




Are their contacts gone? Yes they are!




Did you see what I was trying to show you? Since I’ve got referential integrity set up where there’s a relationship between customers and contacts, and I’ve got cascade delete on – when I deleted the California customers, all of their customers were deleted as well. That’s what the cascade updates and deletes are for.

Now that we’re done with this, let’s close it and turn off the relationship (go to Tools – Relationships). We need to restore our backups now.

Here’s a situation: I want to give you an example of where you would use the From function. Make a new query in Design View using the CustomerT and Contact tables. With no relationship setup, again, you want to delete your Californian’s contacts. So bring in State with a criteria of California.








Let’s change this to a delete query and then bring in the star field of the ContactT table.




This will delete the contacts from the customers that are in California. When I run it, and agree to the deletion, the contact is gone from the ContactT table, but the customer is still in the CustomerT table. Let’s save this query as DeleteMultipleTableQ.

Lesson 8: Make Table Query

Continuing our tour of Actin Queries, the Make Table Query is pretty straightforward. It basically says, “Make me a new table, based on these criteria.” The perfect example for making a Make Table Query is simply making a backup copy of another table. We’ve had to make backup copies of the CustomerT table in the past, so let’s make a query that does that for us.

Create a new query in Design View using the CustomerT table. Let’s change this to a Make Table Query from the toolbar.




Now it’s going to ask you for a new table name. Let’s call this the Customer AUTOBACKUP.




Since I want to back up everything, I’m going to bring in the star field.






Let’s save this as MakeTableCustomerAutoBackupQ. I’m going to close this and run it from the database window.




Agree to the Make Table and let’s see what we got. Open it up and there it is! There’s an exact copy of our CustomerT table.




Let’s run it again and see what happens. Well when I try to do that, I’m warned that the existing table will be deleted before I run the query. If that table exists, the Make Table Query isn’t going to append records, it’s going to wipe out what’s there and create a new table. I’ll click Yes and when I open it, I just see another copy.

If you want to add records to your autobackup, then you need to use an Append Query.

As another example, let’s make a new query in Design View with the CustomerT table. Bring in the Star where the State is New York. Hide the State field.



Let’s turn this into a Make Table Query from the toolbar and call it CustTEST. Run it and agree to the Make Table. Now what I just did was make a new table with only those four records in it.




So you can use a Make Table Query that’s a subset of your other one. Let’s link in our SalesLogT table.




Let’s bring in the UnitPrice greater than 100.




Let’s run this and agree to the Make Table. When we open the CustTEST table, we can sales with a unit price higher than 100. Access is going to conglomerate them together.











Let’s say you want a list of all the customers with all their contacts, but you want it in one table. Bring in the ContactT table. Double-click the join created and select option 2.




Bring in the Star from the CustomerT table, and DateTime and Notes from the ContactT table.




If you want to change the CustTEST name, just pick Make Table again and change the name to TempCustWithContactT. What’s that going to do? Run it and agree to the Make Table. Open up the TempCustWithContactT table and we can then see all the customers and their contact notes.


Lesson 9: Project Automated Queries

This project is going to show you how to automate all these queries together. It’s going to do a Make Table Query, Append Query, Delete Query, and then an Update Query. We’re going to do this with a form, some buttons, and even a macro.

I don’t want to have to manually make a backup table, then append the rows, and then delete them. I want to be able to automate that process in one swoop. So let’s make a form and put a nice big button on there that will automatically back up my contacts.

Create a blank form in Design View. Open up your toolbox and bring in a text box. It will say, “Archive Contacts Before:” and then we’ll put a date in there.




Let’s bring up the properties for the text box. The Name of the text box will be ArchiveDate and the Default Value should be today’s date – 365.




Let’s save this form as ContactArchiveF. Let’s give it some color and take a peek at it in run mode. Okay! Looks good.



Now I need a button that’s going to run my queries when I click on it. Let’s make a macro to run all four queries for us. Since I want to back up my ContactT table, let’s make a query for that real quick.

Create a new query in Design View using the ContactT table. We need to backup the whole thing so let’s change this to a Make Table query named ContactT AUTOBACKUP. Click the star to bring in all the records.




Save it as MakeTableContactAutobackupQ. Run it and agree to the Make Table. The next step is to run my Append Query and append those records onto my ContactArchiveT table.

I already have an AppendContactQ query, so in Design View, I want to change the date criteria in the AppendContactQ query to Forms!ContactArchiveF!ArchiveDate because I want to pull it off of the form. But the DateTime has to be less than that, so I’ll put a less than symbol in front of it.




Now save it. That will append those records. Now it’s the same thing for our DeleteContactQ, In Design View, change the DateTime value to < Forms!ContactArchiveF!ArchiveDate.




Save changes. Now the only thing that I don’t have is an Update Query. So let’s make a new query in Design View using the ContactArchiveT table. We’re going to change records once they’re in the archive. Let’s set this to an Update Query from the toolbar. Bring in the MustCallBack and the CallBackDateTime field.

Update the MustCallBack field to False, and set the CallBackDateTime field to Null.




Save it as UpdateContactArchiveQ. Run it and agree to the update. Now I’ve got all four of my queries all set to go. Now, let’s make a macro to automate the process.

Select Macros from the database window. Click New. Okay, what do I want to do? Well the first thing we have to do is back up our table. Drop the list down and select OpenQuery. Down at the bottom select the MakeTableContactAutobackupQ query for the Query Name.






Next action – OpenQuery again. Now I’m going to want to run the append. So select AppendContactQ for the Query Name.




Nest, we want to delete those same records. Select DeleteContactQ.




Last but not least, we want to OpenQuery again and update the archive (UpdateContactArchiveQ).




Save this macro as ContactArchiveM. Now when I run this thing, it should run all four of those macros. Before we do, let’s put some data in here to see that it’s working. So let’s close this.
Let’s put in a new contact just over a year old from the main menu.




Now let’s make sure the contact is in the system. Go to Tables and then Contact to find it. Just to be safe, let’s check its MustCallBack value.




We need to put a button on our form to run the macro we made earlier. Bring up the toolbox and grab a button.


Select Miscellaneous from the Command Button Wizard’s Categories list and Run Macro from the Actions list.




Next, select the ContactArchiveM macro.




Next, we’ll name the button Archive Old Contacts.









Next, we’ll give the button the meaningful name of ContactArchiveButton and hit Finish. So let’s save that and open it back up again.





Here comes the moment of truth. Are you ready? Click the button and agree to the Make Table, the Deletion, the Paste, and the Append. Let’s look at the ContactT table now. Gooobbaaaa is gone but it is in the ContactArchiveT table. It worked!

To turn off those record number boxes on the form, go into its properties, and set its Record Selectors and Navigation Buttons value to No.




Save changes. We can now put that on our main menu in Design View. Drop a new button onto the main menu and select Form Operations – Open Form from the Command Button Wizard.














Next, select the ContactArchiveF form.




Next, name the button Archive Contacts, and hit Finish.




Save it. When we re-open our main menu and click the button, there it is!




When we click the buttons and go through the confirmation process again, you’ll notice that it gets annoying. How do I get rid of those? Well there are two things that you can do. You can get rid of all of those prompts throughout your entire database or you can turn off the warnings in your macro and then turn them back on when the macro finishes.


To turn them off for good, select Options from the Tools menu. Go to the Edit/Find tab and uncheck Action Queries. If you turn Action Queries off, you won’t be prompted anytime you run an Action Query in your database. Not a good idea – leave that checked on because if you leave that off and run a Delete Query or Update Query – it’s just going to run and you won’t have any idea that the query just ran!




What you should do instead, is go to your macro in Design View. Insert a row up top and select the SetWarnings command.




At the bottom (the end of the macro), set the warnings back on.




Save that and now let’s try running it. Hit the button and Boom – Everything’s done! It didn’t even give you a message! A message might be nice, huh? Let’s put a message in there.

Open the macro in Design View, and insert the MsgBox command on the bottom. What do you want to say? The message box will say Archive Complete and it will display an Information icon with “Archive Done” as its title.




Save it and now let’s try it. Archive completed!




So we’ve got a great little system now – we can archive all our contacts with a macro that will Make Table Query, Append Query, Delete Query, and then an Update Query. That’s a lot of stuff!


Review

Let’s take a moment now to review what we covered in class.

· We learned all about Action Queries, Update Queries to change data, Append Queries to add records onto the end of an existing table, Make Table Queries to create your own table, and Delete Queries to delete records off of a table.
· We learned how to automate all these queries with a macro.



Tell us what you think. Log on to www.AccessLearningZone.com/Survey and take a short survey about this course.


RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!


Take your skills check quiz at www.AccessLearningZone.com/Test. If you pass, you can print out a Certificate of Completion.

What’s next? Visit www.AccessLearningZone.com for our complete list of Microsoft Access courses.

Need Help? Visit www.AccessLearningZone.com/TechHelp for Microsoft assistance.

Make sure you’re on our Mailing List. Go to www.AccessLearningZone.com/Mailing for details.

What’s New? Visit www.AccessLearningZone.com/WhatsNew for a list of what’s been added..

Contact Us. If you have any questions, go to www.AccessLearningZone.com/Contact for information on how you can contact us by phone, email, or live online chat.



Don’t forget to visit our Microsoft Access Forum online at: www.AccessLearningZone.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your Access questions, get answers, and tell us what you thought of our class.



This course, handbook, videos, and other materials are copyright 2002-06 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.

This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:

Amicron Computing
PO Box 1308
Amherst NY 14226 USA
www.AccessLearningZone.com







Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

12/3/2021Access Beginner 2
12/3/2021Access Beginner 2 Lessons
12/3/2021Access Beginner 2
11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/28/2021Random
11/22/2021Currency Symbols
11/13/2021Access Developer 36
11/13/2021Access Developer 36 Lessons
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access handbook  PermaLink