A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Add new records to Table



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2010, 05:35 PM posted to microsoft.public.access.gettingstarted
JMay
external usenet poster
 
Posts: 300
Default Add new records to Table

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
  #2  
Old April 15th, 2010, 05:43 PM posted to microsoft.public.access.gettingstarted
JMay
external usenet poster
 
Posts: 300
Default Add new records to Table

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

"JMay" wrote:

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

  #3  
Old April 15th, 2010, 10:11 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Add new records to Table

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201004/1

  #4  
Old April 16th, 2010, 01:35 AM posted to microsoft.public.access.gettingstarted
JMay
external usenet poster
 
Posts: 300
Default Add new records to Table

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" wrote:

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201004/1

.

  #5  
Old April 16th, 2010, 05:32 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Add new records to Table

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.