Best place to input data

[Ruth]

I have been using a database for a while now and have been inputting data
into a table. However, now I want to add calculated fields to my data (which
I understand you cannot do in a table) and I want to see the calculation
results as I enter the data and its also helpful to see other data entries at
the same time.

So the options I think I have are as follows:-

1. Run a query on the current table, add the calculated fields, enter data
in the query in datasheet view.

2. Run a query on the current table, add the calculated fields, create a
form from the query and enter data on the form in datasheet view.

3. Create a form based on the current table, add the calculated fields to
the form and enter data in datasheet view.

As I have limited experience of databases and have so far tried to keep
things simple I would value the opinions of more experienced users!

 

[Rick Brandt]

I have been using a database for a while now and have been inputting
data into a table. However, now I want to add calculated fields to
my data (which I understand you cannot do in a table) and I want to
see the calculation results as I enter the data and its also helpful
to see other data entries at the same time.

So the options I think I have are as follows:-

1. Run a query on the current table, add the calculated fields,
enter data in the query in datasheet view.

2. Run a query on the current table, add the calculated fields,
create a form from the query and enter data on the form in datasheet
view.

3. Create a form based on the current table, add the calculated
fields to the form and enter data in datasheet view.

As I have limited experience of databases and have so far tried to
keep things simple I would value the opinions of more experienced
users!

3. Though I would use a continuous form rather than datasheet view. 1 and 2
would be seriously BAD ideas.

 

[BruceM]

Forms are for data entry. Tables, except maybe during the development phase
of a project, should not be used for data entry. I don't know why you
specify datasheet view for the forms, unless it's because you prefer that
view. A form's view (single form, continuous form, datasheet) does not
affect its functionality in terms of the data.
You can create a query based on the table, add the calculated fields, and
base a form (datasheet view or some other view) on the query. I'm not sure
why you specified running a query. The calculated query fields (which are
not stored) are updated as you go, so there is no need specifically to "run
the query". Just use the form, and updating should take care of itself.
You can also add expressions to the control source of unbound text boxes on
forms or reports, but it is best (and clearest when you are posting a
question) if you don't think of the text boxes as fields. Fields are in
tables, and the term is also used for queries. A form may have controls
such as text boxes, combo boxes, check boxes, etc. that are bound to fields,
but it does not itself contain fields. I usually find it more convenient to
use queries for calculations. For one thing, the results are available for
reports and for other forms without the need to enter the expression again.
If you are using an expression in several places and need to change it, you
only need to change it once if it is in a query.
Even if you are using expressions in a form (the technique does have its
uses), it is often best to base the form on a query, which allows you easily
to sort and filter the data.

 

[John Spencer]

Personally, I would design a continuous form for the data entry and use that
if at all possible.

You have much more control over things with a continuous form. If you are
limiting yourself to the three choices I would go with choice 3, but I would
use a query as the source of the records.

Remember that the calculated values are not going to be stored in the table
and will need to be re-calculated when you want to use that value in a
report or another form.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ruth]

Thank you for your prompt replies!

So if I've understood correctly, the best thing to do is to create a query
based on my table and use this to put my calculated fields into. Then create
a form based on the query which is where I will input my data.

I currently use my data table for reports and forms. Am I right in thinking
that all I have to do to move over to the "new system" is to just change the
data source to my new data inputting form (or will it be the query?).

The reason I have said I want datasheet view is because that is what we are
used to inputting onto and also you can look at other entries and check that
you are using the same format or wording if you are entering a similar item.

Could you please explain what you mean by a CONTINUOUS form? I'm only
familiar with just plain old forms!

I didn't mean to limit myself to the three options - they were just the ways
I thought I could do it - any better simple suggestions are welcome!

 

[BruceM]

First, create the Record Source. In this case, it is the query, including
any calculated fields. Next, create a form based on the query. You can use
the form wizard to get started, or you can use the Create Form in Design
View option. With this second option, click View > Properties after the new
form is started. You will see the Property Sheet, with tabs for Format,
Data, Event, Other, and All. You can also see this by switching to Design
View of a form (View > Design View), and clicking View > Properties if the
Property Sheet is not already visible.
In any case, on the Format tab of the Property Sheet you will see Default
View, where you can select from the various options, incuding Continuous. A
continuous form is one in which the records are displayed one above the
other. You view records by scrolling
To change or add a form's Record Source, click the Data tab on the Property
sheet (unless you use the wizard, in which case you will be prompted for the
Record Source when you create the form). However, keep in mind that a
Record Source is just that: a source of the records in a form or report.
The Record Source can only be a table or query. There are some nuances to
that last statement, but close enough for now. Your form will *have* a
record source, but it will not and can not *be* a record source.

For more information, check the Tips for Casual Users at this site:
http://allenbrowne.com/tips.html
Also, there are other sites listed at:
http://allenbrowne.com/links.html