field defaults for faster data entry

[Guest]

I am creating a database to contain and later analyse data collected for a
silviculture research project. There are several tables, but the biggest one
contains individual tree measurements as records. Each tree is uniquely
identified by the fields "plot number" and "tree number." Each plot contains
30 to 80 trees, numbered consecutively WITHIN plot (ie: tree numbers start
over at 1 for each new plot). As a result of this structure, the entry for
the "plot number" field is repeated across records 20 to 80 times, and "tree
number" similarly appears as repeated consecutive sets.

I would like to save myself the time and potential error of re-typing these
fields every time. What I want is to type those entries only for the first
record of a new plot, with them being automatically filled in for the
remaining plot entries until I start on a new plot (there are hundreds of
plots).

It seems to me that this ought to be something I could arrange for in table
design view - under default values for field properties. I simply want the
"plot number" field to default to the previous entry, and the "tree number"
field to default to the previous entry plus 1. It seems like a
straightforward demand of the software, and one that must come up fairly
often, but I cannot figure out how to do it. I would appreciate any help
offered.

 

[John Spencer]

You can do that on a form with code, but you cannot do that in Access
tables.

Using a continuous form as an entry device, you could have code in the after
update event that sets the default values during an entry session. The
tough part would setting the Tree number back to one when you switched plots

One way to enter the value of the previous record automatically is to add
code the after update event of the form that sets the default to the
current value in the control.
Assumptions: Plot and Tree are both number fields (not text fields that
happen to contain number characters)

UNTESTED Sample code:
Private Sub Form_AfterUpdate()
Me.txtPlot.DefaultValue = Me.txtPlot
Me.txtTree.DefaultValue = 1 + Nz(DMax("Tree","YourTable","Plot=" &
me.Plot),0)
End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John,

Thank you for your very fast reply.

I am pretty new to using access (at least for creating my own database -
I've entered data on others' many times before), but I know enough to
remember that a form allows entry of only one record at a time. For my
purposes, I prefer a table, which allows the user to enter data by fields,
rather than only by records (down the columns, one after the other). I find
this faster and less error prone - as the eye moves back and forth between
the (paper) data sheet and the computer screen, it is less apt to
accidentally 'jump' from one line to the next when working vertically
(adjacent columns are usually different kinds of data, often with different
formats, precision, or ranges of values) rather than horizontally (adjacent
rows are the same, so an accident is easier to make and unlikely to be
noticed).

Would it be better to enter data in excel (where I can click and drag to
fill in repetitive or sequential entries), and then import into access? I'm
worried that this might make it hard for me later, somehow, to use some of
access's relational advantages when it comes time for analysis. Would I be
better off just sucking it up and entering record-by-record (across many
thousands)?

Thanks!

--Justin--

 

[John Spencer]

Access forms can be single (one form visible at a time) or continuous (many
copies of the form in a scrollable list)

When you create the form you can set it up so that it has a header row and
all the entry controls in a line, so it looks very much like the datasheet
view of a table or query.

So you could without much trouble set the form up to give you something like
what you are requesting. As a matter of fact if you use the form wizard,
you can choose "Tabular" layout on the 2nd or 3rd step and the wizard will
create this layout for you. Once created, you may want to modify it a bit -
your choice.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..