John said:
If by "layout" you mean a display (perhaps editable, perhaps not) of
the data in your table, the proper tool is a Form.
Table datasheets are not designed for data display or editing. They're
very limited in their capabilities.
Forms, on the other hand, are EXTREMELY flexible and can be used to
"lay out" the data in your table (or tables) in a vast variety of
ways.
John W. Vinson[MVP]
Although I agree that forms are more flexible than tables for data
entry, I don't always find them to be the best solution. For example,
if I will be making entries to a table only a limited number of times,
constructing a Form may take more effort than it's worth, at least
initially. I can always add a nice Form later. The Form Wizard will
give you a Form, but there won't be anything extremely flexible about
the results -- you'll have to do some tweaking to get the flexibility.
What I frequently do, and I often do this even if I'm going to set up a
Form later, is to define a "Lookup" query for each field that might be
not very intuitive, including all foreign keys matching Autonumber fields.
For example, suppose I have an [Employees] Table with fields
[EmployeeID], [FirstName], and [LastName], among others. To link to it
from another Table, I define a [Q_LookupEmployeeID] query, with just two
fields, as:
SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Name
FROM Employees;
Then I add to each new related table an [EmployeeID] field of type
Number and set its Lookup property as follows:
Display Control: List Box
Row Source: Q_LookupEmployeeID
Bound Column: 1
Column Count: 2
Column Heads: No
Column Widths: 0;1
(You don't need to type this stuff more than once; to add the same field
to other tables, just copy the field's row in Design View and paste it
into the other table's Design View.)
Then I can start populating my new Table right away without having to
deal with ugly numeric keys. Instead of typing in employee number 3382,
I select (or begin typing) "Gates, Bill". This behavior migrates easily
to Queries, Forms, and Reports, and it's quick and easy to set up.
(Queries and Forms can be displayed in Datasheet View, too.) And
Datasheet View has the advantage over most Forms of being more compact
-- you can see more fields and records at one time, usually.
===
Another quick-and-dirty approach to data entry is to use an Excel
worksheet to populate the table -- Excel has a number of data-entry
shortcuts built in that you don't get in Access datasheets -- and then
import the finished table from Excel into Access. I frequently do this.
===
As another option, you might choose to *maintain* your table in Excel,
and just link to it in Access. This would be useful if you need to do
lots of arithmetic (or charts, PivotTables, or optimizations), or if you
have to re-filter your data frequently, as I think Excel's "AutoFilter"
facility is much easier to use than Access filters. But it requires you
to maintain two separate files for your data, which can be a nuisance.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
