When You Should and Should Not Bind a Form to a Query in Microsoft Access

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?
BECOME A MEMBER:
KZbin: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
Forms Bound to Queries: 599cd.com/FormsBoundtoQueries
PREREQUISITES:
Relationships: 599cd.com/Relationships
LINKS:
Invoicing: 599cd.com/Invoicing
Calculated Fields: 599cd.com/Calculated
Not Updateable: 599cd.com/NotUpdateable
Form Footer Totals: 599cd.com/FormFooterTotals
Subforms: 599cd.com/Subform
RECOMMENDED COURSES:
Access Expert Courses: 599cd.com/AccessExpert
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
How to Use Access in 30 Minutes: 599cd.com/30Minutes
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
TechHelp Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, 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
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.

[@stanTrX]

Hello Richard , i have used to create forms based on queries only before watching your tutorials. Now life seems easier with this approach. I have even stopped setting global relationships. Thanks

[@599CD]

Well both of those things have their place but you shouldn't do it all the time

[@fuego09esmeralda]

It's videos like this that make me realize Access doesn't do the things one wants the way one wants... It must be done the Access way... I can't tell you how many times I've tried to do what you describe in this video... But after this, I think I'm better prepared to avoid the pitfall... Thank you, teacher... 🙏🙂

[@599CD]

Well like anything it's like learning the machine that you're working on. You can't go very fast with your foot on the brake... So you have to do things the way the car wants you to.

[@Shadow.Dragon]

Great video, Richard! Question - I understand why the "Halfcredit" sum gave an #Error#, but why did the "CreditLimit" give an #Error# also (@12:45)?

[@599CD]

Once you get any error in your form-footer totals they sometimes all error out that's just the way it works

[@Shadow.Dragon]

@@599CD Thanks for the reply! I'll just add this "#Error# to the WoA list... Weirdness of Access. LOL!

[@rabidfollower]

When aggregate functions are used in a footer, Access needs to run an internal query to evaluate them: SELECT Sum(CreditLimit), Sum(Halfcredit) FROM CustomerT . So if Halfcredit is nonexistent, this query won't run at all, so none of the Sum() fields will be evaluated, and all the textboxes will show #Error.

[@MsEmir86]

thank you i learned a lot from you. great work🙂

[@599CD]

Thanks

[@sedrickbowser3588]

Released 6 days ago? Right when I needed it!😁

[@599CD]

Sweet

[@CoreyMinter]

Very helpful. From the title, I thought you were going to talk about query benefits for reuse across multiple forms, e.g. calculated fields.

[@599CD]

Yeah sometimes coming up with the title is the most challenging part of the video LOL

[@CoreyMinter]

@@599CD my comment was not to complain. i should have worded it differently... I'm a recent subscriber and I remember some your lessons also explain the benefit of queries for reuse.

[@ahmedjabbar5589]

Thank you.

[@599CD]

Welcome

[@Hrubicundus]

Thanks Richard, very informative. Do you recommend putting subforms into the detail section or the footer section of the main form, or does it not matter? I recently had an absolute nightmare trying to open a form on my laptop that I had originally created on my big computer. The top half of the form has the fields from the main form, then a subform below. The small laptop screen couldn't fit in the whole form and basically wouldn't show any of the fields from the main form, only the subform at the bottom. And there were no scroll bars to be able to scroll up and see the other fields (scroll bars were enabled in the form properties, but none were shown). And the subform only had a couple of records so there was acres of unused space being displayed at the bottom of the form, but none of the main fields at the top were displayed. Very frustrating. Ended up having to change the display resolution to make everything small enough to fit in the screen, but it was too small to be easy to work with.

[@599CD]

Whether to place the subform in the header, footer, or detail section is up to you, and doesn't really matter to Access. For small screens, I would definitely use a 2nd form that opens up over the first one. Make it Modal if you want to make sure the user doesn't "go behind" it and change the parent record.

[@CamiloSanchez1979]

Richard. Big fan. I am using Access as a document manager database. My goal is to put all the articles and associated image files into the database. We have a really large Word document and I want to break it into database records. It has been very clumsy because I get weird characters on the text. Either way, this is the only option at this time as the company will not invest on a large LMS. Do you think this is a good idea or am I f..ng up. Context: I am a corporate trainer

[@599CD]

Do not store documents or other images inside your database it's not designed for that see 599cd.com/Images

[@leomaredcampos4941]

my problem is I created a cross tab query and I want to show it on a form

[@efrainedwards9856]

Hello, I had a quick question. I am 17 and I wanted to know if you had any Online Job Ideas that I could do. I am very interested in being Database contractor, however would I be able to do it online, anywhere in the world? I ask because I am a United States Citizen, however I do live in a foreign Country.

[@599CD]

599cd.com/Advice

[@simonmaersk]

Personally, I think that queries in Access are mostly for the beginners, and I don't use them at all myself. I don't see why you would want to create a query object (in the navigation pane) when you can just run SQL straight from VBA, or directly write your SQL in a form's record source.

[@599CD]

I still use queries from time to time.

[@simonmaersk]

@@599CD Okay, I don't see anything wrong with that! I am just kinda OCD, and for some reason, my OCD is telling me to avoid them if I can 😅

[@ajnabirameez]

Dear Richard! My question is not related to this video sorry for that. Q: I have a table in my database which includes several fields. I want to export the data by selecting different fields. I have to export different fields data time to time. How i can make check boxes to select the Fields to export data. It's difficult to make query everytime to export different type of Fields. Muhammad Rameez from Pakistan

[@ajnabirameez]

If possible can you make a video on it?

[@599CD]

599cd.com/Ask

[@replicaman9215]

Editing table data through a query is like saying Voldemort. Just don't do it.

[@599CD]

Generally, yeah.