A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using Queries better for creating Data Entry Forms?



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2008, 04:15 AM posted to microsoft.public.access.forms
MStadnik
external usenet poster
 
Posts: 16
Default Using 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.
  #2  
Old April 17th, 2008, 12:48 PM posted to microsoft.public.access.forms
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Using Queries better for creating Data Entry Forms?

MStadnik wrote:
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.


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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #3  
Old April 17th, 2008, 01:27 PM posted to microsoft.public.access.forms
MStadnik
external usenet poster
 
Posts: 16
Default Using Queries better for creating Data Entry Forms?

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!


"Rick Brandt" wrote:

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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #4  
Old April 17th, 2008, 02:08 PM posted to microsoft.public.access.forms
Linq Adams via AccessMonster.com
external usenet poster
 
Posts: 1,474
Default Using Queries better for creating Data Entry Forms?

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.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200804/1

  #5  
Old April 17th, 2008, 11:59 PM posted to microsoft.public.access.forms
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Using Queries better for creating Data Entry Forms?

MStadnik wrote:
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!


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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.