Data Entry In Normalized Database

[Wayne]

I am designing a database which needs approximately 50 fields per
record. The database user requires data entry to be via a single
screen.

If I follow good database design practice and split the 50 fields into
several smaller tables, is there any other way to enter the data on the
single data entry screen other than having several subforms with the
individual tables as their recordsource?

[Salad]

Wayne wrote:

I am designing a database which needs approximately 50 fields per
record. The database user requires data entry to be via a single
screen.

If I follow good database design practice and split the 50 fields into
several smaller tables, is there any other way to enter the data on the
single data entry screen other than having several subforms with the
individual tables as their recordsource?

Yes. In a form you can enter a RecordSource (select statement or query
or table) that the form is bound to or leave the Recordsource blank
(unbound form).

Is there anything wrong with 50 fields in a record? Why do you feel you
need to break the record up? Usually subforms are for 1-many
relationships. Ex: you have an order but an order has 1 or many items.
If this is the case. Create a form for the order and a form for the
subform. When you drop the subform into the main form, set a link
between the orderid.

[Wayne]

Thanks for the reply. You are right - after looking at it I probably
don't need to break the record up, except for one field where I need to
add several witnesses per record. I've added a subform for witnesses
to the main data entry form but have come up against a problem.

To stop the user leaving any fields blank, I have set the "required
property" to "yes" for all fields in the underlying data table for the
main form. The witnesses subform data needs to be added in the middle
of the data entry flow and as soon as the main form losses focus I get
the error telling me that such and such a field (on the main form)
can't contain a null value ......

I know that this is to be expected because I'm trying to leave the main
form with out completing all of the mandatory data. Is there a simple
way around this?

[paii, Ron]

"Wayne" <cq*******@volcanomail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks for the reply. You are right - after looking at it I probably
don't need to break the record up, except for one field where I need to
add several witnesses per record. I've added a subform for witnesses
to the main data entry form but have come up against a problem.

To stop the user leaving any fields blank, I have set the "required
property" to "yes" for all fields in the underlying data table for the
main form. The witnesses subform data needs to be added in the middle
of the data entry flow and as soon as the main form losses focus I get
the error telling me that such and such a field (on the main form)
can't contain a null value ......

I know that this is to be expected because I'm trying to leave the main
form with out completing all of the mandatory data. Is there a simple
way around this?

If witnesses is a single field, try using inputbox() to allow the user to
input the data, then user docmd.runsql to write it into the witness table.

If you need more fields, create a form with those fields and open it as a
dialog box for user input.

Possessing stops on the current form whenever a dialog box is open.

[Alan Webb]

Wayne,
If you use a query to present all fifty fields and some of the fields
represent 1-M relationships then rows from the one side of the relationship
will repeat for every row on the many side of the relationship. This may be
interpreted as duplicate information by your users. Form/Subform is an
elegant way to present 1-M related data and you should think about using it.
If you play with the look (formatting) of the nested subforms it is possible
to make it seem like it is all one form for the user. It's not necessary to
tell the user that you pulled off a bit of slight of hand to make this
happen. Just build it and present it as their single form. If all goes
well you get your "atta boy"s and "job well done"s. Last, think about how
you are going to print out the data for these fifty fields and whether your
current set of tables is the best arrangement for printing or reporting the
data that is captured.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Wayne" <cq*******@volcanomail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I am designing a database which needs approximately 50 fields per
record. The database user requires data entry to be via a single
screen.

If I follow good database design practice and split the 50 fields into
several smaller tables, is there any other way to enter the data on the
single data entry screen other than having several subforms with the
individual tables as their recordsource?

[Salad]

Wayne wrote:

Thanks for the reply. You are right - after looking at it I probably
don't need to break the record up, except for one field where I need to
add several witnesses per record. I've added a subform for witnesses
to the main data entry form but have come up against a problem.

To stop the user leaving any fields blank, I have set the "required
property" to "yes" for all fields in the underlying data table for the
main form. The witnesses subform data needs to be added in the middle
of the data entry flow and as soon as the main form losses focus I get
the error telling me that such and such a field (on the main form)
can't contain a null value ......

I know that this is to be expected because I'm trying to leave the main
form with out completing all of the mandatory data. Is there a simple
way around this?

Well...you could create an unbound form instead of a bound form. A
bound form has a recordsource, the unbound does not. You would need to
have a way to save and insert the data into a table via a command button
to save or undo. If you undo, you'd need to delete any subform
"witness" records.

There is no easy way that I see where you can start entering data on the
main record then go to the many records subform without calling the
BeforeUpdate event of the Main form and saving/canceling the record.

Check out unbound forms. It's about the best advice I can provide. It
will require work on your part, but you can do it if you have the patience.

[Wayne]

Thanks for the feedback and help. I will try these ideas and see how I
go.