|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Record Source By Richard Rost 
6 years ago
Learn About the Record Source Property in Forms In this lesson, I'm going to teach you how to change the Record Source property of a form so you can switch the table it's based on, or even change it to a query. MembersThere is no Extended Cut for this video. Links
IntroIn this video, I will show you how to change the Record Source property for a form in Microsoft Access, allowing you to update your form to pull data from a different table or query without having to redesign it from scratch. We'll look at an example where a form originally based on a table is switched to use a query instead, and I'll explain important tips such as matching field names and adjusting query settings to ensure your form works as expected.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's class, I am going to teach you about the Record Source property of a form that you can use to change where the form gets its data from. In this case, we are going to change from a table to a query.Today's question comes from Dawn. Dawn says, I would like to base a form that I took a long time to design off a new query instead of the table to which it was originally set. Is this possible without restarting from scratch? Yes, fortunately, that is very easy to do. Here I have a customer table and a customer form. Open up the customer table. You can see first name, last name, address, city, state, zip, and so on. The customer form, which is based on the table, has basically the same fields. If you right-click on the title bar and come down to Design View, here we have the form and the property sheet. If the property sheet does not appear, you can click on the property sheet up here on the menu or simply double-click right here where the two ruler bars meet. That brings up the property sheet. Now, on the property sheet, go to the All tab. You will see CustomerT. That is the table that this form is based on. If you want to change that, drop this little list down here. That should show you a list of all the tables and queries in your database. That is why in my Access Beginner 1 class, which you can watch for free on my website, three hours long, I tell you to put all your table names ending in T and your queries ending in Q. That way, in this list, you can see which one is which. It shows you the difference. You might have a CustomerT and a CustomerQ. We are going to pick Customer New YorkQ. This is a simple query that shows just customers from New York. Close this, close that, save your form. Let's take a look at the results real quick. If I open up this query, you just see the customers from New York state. Now, if I open up the form, I am only seeing one of two and they are both customers from New York because I changed the Record Source property. Right over here, Record Source property. You can change it to any table or query that you want as long as the fields match up. Make sure that if you open up this query, you have the exact same field names in here. If you do have criteria in your query, make sure that you turn off the Show box. If not, then you will see the names change. Remember, you get CustomerT.State. We have to make sure for any other added fields, you turn off the Show box there. And that is it. Thanks, and I hope you learned something. Make sure to subscribe to my channel. Click the subscribe button and then click the little bell and subscribe to all to get notifications whenever I release new TechHelp videos like this one. Want to get your question answered? Visit my website. Go to the TechHelp page and you will see some options for sending me your questions. Of course, you can always email me directly if you have questions that are of a sensitive nature, things about your business, for example. But if it is something that can be public, go ahead and post it on my TechHelp page. Make sure you visit the forums on my website. I have lots of good discussions going on there on Microsoft Access, Excel, and other topics as well. If you are on Facebook, drop by our Facebook page. Here are my other goodies: my blog, Facebook, Twitter, YouTube. Here is a link to try my Access Level 1 class for free. It is a three-hour full-length tutorial that you can watch absolutely free on my website and on YouTube. Here is a special: if you like Level 1, you can get Level 2, which is another full class, for just one dollar. Thank you for learning Access with Access Learning Zone. Again, I am Richard Rost, and thanks for watching. QuizQ1. What is the main purpose of the Record Source property in an Access form?A. To change where the form gets its data from, such as a table or query B. To set the order in which records are displayed C. To specify user access permissions D. To assign macros to form fields Q2. How can you access the property sheet in Design View if it is not already visible? A. Click the Office button B. Press F4 or click on Property Sheet in the menu C. Right-click the database window D. Use the Form Wizard Q3. According to the video, why does Richard suggest ending table names with "T" and query names with "Q"? A. To organize files alphabetically B. To differentiate tables from queries easily in dropdown lists C. To comply with Access system requirements D. To prevent accidental deletion Q4. What must be ensured when switching a form's Record Source from a table to a query? A. The query has at least 10 records B. The field names in the query match the ones used in the form C. The query has no filters set D. The query uses only primary keys Q5. What should you do if you have criteria set in your query but do not want the field to display in the form? A. Delete the field from the query B. Deselect the Show box for that field in the query design C. Rename the field D. Set the field value to null Q6. What will happen if you change the Record Source to a query that only returns certain records, such as customers from New York? A. The form will display customers from all states B. The form will show only customers from New York C. The form will become blank D. The form will display a warning message Q7. What is the advantage of being able to change a form's Record Source property? A. It allows using the same form design with different sets of data B. It improves database performance automatically C. It creates a backup of the table D. It prevents accidental data changes Q8. Where can you submit your own questions for future TechHelp videos? A. Only by calling Richard directly B. The TechHelp page on the AccessLearningZone website C. The Access application itself D. Any YouTube video comment section Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-A; 8-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to change the Record Source property of a form in Microsoft Access. This is a very useful feature because it allows you to point your form to a different set of data, such as a query instead of the original table, without having to redesign the entire form from scratch.Dawn asked whether it is possible to swap out the data source behind a form she spent a lot of time designing. She wants her form, which was originally based on a table, to instead display the results from a query. Fortunately, this is a straightforward process. Consider a scenario where you have a customer table containing fields like first name, last name, address, and so on. There is also a customer form based on that table, which displays the same fields. If you want to change the source of the data for this form, you can open the form in Design View. Once there, you should see the property sheet. If the property sheet is not visible, you can bring it up from the menu or by double-clicking where the two ruler bars meet in the corner. On the property sheet's All tab, there is a Record Source property, which will show you the current data source for the form. By default, this might be set to the customer table. If you expand the list for this property, you will see all the tables and queries in your database. This is where it helps to name your tables and queries consistently, for example, ending table names with T and query names with Q. That way, you can easily distinguish between them in the list. To change the source, select your desired query from the list, such as Customer New YorkQ, which contains only customers from New York. After you select the query and save your changes, opening the form now will show only records that match the query—so if the query returns only two customers from New York, the form will display just those two. It is important to make sure that the query you connect to the form contains all the necessary fields that your form expects. The field names should match between the form and the query. Also, if your query uses criteria, pay attention to the Show checkbox in the query design grid. If you do not want a field shown in the results but need its value for criteria, simply uncheck the Show box for that field. Otherwise, you might see unexpected changes in your form. That is essentially all there is to it. You can change the Record Source property to any table or query as long as the field structure matches what the form is designed to display. If you want step-by-step instructions, you will find a complete video tutorial on my website at the link below, where I walk through the entire process. Live long and prosper, my friends. Topic ListChanging the record source property of an Access formOpening the form in Design View Accessing the property sheet of a form Locating and modifying the Record Source property Selecting a query as the new record source Ensuring field names match between source and form Using queries with criteria as form record sources Managing which query fields display with the Show box |
||||
|
| |||
| Keywords: TechHelp Access Record Source RecordSource PermaLink Record Source Property in Microsoft Access |
