Jim:
Its certainly possible, and very simply done.
With your separate frmMinisterToChurches form your two text boxes are quite
correct. You could simply make the tblMatchMinisterToChurch table the form's
RecordSource, but the disadvantage of that would be that the records in the
form would not be sorted in any logical order. Better would be to use a
query as the form's RecordSource so that the records can be ordered logically.
I'd imagine that the most appropriate sort order would be by church name so
that all ministers assigned to a church would be grouped together. Such a
query would use the tblMatchMinisterToChurch and tblChurches tables, joined
on ChurchID. Add the ChurchID and MinisterID fields from
tblMatchMinisterToChurch to the query; also add the ChurchName field and sort
this in ascending order. Uncheck the 'show' checkbox for this column in
query design view as its purely to sort the records, not to be shown in the
form.
All you have to do to assign a minister to a church is select the minister
and church in the combo boxes. The record will be automatically saved when
you move to another record or close the form; no code is needed, but one
refinement you might add would be to requery the form when a record is
updated; this would include adding a new record or amending an existing one,
e.g. moving a minister from one church to another. By requerying the form
the new or amended record would jump to its correct position in the sort
order in the form and the focus would move to the form's first record. This
just needs one line of code in the form's AfterUpdate event procedu
Me.Requery
The form can be in single form view, or continuous forms view, but my
inclination would be to use the latter as you can then see all ministers
assigned to a church without having to move between individual records.
One other thing you should do is create a unique index on the MinisterID and
ChurchID fields in the tblMatchMinisterToChurch table to prevent the same
minister being assigned to the same church more than once. In pre 2007
versions this is done in table design view by selecting Indexes from the View
menu. In the dialogue which opens enter a suitable name for the index in the
first empty row of the Index name column, ChurchMinister say. In the Field
Name column of the same row enter ChurchID, and with this row still selected,
in the Index Properties below, select 'Yes' as the Unique property. Then
enter MinisterID in the Field Name column in the row immediately beneath
ChurchID. Don't enter anything in the Index name column for this row. Close
the dialogue and save the table design. The interface will differ in Access
2007 of course, but I'd imagine its broadly similar.
Ken Sheridan
Stafford, England
JMay wrote:
Ken,
Thanks. I will likely go with your suggestion, but so that I can better
understand and learn more about this, can you audit my steps I gave
originally and let me know if it is possible, and HOW? This way I will
better learn the product. Thanks,
Jim
Jim:
[quoted text clipped - 80 lines]
Jim
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201004/1