 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / April 2008Using Queries better for creating Data Entry Forms?
I am just setting up a brand new database and am building the forms to be used for data entry, editing, etc. Since all the fields will be needed on each form is there any reason to base the form on a query of all the fields in the table rather than just using the table itself? If so, I notice the attachment fields have a number of fields associated with them that seem to clutter up the resulting form - where I don't see all the individual pieces when I build the form based on the table... Do I need to keep those? If not, what is the preferred method for modyifying the form fields. Thanks for any help. > I am just setting up a brand new database and am building the forms > to be used for data entry, editing, etc. Since all the fields will be [quoted text clipped - 5 lines] > based on the table... Do I need to keep those? If not, what is the > preferred method for modyifying the form fields. Thanks for any help. If you need all of the fields then the only advantage to using a query would be to impose a more reliable sort order on the records as shown in the form and/or to have a criteria applied. An example of the latter might be a case where you don't allow "hard" delettions of records, but rather just set a flag field in the table to indicate that the record is "voided" or similar. Your form could then never display "voided" records simply by having that in the criteria of the underlying query. Both sorting and filtering can also be done at the form level using a table as its RecordSource, but the sorting from a query is more reliable (the user can't accidentally change it) and filtering at the form level could also be turned off by the user and in some cases that is not wanted. More likely you might want the user to be able to apply and remove various filters of his own and if you want some criteria to always be applied then having that in the query is the most practical way to achieve that.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Okay... so a Form based on Query looks a bit different and one of the things (besides the header) is that attachment fields bring over all their "extra" stuff with them that you don't normally see when you create a form from a table. Are these extra fields necessary... and if so is there a way to "hide" them from the user? It creates a very confusing form. Though to be honest, I'm not inclined to use the query to create the form at this point but I would like the information in case I later decide to use this approach. Thanks for your help! > If you need all of the fields then the only advantage to using a query would > be to impose a more reliable sort order on the records as shown in the form [quoted text clipped - 11 lines] > his own and if you want some criteria to always be applied then having that > in the query is the most practical way to achieve that. Forms should, in most cases, be based on queries, even if only a single table is involved. Besides the ease of sorting, which can include multi-field sorts, and the ability to retrieve subsets of data, such as active records only, you have the advantage of being able to use calculated fields in queries. One simple example would be in the handling of names. Best practice dictates entering LastName and FirstName in separate fields, but there's many situations where you'd want to the name to appear as a single unit. You could do this at the form/report level *every single time* you need it to happen, using Me. CompleteName = FirstName & " " & LastName or you could *do it once* in your query, using CompleteName: FirstName & " " & LastName in which case you simple refer to the calculated field CompleteName everytime you need it in that format. Another example, using names again, would be if you wanted to use a combobox to retrieve a person's data based on their name. Using the combobx wizard takes less than a minute, but if your combobox, for example, includes the fields LastName FirstName Address City State Zip with the combobox being bound to the LastName, you'll run into problems if you have more than one person with the same LastName. With this setup, if you have persons named Adams Aaron Adams Benjamin Adams Charles Adams Dale Access will always retrieve the data for Adams Aaron even if you select Adams Dale because Access is looking for the *first occurrence* of the bound field, in this case Adams. But if you once again have a calculated field in a query RetrievalName: LastName & " " & FirstName and bind your combobox to the calculated field RetrievalName, it will retrieval data for the field you selected. And there are many other, everyday applications for this type of data manipulation. SignatureThere's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 > Okay... so a Form based on Query looks a bit different and one of the > things (besides the header) is that attachment fields bring over all [quoted text clipped - 5 lines] > information in case I later decide to use this approach. Thanks for > your help! Sorry, but attachment fields are new in Access 2007 of which I have little experience. So far I have only used it to diagnose things in my apps that don't work in 2007 and for testing. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.