How to Create a Form with a Subform to Show Related Records in Microsoft Access
HTML-код
- Опубликовано: 30 июл 2024
- In this video I will explain what a Form and Subform are. I'll show you how to create a Single Master Form and a Continuous Subform to go inside it. We will track employees and their mileage reports.
Janessa from Durham, North Carolina (a Gold Member) asks: I have a business where we reimburse our employees for mileage. I've been keeping track of their miles for every day in an Excel sheet, but I know there's got to be a better way to store them in my Access employee database. Can you help me?
Silver Members and up get access to an Extended Cut of this video. Members will see how to determine if a specific record has already been added to a subform, and if so, don't allow it to be added again. This is handy to prevent duplicate services for the same customer, or you can use it to see if a product has already been added to an invoice. We'll design a "wizard" to enter mileage where the database will ask you the date, check to see if it's been entered, and if not, ask you for the mileage.
MEMBERS VIDEO:
• Subforms in Microsoft ...
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/Subforms
LINKS:
Relationships: 599cd.com/Relationships
Continuous Forms: 599cd.com/Continuous
Blank Template: 599cd.com/Blank
Contact Template: 599cd.com/Contacts
Invoicing: 599cd.com/Invoicing
Vehicle Maintenance: 599cd.com/Vehicle
Widows & Orphans: 599cd.com/WidowOrphan
Subform in Continuous Form: 599cd.com/SubformInContForm
Nested Continuous Subforms: 599cd.com/NestedContinuous
Event Enrollment: 599cd.com/EventEnrollment
Access Expert 3: 599cd.com/ACX3
Side by Side Subforms: 599cd.com/ACD21
Composite Keys: 599cd.com/ACX23
Increment Quantity if On Order: 599cd.com/ACD27
Microsoft Subforms Help: tinyurl.com/y5w2uoho
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
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:
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:
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Create a form that contains a subform, One-to-many form, What is the subform in Access, How do you create a subform in Access, What is main form and subform in MS Access, access subform tutorial, how to create subforms, how to use subforms, create a form with subform, Creating a Subform in Microsoft Access, main form and subform in ms access, composite key
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
I am trainning myself to thumb up from the beginning of class instead of the end so that I won't forget. Richard is a hardworking, dedicated, detailed oriented, intellectual teacher. We are blessed. Thank you Richard for sharing your knowledge to the world.
Thanks! :)
Thank you so much! This is exactly what I need to keep track of invoices and payments for our outside employees. I spent hours today trying to figure it out before I came across your channel.
OMG I did it! I cannot tell you how amazingly easy your videos are to follow. I know Excel inside out but just couldn't grasp the concept of Access, then I started watching your videos. I just built my first database and I am so proud of myself - THANK YOU!!!!
Great to hear!
Thank you. Subforms are so much easier than an explicit query, but I just wasn't getting it until I watched this video... while working through your sample template. Earlier today I tried to engage CLZ to consult on my database. Instead, with just a few hours invested in sorting through it, I think I'm a lot farther along. Thanks. Great instruction - your enthusiasm helps so much!
You're very welcome. Glad you enjoyed.
I started with Access 1.0 on FLOPPY DISK back in the 90's! I've been away from it for many years, but my current job needs those skills. Your video's have been great to get me back into Access. You're easy to follow, provide great tips in every video, and a great teacher! Thank you so much!
If you started with Access version 1 then you've been using it longer than me. I started with version 2.0 back in 1994, I believe. Before that I was actually trying to build databases for clients using C. That was tough.
Hello Sir, Good morning! Revising your video as being forgotten. Thanks for posting your videos. Wish you good luck.
Just started watching the Level 1 and 2. Moving a large Anaplan DB to Access. Not that happy about it, but happy I found these videos.
Get happy. Access is amazing.
Your video's have been a great help to me. You're easy to follow, provide great tips in every video, and a great teacher! Thank you so much!
You're very welcome!
This is superb. I'm working on a few things to make my life easier, and this might be the right track.
Very useful information, a great addition to the knowledge about sub forms.
Thanks
Really helpful information well delivered. Thank you. I am re learning Access after a 10 year hiatus.
Welcome back
Excellent and clear explanation. Perfect
Thanks
love the way you explain access, as a newbie.
Thanks
You just saved my job and my life several times. THANKS ALOT :)
You are welcome!
Great information!!
Hi, thanks for all this videos, are awesome, please one question, why the access search button does not work on that subforms? I google it, a lot, but I can't find an answer, I just want to find a record on any part of a field on the subform. maybe a video where you did that? thanks for your help
Thanks! Very Helpful
Glad it helped!
Great, Thank You 🙏🏻
Welcome
Hi there! Love these videos and I have found them SOO useful! Thank you!
I'm curious if there is a way to take a dollar amount displayed in a master form and subtract a number from a subform and them display the result in the master form. I hope this makes sense.
Thanks in advance for the help!
-Zach
I love your videos.
Thanks
Thanks!
Is there a way to make the system work online, in which multiple users can work together from differant locations?
599cd.com/AccessOnline
@@599CD
Thanks!!
Hi Richard, for the Gold membership which offers "One FREE Expert Course Per Month" we have to create a user account on your website? And Do we have to sign up to your site with a credit card, or do you just offer us access to the courses based on the youtube Gold membership?
You get one free course per month up to the Expert level, in order. You can sign up on my web site directly or here on RUclips. Either way, you'll get an account on my web site where you can watch your free classes.
Thank you so much
Welcome
Nice. It is possible to create two lists and through one to choose information and give its characterists in other list??
For example, un list name continent, the other list countries. When you choose a continent, its must give only the countries affected
599cd.com/Cascading
Can people use the exchange rate automatically connected to another site in access and that the new rate does not affect the old month price conversion???
Sure
Good Job
No matter what method I use to add a datasheet form as a subform, it gets converted to a form view! I can even set it so that form view isn't allowed, but it still is stuck as form view.
Great presentation. Thank you
Please i need your help
I created two forms from two different tables. i want to enter only the PatientID from the first form so all the remaining patient details will be filled in the second form. How can i do that? i will be grateful if you can help me.
599cd.com/Ask
Can this be done? A subform shows dates that training is completed for 5 training requirements. Can the form show all five requirements before the dates are entered? The database has 3 tables, Employees, Requirements, RequirementDate. The RequirementDate table is a links table with EmployeeID, RequirementID, and Date.
Sure... but without knowing more about your database, I really can't tell you how. Post your question at 599cd.com/TH
How to filter subform using multiple values selected in listbox
I came across a weird situation. I followed your instructions exactly and it seems to be working but the issue i am having would be the following. When I click on the Employee form to go to the next record it appears that it is pulling up the multiple records in the mileage form. So if there are four records in the mileage form i have to click next 4 times in the employee form so it goes to the next records. So on your employee form you have 3 records and hit next 3 times and you have gone through all the records. In my scenario I would have to hit next and if there are multiple records in the mileage form its as if it creates a phantom record in the employee form that i have to hit next on. Hope that made sense.
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
very interesting video I watch from Pakistan
Thank you from Florida
true legend
Appreciate that
I am following your video, however when I view the form after creating it it just fills the entire window. Like wise my form design view does the same, the scroll bars are at the edge of the screen or window. What am I doing wrong? Also I don't get the dots between my grid lines in design view
599cd.com/grid 599cd.com/overlap
Every time i open a specific form then close it the try to open anothe report that is related to the same table it cause the error "the database could not lock table becaouse it is being used by another user so i have to log out the. Login with the login form....if i open the report then close it before opening the form when i open the form it now becomes blank....how do i solve this
Not sure. I'd have to see it.
Hi Richard - I am working on a form based tool. I am stuck in designing something. Appreciate of you could help me.
Can't help you without more details.
@@599CD Thank you for your response. I am trying to build a subform that pulls data from diffrent diffrent tables based on a user input. On that form, I would like to add a button to select/delete records. User should not be allowed to edit directly through the subform. Appreciate if you could help me a readily available logic you might have. Please do let know if you would need more information.
Can a subform be designed in the same was as a master form - rather than just looking like a list of records?
Sure you don't have to use a continuous form for the subform you can use a single form if you want to
One question, how can I change the employee ID to Combo box?
I would like to have a combo box instead of clicking on right.
599cd.com/ask
Thanx
Thanks
Welcome
I often set a subform's Tab Stop property to No, so the user will stay on the main form if he keeps pressing Tab. If he presses Tab while on the last text box, he will go back to the first text box on the main form, which I think is what most users expect to happen. If the subform's Tab Stop is Yes (default), the cursor will be stuck inside the subform eventually, and it's hard to tab back out. When Tab Stop is No, and the user can only get into a subform by clicking on it. Also, when you tab onto a command button, it's hard to see the "cursor" and the user may get confused if he expects the cursor to go to the next text box. So I set Tab Stop to No for command buttons too. In fact, I set Tab Stop to Yes only for things the user types on: text boxes, combo boxes, etc.
I usually do the same in finished databases. Great minds...
I believe the outer form is a customer order form but just contains all of the customer's details. The first subform is an order form with a date and order id and then this subform contains a subform with all the orderlines. The form is three layers deep.
K
I am doing the census of my neighborhood in access. The main form contains the details of the head and the sub-form contains the details of the household. The names of those who have more than seven people in their household are not showing while taking out the print
599cd.com/ask
Please I’m on a project of creating a database for my department in Tertiary school
It will be used to all records of the department ,Students registration, executive’s activities and others however I’m finding difficult in creating relationship so that each student will have a separate details whenever the name is searched ..everything concerning the student will appear
Please is any way I can reach you
599cd.com/ask
I'm looking a tutorial for a subform within a subform within a form, with each a calculated field that must finally show on the outer form. Imagine you have an order form with order lines with a calculated line total and then the total for all the order lines is on this sub-subform but this total must only show itself on the outer form.
599cd.com/Ask - need more details, preferably some screen shots
@@599CD i really wish I could because this was a task we were supposed to complete. It's kind of been forgotten about, it was a challenge from our tutor. He's not very good at producing answers. If you don't try at least we get nothing from him. It's not a real big deal but i thought it was interesting at the time. I'll dig out the question and post it here tomorrow.
🤩🤩🤩
With this method you one can create a transaction history/statement for a customer
Yep
🙏🙏🙏🙏🙏🙏🙏
Welcome
Just make some space between fields to accommodate date picker. Sheesh.