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 » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

linking/populating 3rd table though form



 
 
Thread Tools Display Modes
  #1  
Old April 13th, 2010, 01:45 PM posted to microsoft.public.access.forms
BenEl via AccessMonster.com
external usenet poster
 
Posts: 18
Default 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  
Old April 14th, 2010, 05:30 AM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default 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  
Old April 14th, 2010, 01:48 PM posted to microsoft.public.access.forms
BenEl via AccessMonster.com
external usenet poster
 
Posts: 18
Default 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  
Old April 14th, 2010, 11:49 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old April 15th, 2010, 02:08 PM posted to microsoft.public.access.forms
BenEl via AccessMonster.com
external usenet poster
 
Posts: 18
Default 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

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 03:20 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.