Forms Bound to Queries
By Richard Rost
2 years ago
When and When Not to Bind a Form to a Query In this Microsoft Access tutorial, I'm going to show you when you should and should not build a form based on a query. We'll see when it's useful, when it's harmful, and when you absolutely should not do it. Kieran from Newton, Massachusetts (a Platinum Member) asks: I'm relatively new to Microsoft Access, but with the help of your tutorials, I'm making a lot of progress. Thank you. One problem that I'm having is that I tried to make a continuous form where I can see customers and their order information, but whenever I try to edit something like the customer's address, it ends up changing all of the orders. I have my form based on a query that joins customers, orders, and order details, but I'm not doing something right. Can you shed some light on this? PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Continuous form, multiple table query, form based on query, non-updatable record sets, subforms, one-to-many relationships, form footer totals, calculated fields in queries, action queries, VB for Access, Access form design best practices
Intro In this video, we will talk about when you should and should not bind a form to a query in Microsoft Access. We will discuss common mistakes beginners make when trying to edit fields from multiple tables in a single form, why this approach can be problematic, and the risks of creating non-updateable recordsets. You will learn best practices for using forms, queries, and subforms in Access, and how to safely display information from related tables without causing editing issues.Transcript Today we are going to talk about when you should and should not bind a form to a query in Microsoft Access.
This one is for beginners, especially those who are just starting to learn about table relationships, relating multiple tables together like customers and orders, and putting those together into queries. If you are a beginner and you want to learn more about relating tables together, go watch my Relationships video first and you will better understand the topic that I am talking about today.
Today's question comes from Kirin in Newton, Massachusetts, one of my Platinum members. Kirin says:
I am relatively new to Microsoft Access, but with the help of your tutorials I am making lots of progress. Thank you.
You are welcome.
One problem that I am having is that I try to make a continuous form where I can see customers and their order information, but whenever I try to edit something like the customer's address, it ends up changing all of the orders. I have my form based on a query that joins customers and orders and order details, but I am not doing something right. Can you shed some light on this?
Kirin sent me some screenshots of what he has got going on in his database, and he is making a common mistake that I see from a lot of people who are beginners with Microsoft Access and Relationships. Let me show you what he has got going on.
Here is a copy of my TechHelp free template. This is a free database. You can grab a copy up on my website if you want to.
This database has customers, a customer table, an order table, and an order detail table. For example, each customer can have multiple orders and each order can have multiple details associated with it.
If you are not familiar with how this was set up, go watch my invoicing video. I explain all that in that video.
What I see people try to do a lot is they try to make one form to edit data that is in multiple tables, and that is not a good idea.
For example, create query design. You take the customer table, you bring over the order table, you bring over the order detail table, and these things are all related to each other.
So, you want to see the customer ID, first name, last name, address, maybe the order ID, the order date, whether it is paid, and then maybe the order detail ID, the product, the quantity, the unit price, all this stuff. And this is all in one big query. I save it, and let us call this the customer order query.
When I run this, now I have all this information here. You can see all the customer information, all the order information, all the order details for each order. People try to make a form based on this, thinking they can just go in here and edit whatever they want.
They will take a continuous form - let us use my template one here - right-click design view. Then they will base this form on that query, my customer order query. Then we can take this and get rid of the fields that are in here.
Now we can go to form design, add existing fields; here are all the fields from that query that we made. Let us just select all of them, we will bring them all over in here.
Here is a little quick trick. Go to Arrange, then go to Tabular, and look, it arranges them for us. Slide them over, and then I like to turn off the layout, remove layout after that.
Now we have them all looking like this. Slide them up, slide that up. Save it, close it, open it back up again.
Here is my form now where I have got all the fields: all the customer fields, all the order fields, all the order detail fields.
Then you come in here and you think you can edit something, like let us say its name is Apur Card. As soon as I leave that record, it updated all that stuff. I am thinking I am just updating this one order, or this one item, but I just changed all of these. What is going on here?
This is data from the customer table. If you are thinking about changing, maybe you have got an address field in the order as well (a lot of times people do that). You want to know specifically where an order was shipped, so you might have an address field in the customer table and then an address field also in the order table.
But if you change something over here, these are customer fields. If you change something over there, those are order fields, and if you change something over there, those are order detail fields. This is why I recommend you do not make a form that has data from a query with multiple tables in it. It gets really confusing.
Sometimes you will run into a situation where, because of the query, you can no longer edit data in here; it becomes a non-updatable recordset.
I have a whole video on non-updateable recordsets, go watch this one. Usually, your query is too complicated, and Access cannot figure out what you want to edit, and so it says, "Well, you cannot edit anything." That is one of the things that happens.
Another thing is it just gets confusing, and Access might edit the wrong table. You have got two fields in here that are similar like address, and you think you are editing the customer address and you are actually editing the order address, or an ID.
Remember, the order ID is going to be in a couple of different places. It is going to be on the order form; it is also going to be a foreign key on the order detail form.
So what do you do? How do you handle this? How do you set it up so you can edit the customer and the order and all that stuff? What I recommend is use multiple forms.
A form should really only have data from one table on it.
If you want to see the customers, here are the customers; if you want to see the orders, here are the orders.
Notice here I have got a subform. If you have orders and order details, use a subform to show one-to-many relationships. You can do the same thing with customers and contacts. I also have contacts; a contact is any time you talk to a customer. Here is a subform showing the customer and their contacts; that is a one-to-many relationship.
We do not want to put this on the same form; this is a separate form, it is just inside the other one - that is a subform.
If you want to learn more about subforms, I have a whole separate video on that. I will put links to all these down below; they are all free videos you can watch on my YouTube channel or my website.
You can sometimes bring data from multiple tables together if you just want to display information, but you do not necessarily want to be able to edit both tables. For example, let us say you have got your orders. Here is my order form. Let us say, just as a convenience, you want to also show the customer's credit limit on this form. In the customer table, we do have a field called credit limit.
What you can do is make a query for the orders and include some customer detail information. This form is currently based on just the order table, but we could make a query.
Let us create a query. Bring in the order table and join in the customer table. Bring all the order fields in, and then bring in just the stuff you want to see from the customer table, like credit limit, or whatever other fields you want to include.
Let us save this. I do not think I have an order query yet, so let us call it order query.
Before you do anything, run this query and just make sure you can hit a new record down here in the bottom. If you do not see this new row, that means whatever you did made your query uneditable, so that is bad.
Be careful, because you might still be able to edit this. I can probably change some of this stuff, and that is going to edit it in the customer table, but that is dangerous. Be careful, because that could lead to unintended consequences. I will show you how to fix that in just a second.
Now we are going to come back to our order form, right-click, design view, open up the properties, and change the record source to that order query now.
Now the order query is going to include that new field.
Let me slide this down and make this a little bit bigger. If I add existing fields, there is the credit limit - that is off the customer table. Click and drag and drop it right there. I know it is not going to match the formatting.
This is actually bound to the customer table. If I close this and open it up, you can come in here and make changes to it. I just updated that customer's credit limit, but again, I do not recommend this - it is dangerous and will get you into bad habits.
If you want to make changes to the customer record, come back here and change it.
Look, you go under these kinds of problems. Now this is me: 3,000. This still saw 5,000 over here because the record is still dirty. Dirty means it is still in the process of being edited. I have to actually leave this record and come back to see it updated over here.
Again, this can cause issues, especially in a multi-user database. You have to be careful with this.
What I recommend is, if you have any foreign fields in here (fields from a table that is not the primary field in that query or for this form), I recommend you lock or disable this.
I go to format; I like to make it gray, so the user recognizes that they cannot edit that here. Even though Access allows it, I do not recommend it. I have seen lots of problems caused by this.
Then open this up, go to Data, and you have two choices: you can lock it, which means it looks the same but you just cannot change it, or you can disable it, which actually makes it look different (it is gray and such).
Now if I save it and close it, then go back in, I can see that credit limit is there but I cannot do anything, I cannot even click on it. That is my recommendation.
I have had many students and past clients who have built forms that were based on multiple tables and the fields get confused, and Access causes problems. No - one table per form, unless you just want to display information. That is fine.
Another way this is handy is for doing form footer totals. For example, here I have a customer list. Let us say I wanted to get a total down here of all of the maximum possible credit limit - like if all my customers came into the store tomorrow and spent all the credit limit, how much would I be on the hook for?
You can right-click design view, take the credit limit field, copy it and paste it, put it down here. In the control source, you can say =Sum([CreditLimit]), just like that. Here, I will zoom in so you can see it better.
It is =Sum([CreditLimit]). That is the zoom window. Now you can get a total down here.
If that is not a field that is in the underlying table or query, this does not work.
For example, say you are taking the credit limit and have some other field based on the credit limit. Let us say it is half the credit limit. Copy and paste, make a new field right here. Open this up and let us call it HalfCredit. The control source is = [CreditLimit]/2.
It is called HalfCredit. Save it, and now open it up again. That works - we now have a new field called HalfCredit in the form. It is not editable though. You do not want it grayed out.
If I try to do the same trick down here and say give me the sum of HalfCredit, it even sees it as a field, but you get an error down here. Why is that? Because it is not in the underlying table or query.
What you have to do is put that in a query itself, and then you can add that up.
For example, create query design. Bring in the customer table, bring in the fields that you want, and then make your calculated field here called HalfCredit, which is CreditLimit/2. Save this as Customer2Query, or whatever you want to call it.
Here is a query now, and there is the HalfCredit field. Now this is actually a field in an underlying table or query.
Now take this form and base it on Customer2Query that has that calculation in it. Now here, change the control source to HalfCredit.
Now this calculation is pulling off of a field that is in the query underlying the form, and it works.
There are good reasons why you would want to base a form off of a query. This is one of them. If you want calculations in it, and you want to be able to do sums on those calculated values, great.
I prefer putting those in queries, usually, instead of directly in form fields. Unless it is a single form, for continuous forms, I always use a query.
You want to be very careful of doing something like the query I made before. Do not make a big continuous form that has fields from multiple tables, or even a single form where you have got fields from multiple tables and you are trying to edit them. Trust me, you will run into problems eventually.
If you like this and you want to learn more, and you enjoy learning with me, check out my expert lessons.
Usually, relationships and things like this are a little bit beyond what I cover in the beginner lessons, but definitely take the beginner lessons if you need them.
My expert lessons are where I spend a lot of time covering relationships, using forms with subforms, and all kinds of extra stuff like that. I have lots of expert lessons available. We build invoices, do form totals, report and grouping levels, all kinds of stuff, action queries. This is all covered in my expert series.
Once you are beyond the basics and you want to learn more about Access but you are not quite into development, and you do not want to learn VBA, that is what my expert courses are for. They make you an expert without making you a developer. But I will make you developers, too. Programming is not scary, folks.
VBA is simple to use, but this is the stuff that I cover in my expert classes.
So there you go. That is when you should and should not build a form that is based on a query.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. Why is it generally NOT recommended to bind a single form to a query that includes fields from multiple related tables in Access?
A. Because it can cause confusion and accidental editing of the wrong data
B. Because queries with multiple tables are always non-updatable
C. Because Access forms cannot display data from multiple tables
D. Because subforms cannot be used with queries
Q2. What is the recommended approach for editing data from multiple related tables in Access forms?
A. Use one form per table and display related data using subforms
B. Use one large form based on a single multi-table query
C. Only use datasheet views for editing data
D. Only display data and never allow editing
Q3. What is a subform MOST commonly used for in an Access database?
A. Displaying one-to-many relationships within a main form
B. Copying forms between databases
C. Creating calculated fields
D. Designing navigation controls
Q4. What potential problem can occur if a form allows editing of fields from more than one table, especially if fields have similar names like "Address"?
A. The user may accidentally edit the wrong table's field
B. The form will not display any data
C. Queries will always be non-updatable
D. Only one field will ever be editable
Q5. What should you do to a control in a form if it displays a field from a related (foreign) table and you want to PREVENT the user from editing it?
A. Lock or disable the control in the form's properties
B. Delete the field from the form
C. Change the record source to remove the field
D. Set the form to Read-Only mode
Q6. What is meant by a "non-updatable recordset" in Access?
A. A query result where the data cannot be edited in the form
B. A recordset that automatically updates when new records are added
C. Any form bound to a single table
D. Any report preview in Access
Q7. Why is it usually okay to include fields from multiple tables in a query for a form IF you only want to display information and not edit it?
A. Because displaying data does not risk data integrity across tables
B. Because queries are always editable
C. Because forms can only display calculated fields
D. Because subforms are required for all editing
Q8. What is the BEST way to include a calculated field (like "HalfCredit") that you want to sum in a form footer?
A. Create the calculation in the underlying query and use it in the form
B. Add the calculation directly in the form footer as a control source
C. Place the calculation in a macro attached to the form
D. Only use VBA code for summing fields
Q9. Why should you avoid binding a continuous form to a query that brings in fields from multiple related tables when editing data?
A. Editing may inadvertently change data in unintended tables, causing data integrity issues
B. Continuous forms cannot display multiple records
C. Only single-table queries are allowed for forms
D. Table relationships are not necessary in queries
Q10. When is it appropriate to use a single form based on a query including multiple tables?
A. When you want to display (but not edit) summary or related data from other tables
B. When you are new to Access and want to simplify data entry
C. When you need to delete records from multiple tables at once
D. When creating tables from scratch
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
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.Summary Today's TechHelp tutorial from Access Learning Zone will focus on when it is appropriate to bind a form to a query in Microsoft Access, and when you should avoid it.
This lesson is especially geared towards beginners, particularly those who are just starting to understand table relationships and how to connect multiple tables, such as customers and orders, into queries. If you are still new to table relationships, I strongly recommend watching my video on that subject first. Doing so will provide you a solid foundation for the topic I am about to discuss.
The question today was sent in by a student who is working through some beginner Access challenges. He said he is trying to build a continuous form to display both customers and their order data. However, when he tries to edit a field like the customer's address, it ends up changing all related orders, creating unexpected results. His form is based on a query that joins customers, orders, and order details, but he is running into trouble when making edits.
This is a classic mistake I see from many beginners working with Access and relationships between tables. Let me illustrate the issue and the right approach.
In my free TechHelp template, there are three main tables: one for customers, one for orders, and one for order details. The structure is set up so each customer can have multiple orders, and each order can include multiple order details.
A very common attempt is to create a query that pulls fields from all of these tables: for example, customer name, address, order ID, order date, payment status, order detail ID, product, quantity, unit price, and so on. After saving the query — let's call it the customer order query — you get a big dataset with all customer, order, and detail information together. Many users then try to make a form based directly on this query, hoping to edit any data they see.
The typical next step is to create a continuous form and set its record source to the new, multi-table query. All relevant fields are dragged into the form layout. At this point, when users open the form and try to edit, for example, a customer address, they often discover that this change affects every related record displayed — sometimes all the orders for that customer. This confusion arises because the field belongs to just one of the underlying tables, so changing data affects all instances in the query result.
This approach can also result in the form's recordset becoming non-updatable, meaning you cannot edit any data at all because Access cannot determine how to handle changes across multiple joined tables. I have an entire lesson on non-updateable recordsets, which you can refer to for more details on why this happens.
The main point to remember is that trying to build a form for editing that is based on a query joining multiple tables will lead to confusion and mistakes. You might not be editing the field you think you are, and Access could update the wrong table. For instance, if both the customer and order tables have an address field, it can be difficult to tell which you are actually changing.
So, what is the best solution? I strongly recommend using individual forms, each relating to a single table. If you want to work with customers, use a form bound only to the customer table. If you want to see orders, use an order form. When managing one-to-many relationships such as orders and order details, use a main form with a subform. Subforms allow you to display and edit related data without mixing information from multiple tables into one editable form.
If you are interested in how to properly use subforms, I have a video dedicated to this subject as well. Subforms are a reliable way to represent one-to-many relationships such as customers to orders, or customers to contacts (where a contact is any interaction with the customer).
There are cases when you may want to display data from more than one table in a form — for example, showing a customer's credit limit on an order form. In that situation, you can base the form on a query that joins the order and customer tables and include fields like the credit limit for display purposes. However, you should be careful not to allow editing of those fields that are not from the form's primary table. If you do allow editing, you risk making unexpected changes in the related table.
To prevent accidental editing of fields from related tables, I recommend either locking or disabling these controls on your forms. You can set them to appear grayed out in the format properties, making it clear to users that they cannot modify those values from that context. This avoids potential data integrity issues and confusion.
Another situation where it is beneficial to bind a form to a query is when you want to display summarized or calculated values, such as totals in a form footer. For example, if you want to show the total possible credit limit exposure for all customers, you can use a calculated control with an aggregate function like Sum. However, if you wish to sum a calculated value such as half the credit limit, that calculation needs to reside in the underlying query, not just as a control on the form. Otherwise, you will get errors when trying to aggregate it.
There are good reasons for basing a form on a query: including calculated fields, summarizing data, or displaying read-only information from related tables. For displaying and editing core data, however, stick to one table per form with appropriate use of subforms for related records.
I have seen many students and clients run into problems when building forms directly on multi-table queries, whether it is confusion over which data is being edited or issues with non-updateable recordsets. The best practice is to edit data in forms bound to single tables. Use subforms for related data, and only include fields from other tables for display — and even then, lock or disable those controls if you do not want users to edit them from that form.
If you found this helpful and want to expand your skills, my expert Access lessons go much deeper into relationships, subforms, invoicing, totals, reports, grouping, action queries, and more advanced topics. These are designed for students who are comfortable with the basics and want to advance without necessarily jumping into VBA or development straight away, though I do cover programming for those who want to learn that too.
That wraps up today's TechHelp lesson on working with forms and queries in Access. For complete step-by-step video instruction on everything I discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List When to bind a form to a query in Access
Problems with editing multi-table queries in forms
Why editing customer data updates all records
Understanding one-to-many relationships in tables
Using queries that join customers, orders, and order details
Basing a form on a multi-table query
Designing a form with fields from multiple tables
Limitations of editing data from joined queries
Access forms becoming non-updatable
Dangers of similar fields in multiple tables on forms
Handling fields like address present in several tables
Why use a subform for one-to-many relationships
Configuring subforms for customers and related data
Displaying data from multiple tables without editing
Locking or disabling controls from related tables
Adding related table fields to forms for display only
Formatting controls to indicate read-only status
Updating foreign table values and record states
Causes and implications of dirty records in Access
Creating calculated fields in a query
Displaying calculated fields on a form
Summing form footer totals using query fields
Ensuring calculated fields exist in underlying queries
Why use queries for continuous form calculations
Best practices for basing forms on single-table queries
Risks of editable fields from multiple tables on a form |
