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
|
|||
|
|||
linking/populating 3rd table though form
Hi. I have a dbase with 3 tables CANDIDATES (CandidateID), POSITIONS
(PositionID), & INTERVIEWS. Both the CANDIDATES and POSITION tables have a 1 to many relationship with the INTERVIEW table. The INTERVIEW table links the other 2 tables. A CANDIDATE can apply for many POSITIONS just as a POSITION can have many CANDIDATES. You can have a CANDIDATE without a POSITION as well as a POSITION without a CANDIDATE. When you want to link a CANDIDATE with a POSITION you use the CandidateID and PositionID in the INTERVIEW table. In tables, everything works fine. The issue I am having is connecting CANDIDATES and POSITIONS through a form. My frmCandidate is tabbed. On the first tab are the fields from CANDIDATE table. On the second tab is a subform with the fields from the INTERVIEW table. I've included a combobox (with 2 columns) of the PositionName and PositionID with a command button below it. I would like the user to be able to use the combo box to select the position and then click the command button to attach that Candidate to that Position. How can I do this? Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201004/1 |
#2
|
|||
|
|||
linking/populating 3rd table though form
On Tue, 13 Apr 2010 12:45:47 GMT, "BenEl via AccessMonster.com"
u56454@uwe wrote: Excellent, well written question. Your db design is good, provided you use the Relationships window to create relations between the tables and enforce them, and that the PK for tblInterviews is over the combination of CandidateID and PositionID. I follow you all the way up to the command button. I don't see the need for it. The subform has tblInterviews as its recordsource (or a query on this table, not joining to other tables). The subform control's LinkMasterField and LinkChildField properties are set to CandidateID. This will populate tblInterviews.CandidateID. Your dropdown is in the subform and is bound to the tblInterviews.PositionID field. So the Interview record is created and populated (at least the PK fields). Then the user may fill out a few more member fields e.g. InterviewDate, and we're done. You now have an Interview record that joins the Candidate with the Position. -Tom. Microsoft Access MVP Hi. I have a dbase with 3 tables CANDIDATES (CandidateID), POSITIONS (PositionID), & INTERVIEWS. Both the CANDIDATES and POSITION tables have a 1 to many relationship with the INTERVIEW table. The INTERVIEW table links the other 2 tables. A CANDIDATE can apply for many POSITIONS just as a POSITION can have many CANDIDATES. You can have a CANDIDATE without a POSITION as well as a POSITION without a CANDIDATE. When you want to link a CANDIDATE with a POSITION you use the CandidateID and PositionID in the INTERVIEW table. In tables, everything works fine. The issue I am having is connecting CANDIDATES and POSITIONS through a form. My frmCandidate is tabbed. On the first tab are the fields from CANDIDATE table. On the second tab is a subform with the fields from the INTERVIEW table. I've included a combobox (with 2 columns) of the PositionName and PositionID with a command button below it. I would like the user to be able to use the combo box to select the position and then click the command button to attach that Candidate to that Position. How can I do this? Thanks! |
#3
|
|||
|
|||
linking/populating 3rd table though form
Right. The Interview subform does include the CandidateID and PositionID. The
PositionID (on the Interview subform), is only displayed for related records to that particular CandidateID on the main form. If the user wants to attach another Position ID to the Candidate ID, how can they do that? They would need to remember the Position ID in order to attach it, which isn't very user friendly. I'd like the user to see the list of positions and by clicking on it attach it to the Candidate. Does that make sense? Thanks. Tom van Stiphout wrote: Excellent, well written question. Your db design is good, provided you use the Relationships window to create relations between the tables and enforce them, and that the PK for tblInterviews is over the combination of CandidateID and PositionID. I follow you all the way up to the command button. I don't see the need for it. The subform has tblInterviews as its recordsource (or a query on this table, not joining to other tables). The subform control's LinkMasterField and LinkChildField properties are set to CandidateID. This will populate tblInterviews.CandidateID. Your dropdown is in the subform and is bound to the tblInterviews.PositionID field. So the Interview record is created and populated (at least the PK fields). Then the user may fill out a few more member fields e.g. InterviewDate, and we're done. You now have an Interview record that joins the Candidate with the Position. -Tom. Microsoft Access MVP Hi. I have a dbase with 3 tables CANDIDATES (CandidateID), POSITIONS (PositionID), & INTERVIEWS. Both the CANDIDATES and POSITION tables have a 1 [quoted text clipped - 14 lines] Thanks! -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
linking/populating 3rd table though form
On Wed, 14 Apr 2010 12:48:03 GMT, "BenEl via AccessMonster.com" u56454@uwe
wrote: Right. The Interview subform does include the CandidateID and PositionID. The PositionID (on the Interview subform), is only displayed for related records to that particular CandidateID on the main form. If the user wants to attach another Position ID to the Candidate ID, how can they do that? They would need to remember the Position ID in order to attach it, which isn't very user friendly. I'd like the user to see the list of positions and by clicking on it attach it to the Candidate. Does that make sense? You should put a Combo Box on the subform, based on the Positions table. Its bound column would be the PositionID; its only visible column would be the position name. The user can then simply go to the new, blank record at the bottom of the subform and select the desired position from the combo box. They never need to enter - or even *see* - the ID field. Use the tools Access provides! You don't have to reinvent everything from scratch, you don't need to memorize IDs, and you don't need any code at all for a lot of common tasks; the Access developers thought of a lot of this stuff a decade ago. You may want to check out Crystal's video or the other tutorials here, to get started with what Access can let you do: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#5
|
|||
|
|||
linking/populating 3rd table though form
Thank you so much!! I can't believe I was complicating things so much! Took
me 30 sec to set it up with no coding! Beautiful! John W. Vinson wrote: Right. The Interview subform does include the CandidateID and PositionID. The PositionID (on the Interview subform), is only displayed for related records [quoted text clipped - 3 lines] friendly. I'd like the user to see the list of positions and by clicking on it attach it to the Candidate. Does that make sense? You should put a Combo Box on the subform, based on the Positions table. Its bound column would be the PositionID; its only visible column would be the position name. The user can then simply go to the new, blank record at the bottom of the subform and select the desired position from the combo box. They never need to enter - or even *see* - the ID field. Use the tools Access provides! You don't have to reinvent everything from scratch, you don't need to memorize IDs, and you don't need any code at all for a lot of common tasks; the Access developers thought of a lot of this stuff a decade ago. You may want to check out Crystal's video or the other tutorials here, to get started with what Access can let you do: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201004/1 |
Thread Tools | |
Display Modes | |
|
|