Dlookup related records
in [Forms]
|
Prev: record Locking multi user
Next: Can not choose in combo
From: BenEl via AccessMonster.com on 31 May 2010 14:38 Hi. I'm having trouble thinking through the logic for this. Can someone point me in the right direction? I have a subform that the users can attach a Position (PK=PositionID) to a Candidate (PK=CandidateID). When this is done, the Candidate and Position IDs/information are populated in a third table "Interview". The subform has a textbox with : ="" & [Job Title] & " (" & [Status] & ")" This textbox has navigation buttons so the user can scroll through the positions that are attached to the candidate. I also have a combo box that lists all positions (that are open). Users use this combo box to attach a new position to the candidate. When the user chooses a position from the combo box, they click on a button to confirm. I currently have the DLookup on the confirm button: If Not IsNull(DLookup("[PositionID]", "MT_Interview", "[PositionID] = " & Me. SelectJob)) Then MsgBox "This position is already attached to this candidate. Please choose another position.", vbOKOnly Me.SelectJob.SetFocus End If The DLookup looks up ANY occurance of Position ID in the table. I would like to look up only those related to the CandidateID they are currently on. i know I need to add CandidateID in the lookup, but not sure how. Thanks for any help! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: ruralguy via AccessMonster.com on 31 May 2010 17:52 DLookup() will accept a compound Criteria. http://www.mvps.org/access/general/gen0018.htm BenEl wrote: >Hi. I'm having trouble thinking through the logic for this. Can someone point >me in the right direction? > >I have a subform that the users can attach a Position (PK=PositionID) to a >Candidate (PK=CandidateID). When this is done, the Candidate and Position >IDs/information are populated in a third table "Interview". The subform has >a textbox with : > >="" & [Job Title] & " (" & [Status] & ")" > >This textbox has navigation buttons so the user can scroll through the >positions that are attached to the candidate. I also have a combo box that >lists all positions (that are open). Users use this combo box to attach a new >position to the candidate. > >When the user chooses a position from the combo box, they click on a button >to confirm. I currently have the DLookup on the confirm button: > >If Not IsNull(DLookup("[PositionID]", "MT_Interview", "[PositionID] = " & Me. >SelectJob)) Then >MsgBox "This position is already attached to this candidate. Please choose >another position.", vbOKOnly >Me.SelectJob.SetFocus >End If > >The DLookup looks up ANY occurance of Position ID in the table. I would like >to look up only those related to the CandidateID they are currently on. i >know I need to add CandidateID in the lookup, but not sure how. > >Thanks for any help! -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via http://www.accessmonster.com
From: BenEl via AccessMonster.com on 1 Jun 2010 08:17 Thanks, I'll try it. ruralguy wrote: >DLookup() will accept a compound Criteria. >http://www.mvps.org/access/general/gen0018.htm > >>Hi. I'm having trouble thinking through the logic for this. Can someone point >>me in the right direction? >[quoted text clipped - 26 lines] >> >>Thanks for any help! > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1
From: BenEl via AccessMonster.com on 1 Jun 2010 08:42 It is working - sorta! It looks up the PositionID and returns the message box. It looks up the CandidateID and returns the message box. BUT... it is looking up the PositionID and CandidateID seperately. If there is a PositionID or CandidateID anywhere in the table it returns the message box. I need it to look for the PositionID that is in the table ONLY if it is attached to the current Candidate ID. How can i do this? Here is my current code: Private Sub Confirm_Click() If (IsNull(DLookup("[PositionID]", "MT_Job_Candidates", "[PositionID] = " & Me.SelectJob)) = False And IsNull(DLookup("[CandidateID]", "MT_Job_Candidates", "[CandidateID] = " & Me.CandidateID)) = False) Then MsgBox "This position is already attached to this candidate. Please choose another position.", vbOKOnly End If End Sub BenEl wrote: >Thanks, I'll try it. > >>DLookup() will accept a compound Criteria. >>http://www.mvps.org/access/general/gen0018.htm >[quoted text clipped - 4 lines] >>> >>>Thanks for any help! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1
From: ruralguy via AccessMonster.com on 1 Jun 2010 09:15
Try: If IsNull(DLookup("[PositionID]", "MT_Job_Candidates", _ "[PositionID] = " & Me.SelectJob & " AND [CandidateID] = " & Me.CandidateID)) Then <<< AIR CODE >>> BenEl wrote: >It is working - sorta! It looks up the PositionID and returns the message box. >It looks up the CandidateID and returns the message box. BUT... it is looking >up the PositionID and CandidateID seperately. If there is a PositionID or >CandidateID anywhere in the table it returns the message box. I need it to >look for the PositionID that is in the table ONLY if it is attached to the >current Candidate ID. How can i do this? Here is my current code: > >Private Sub Confirm_Click() > >If (IsNull(DLookup("[PositionID]", "MT_Job_Candidates", "[PositionID] = " & >Me.SelectJob)) = False And IsNull(DLookup("[CandidateID]", >"MT_Job_Candidates", "[CandidateID] = " & Me.CandidateID)) = False) Then > >MsgBox "This position is already attached to this candidate. Please choose >another position.", vbOKOnly >End If > >End Sub > > > >>Thanks, I'll try it. >> >[quoted text clipped - 3 lines] >>>> >>>>Thanks for any help! -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1 |