Forum Discussion
Setting query as record source
I'm trying to redo my current training database by using the Access Faculty template as my guide. I noticed that the forms are all set to the query "Faculty Extended" as the record source, which has become a little annoying since I'm obviously changing the column headers. Whats the reason behind using a query as the record source?
- There are multiple possible reasons for using queries as a form's recordsource. One is that this can allow joins on lookup tables so that the value represented by a foreign key is available to the query directly, and hence to the form.
Generally speaking, if a form supports data updates or additions, though, it should be bound to a single table, or to a query based on that single table.
I'm not sure why you think it's obvious that you are changing field names (column headers), though. Maybe more context will help provide some more useful feedback.George_HepworthThe original template is set as a database to log school staff where as I'm using it for warehouse staff. The original field names do not all translate over such as "Department". We don't have individual departments but more so define based off their specific job function like Reach Truck, Dock, Inventory, Utility, etc.
The main goal is to use the database to track employee trainings.
Thanks for the clarification. Context is so important to understanding problems.
Actually, that's the basic point of templates; they provide a starting point for similar kinds of applications. It's going to take some discipline and effort to make all of the changes needed to adapt it to your requirements, though.
Make sure you create and keep a back up of the template before starting. It's not uncommon to change your mind partway through a modification, so being able to go back to a known starting point is really important. It's a template that could be recreated, but I like having a backup handy for reference.
First, I might invest in a 3rd party tool like this one. It offers a search and replace process that can change all relevant occurrences of names throughout an accdb. It's probably going to save quite a bit of time in simply renaming the fields in tables and all of the references to those fields in queries, forms, reports, macros and VBA. I find it invaluable.
Second, start with the tables, not the forms. All else flows from the tables. Don't even open any forms or queries until you are satisfied the tables meet your needs.
Then move to the queries. Open each one and look for problems (assuming you don't invest in the Search and Replace tool). They'll pop out at you and Access will raise errors when you try to run the queries. Get all of the queries cleaned up and working with the new field names. Add any fields you added to the tables.
Then turn your attention to the forms. Open them in design view and look for controls that show errors because they are bound to fields that are no longer available due to the name changes.And then work on the reports.
Somewhere along the line, you'll have to manually review all of the macros as well. That's probably going to be the hardest part because Access isn't going to show you where macro code refers to non-existent fields, I don't think. But by then, you should have a good handle on what you're looking for.
There will be some VBA in the template. Most templates use macros a lot, but they do include some VBA.
Add the directive Option Explicit to ALL modules and set the option to require it in all future VBA.This is crucial to writing clean code.
Just compile it as the first step. See if any errors are raised. Some might be for names you have changed.
At that point, you should have found and updated most, if not all, of the references to changed names.
