Dlookup related records

[BenEl via AccessMonster.com]

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 via AccessMonster.com]

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?

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!

 

[BenEl via AccessMonster.com]

Thanks, I'll try it.

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!

 

[BenEl via AccessMonster.com]

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.

DLookup() will accept a compound Criteria.
http://www.mvps.org/access/general/gen0018.htm

[quoted text clipped - 4 lines]

 

[ruralguy via AccessMonster.com]

Try:
If IsNull(DLookup("[PositionID]", "MT_Job_Candidates", _
"[PositionID] = " & Me.SelectJob & " AND [CandidateID] = " & Me.CandidateID))
Then

<<< AIR CODE >>>

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]

 

[BenEl via AccessMonster.com]

It's producing the same results. If the PositionID is in the table (even
attached to another CandidateID), it brings back that message box. The only
time it doesn't is if the PositionID is not yet in the table attached to ANY
CandidateID.

Any other ideas?

Try:
If IsNull(DLookup("[PositionID]", "MT_Job_Candidates", _
"[PositionID] = " & Me.SelectJob & " AND [CandidateID] = " & Me.CandidateID))
Then

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

[quoted text clipped - 22 lines]

 

[ruralguy via AccessMonster.com]

It should *only* return a PositionID when BOTH the PositionID AND the
CandidateID for that record match. You may have overlooked something in the
implementation.

It's producing the same results. If the PositionID is in the table (even
attached to another CandidateID), it brings back that message box. The only
time it doesn't is if the PositionID is not yet in the table attached to ANY
CandidateID.

Any other ideas?

Try:
If IsNull(DLookup("[PositionID]", "MT_Job_Candidates", _

[quoted text clipped - 8 lines]

 

[BenEl via AccessMonster.com]

What could I have overlooked? I spent a long time trying to figure that out
and can't seem to see anything. Any clue?

It should *only* return a PositionID when BOTH the PositionID AND the
CandidateID for that record match. You may have overlooked something in the
implementation.

It's producing the same results. If the PositionID is in the table (even
attached to another CandidateID), it brings back that message box. The only

[quoted text clipped - 8 lines]

 

[ruralguy via AccessMonster.com]

Copy and paste *exactly* what you have into a post here.

What could I have overlooked? I spent a long time trying to figure that out
and can't seem to see anything. Any clue?

It should *only* return a PositionID when BOTH the PositionID AND the
CandidateID for that record match. You may have overlooked something in the

[quoted text clipped - 5 lines]

 

[BenEl via AccessMonster.com]

Private Sub Confirm_Click()

If IsNull(DLookup("[PositionID]", "MT_Job_Candidates", "[PositionID] = " & Me.
SelectJob & " AND [CandidateID] = " & Me.CandidateID)) Then
MsgBox "This position is already attached to this candidate. Please choose
another position.", vbOKOnly

End Sub

Copy and paste *exactly* what you have into a post here.

What could I have overlooked? I spent a long time trying to figure that out
and can't seem to see anything. Any clue?

[quoted text clipped - 4 lines]

 

[John W. Vinson]

Private Sub Confirm_Click()

If IsNull(DLookup("[PositionID]", "MT_Job_Candidates", "[PositionID] = " & Me.
SelectJob & " AND [CandidateID] = " & Me.CandidateID)) Then
MsgBox "This position is already attached to this candidate. Please choose
another position.", vbOKOnly

End Sub

Your expression is finding positions which are *NOT* attached to the candidate
(the DLookUp will return the position ID if there is a hit, and be null
otherwise). Change the line to

If Not IsNull(DLookup( <etc etc>

 

[BenEl via AccessMonster.com]

Oh my goodness!! Thankyou! (what a silly mistake on my part!!). It works
beautifully now!

Private Sub Confirm_Click()

[quoted text clipped - 4 lines]

End Sub

Your expression is finding positions which are *NOT* attached to the candidate
(the DLookUp will return the position ID if there is a hit, and be null
otherwise). Change the line to

If Not IsNull(DLookup( <etc etc>