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... 🙏🙂
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.
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
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.
@@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.
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.
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.
I need some clarification... you state that it is not good to edit data in the forms BOUND to a query that draws data from multiple tables. I totally get that. MY question is... is it ok to edit data in a query that is NOT BOUND to a form? So editing the information directly in the query table. (if that makes sense). So is the issue editing a query with multiple table data, or specifically editing one that is bound in a form? Thank you. Gold member.
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
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.
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.
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
Yeah, sometimes I like to leave comments from ignorant people on here so they can share their ignorance with the rest of the world... to shine as a beacon of what NOT to be... (But I'm sure he already knew this).
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... 🙏🙂
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.
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
Well both of those things have their place but you shouldn't do it all the time
Great video, Richard! Question - I understand why the "Halfcredit" sum gave an #Error#, but why did the "CreditLimit" give an #Error# also (@12:45)?
Once you get any error in your form-footer totals they sometimes all error out that's just the way it works
@@599CD Thanks for the reply! I'll just add this "#Error# to the WoA list... Weirdness of Access. LOL!
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.
thank you i learned a lot from you. great work🙂
Thanks
Released 6 days ago? Right when I needed it!😁
Sweet
Very helpful. From the title, I thought you were going to talk about query benefits for reuse across multiple forms, e.g. calculated fields.
Yeah sometimes coming up with the title is the most challenging part of the video LOL
@@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.
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.
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.
I need some clarification... you state that it is not good to edit data in the forms BOUND to a query that draws data from multiple tables. I totally get that. MY question is... is it ok to edit data in a query that is NOT BOUND to a form? So editing the information directly in the query table. (if that makes sense).
So is the issue editing a query with multiple table data, or specifically editing one that is bound in a form?
Thank you. Gold member.
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
Do not store documents or other images inside your database it's not designed for that see 599cd.com/Images
my problem is I created a cross tab query and I want to show it on a form
Thank you.
Welcome
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.
I still use queries from time to time.
@@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 😅
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.com/Advice
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
If possible can you make a video on it?
599cd.com/Ask
Editing table data through a query is like saying Voldemort. Just don't do it.
Generally, yeah.
i already knew this. you teach late sir.
Well aren't you special? Sorry not everyone is as educated as you.
Nobody likes a SmartArse. Anyway, you're the one that's late, you should have watched this before, when you didn't know.
Yeah, sometimes I like to leave comments from ignorant people on here so they can share their ignorance with the rest of the world... to shine as a beacon of what NOT to be... (But I'm sure he already knew this).