Add new records to Table

[JMay]

I have tblMinisters with MinisterID (PK) and MinisterName, +++(with 50 records)

I have tblChurches with ChurchID(PK) and ChurchName, +++(with 7 records)

I have created new tblMatchMinisterToChurch with MinisterToChurchID(PK),
MinisterID, ChurchID

Currently things are set up as follows(which can easily be wrong since I
don't know what I'm doing..., but)

(I have a Query That's identical to this - qryMinistersToChurches (thinking
I needed one)

I have created a form frmMinisterToChurches with qryToChurches as
RecordSource and I have 2 combobox controls on it;

the 1st cb I have created cboMinisterID has:
Control Source: MemberID
Row Source: qryShowMinistersNames
Row Source Type: Table/Query
Bound Column: 1
Column Count: 2
Column Widths: 0";2.5"
etc

My qryShowMinistersNames looks like this:
MinistersID FullName

the 2nd cb I have created cboChurchesID has:
Control Source: ChurchID
Row Source: qryShowChurchNames
Row Source Type: Table/Query
Bound Column: 1
Column Count: 2
Column Widths: 0";3"
etc

My qryShowChurchNames looks like this:
ChurchID ChurchName

In My frmMinisterToChurches I want to:

1) Select a Minister (Fullname Shows) from the drop-down
2) Select a Church (FullChurch Name Shows) from the drop-down

after doing this -- do whatever (Maybe clicking a command button) to have
the unique combination selcted added to the Table (perhaps through the query)
tblMinistersToChurches. I am totallu confused, but if I learn this It's
liable to open up new opportunities...

There are just "Too many" things that have to be right for this to work, and
I'm too new to this to know. If a commandbutton is the answer to adding a
new combination what code should be behind it?

Thanks do much - sorry for the length of this -- but there are no shortcuts
in getting all this out.

TIA,

Jim

 

[JMay]

CORRECTION of: where I said...
after doing this -- do whatever (Maybe clicking a command button) to have
the unique combination selcted added to the Table (perhaps through the query)
tblMinistersToChurches <<< Correction s/b below:

tblMatchMinisterToChurch

 

[KenSheridan via AccessMonster.com]

Jim:

Rather than having a separate form for assigning ministers to churches I'd
suggest a churches form (based on the tblChurches table) and within it a
subform based on the tblMatchMinisterToChurch table.

The churches form should be in single form view and have controls for the
church name and any other non-key fields from the table. The subform should
be in continuous form view, so first create this as a separate form and then
embed it in the main churches form. The subform needs only the one control,
your cboMinisterID, whose ControlSource is MinisterID (I assume that MemberID
was a typo). Design the subform so its just one row deep, but when embedding
it in the man form make it deep enough to show several rows at least. Set
the LinkMasterFields and LinkChildFields properties of the subform control in
the main churches form to ChurchID.

When you navigate to a church record in the main form the subform will show
all ministers assigned to the church in question. To assign a new one is
simply a case of entering another record in the subform by going to the blank
record at the bottom and selecting a minister in the combo box.

Ken Sheridan
Stafford, England

 

[JMay]

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

 

[KenSheridan via AccessMonster.com]

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 procedure:

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

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]