![]() |
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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 | |
|
|