Master/Detail Form (General Guidance)

[John Pritchard]

I'm trying to create a small application that will maintain
master records and there associated detail. I'm using DAO
and have got as far as adding / editing the master records
via a recordset to my master table. I'd like to create a
detail 'window / field' but not sure what to create.
Should I use a separate form or a combobox on the current
form? I only want to update/add records on the press of a
button (as my master currently does) when all
master/detail will be checked then submitted to the
database at the same time. I know this is vague but I'm
new to this and only looking for general guidance / simple
example. Thanks for any help !

 

[John Vinson]

I'm trying to create a small application that will maintain
master records and there associated detail. I'm using DAO
and have got as far as adding / editing the master records
via a recordset to my master table.

Have you considered using the out-of-the-box tools in Access? It is
NOT essential (though sometimes it's desirable, in special cases) to
write DAO code to get data from a form to a Table.

Typically one would simply use a bound Form for the master table, with
a Subform for the detail.

 

[John Vinson]

Thanks for the reply! I can be a bit more specific about
my problem now....

The out of the box tools automatically update my tables -
hence my use of DAO which seemed to mean I could control
when data was added/updated in the database.

Well... you can control it without going to unbound forms.

The requirement I have is for a control total on the master
which must match the sum of the totals in the detail. The
relationship is one to many so I don't know beforehand the
number of detail records. I can create a form with default
view continuous and if this is linked to a table it seems
to behave OK from the appearance point of view - adding
extra lines as the user enters data - BUT my details table
now has records that may not match the master total. So....

Why does the master total have to be entered? It seems redundant; is
this just a validity check or what?

1. Can I somehow create a form that will allow possibly
many detail lines to be added/amended without commiting to
the database until a button is pressed and all the
integrity checks have been made ?

Only by storing those records in a temp table, or (for AccessXP) a
disconnected recordset which I've never used.

2. Could I use a temp table then copy the records to the
real tables if all is well ? - This seems tortuous esp as
the application will be multiuser so user id will have to
be an extra key in the temp table.

If you go that route, keep the temp table in the user's desktop
frontend (you REALLY REALLY need to use a split database architecture,
with shared tables in a backend and everything else in a frontend); or
put the temp table in a second backend, created as needed on the
user's local machine.

 

[John Vinson]

Thanks,

I'm new to access - but not databases. So

1. Given I need to ensure referential integrity and yes
the header total is a control what is the recommended way
to go?

Referential integrity is usually maintained (in Access, SQL, Oracle,
DB/2...) at the table level. It may ALSO be maintained at the form
level (e.g. to spare users from cryptic error messages or trap
violations sooner). My concern about this field (if I understand
correctly) is that you're maintaining RI between a manually entered
field and *derived data* - a Count - which would ordinarily not be
stored in a table at all. If you do need this check then I agree, you
are very likely doing it correctly, using unbound forms.

2. My experience is with databases engines where I check
all data is correct (in a transaction) before commiting to
the database. I obviously need to change my thinking as
the whole design philosophy seems different here. How can
I control it using a form bound to a table ? As you
suggest this method I'd like to follow your advice.

The ONLY sticking point here, that I can see, is the (what I see as
nonstandard) RI between a table field and an aggregated value. RI is
generally between fields of table data. Again... if that's ESSENTIAL,
at the RI level (as opposed to just warning the user), you may well be
stuck. If you do want to do it, a temp table as the recordsource for
the subform with code to migrate the data into the production table
when the data is satisfactory is probably the best way to do it.

3. Are there any examples about I could look at ? I see
this sort of master/detail form as fundamental as is
keeping the database void of junk input so it must be easy
to achieve.

I guess that's where I do see things differently. If I'm entering
(say) invoice items, I don't sit down beforehand and determine that
there are 83 detail lines, type 83 into a textbox on the mainform, and
then expect Access to complain if I enter 82 or 84. Instead I treat a
count of records AS A COUNT; I enter however many lines there are, and
(if it's relevant) display that count on the screen.