ADO Seek with compond foreign key

[WantToKnowMore]

Hi all,

I've never used foreign keys for searching before... so I've researched all morning and I can't find any samples of how to use a unique foreign key that is comrpised of two long fields in an ADO recordset. Maybe it has to be DAO?

I want to add new rows to tblEmpSkills from data in two lists boxes, but only if the two fields together won't cause a duplicate entry in my receiving table.

As I'm step thru the code, and when the values would be duplicate and be found and not EOF - it just sails thru and ends up in the error routine.

How can I check for duplicate values in my foreign key and skip that entry if a duplicate exists?

Set cnn = CurrentProject.Connection
Set tblEmpSkills = New ADODB.Recordset
tblEmpSkills .CursorType = adOpenStatic
tblEmpSkills .LockType = adLockOptimistic
tblEmpSkills .Open "tblEmpSkills", cnn, , , adCmdTableDirect
tblEmpSkills .Index = "UniqueSkillSet" ' index is two long fields
Dim varItem As Variant
Dim varItem2 As Variant
' Enumerate through listbox selected items and if not duplicate on foreign key, add to tblEmpSkills.
' the two values from the lstbox contain the two long fields of the foreign key
'
For Each varItem In Me.lstEmps.ItemsSelected
For Each varItem2 In Me.lstTasks.ItemsSelected
lngValue = Me.lstEmps.ItemData(varItem) & Me.lstTasks.ItemData(varItem2)
tblEmpSkills .Seek lngValue, adSeekFirstEQ
If tblEmpSkills .EOF Then ' record doesn't exist, add it
tblEmpSkills .AddNew
tblEmpSkills .Fields("intEmployeeID") = Me.lstEmps.ItemData(varItem)
tblEmpSkills .Fields("intSkillID") = Me.lstTasks.ItemData(varItem2)
tblEmpSkills .Update
End If
Next varItem2
Next varItem

'
MsgBox "Work is done"

Thanks in advance for reading this and providing help! I'm really behind on solving this issue...

 

[PHV]

I'd replace this:
lngValue = Me.lstEmps.ItemData(varItem) & Me.lstTasks.ItemData(varItem2)
tblEmpSkills .Seek lngValue, adSeekFirstEQ
with this:
tblEmpSkills .Seek Array(Me.lstEmps.ItemData(varItem), Me.lstTasks.ItemData(varItem2)), adSeekFirstEQ

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WantToKnowMore]

Thanks for the help. I couldn't get it with the array embedded in the seek statement, but I split it out and that was the solution! Again - thanks very much...