Message to inform a record already exists

[lithium1976]

Hello,

I have three tables on my database - tblPersons, tblJobs and tblLinkPersonToJobs. It is a many to many relationship via the link table as many persons can be linked to many jobs.

I have two list boxes on my form (list0 and list35) where I select at least one person from list0 (tblPersons) and then I select a job to match them to from list35 (tblJobs).

List0 allows for multiple selection of persons. The persons and selected jobs are then added to the link table when a button is pressed on the form.

If a person (or persons) are already matched to a job (eg a potential duplicate job match) I have a message box which pops up I forming me of this and the routine stops. But I want the message to inform me which person (or persons) have already been added to that job.

Can anyone help me with the code for the message box?

Dim strSQL          As String
Dim db                As DAO.Database
Dim rst                As DAO.Recordset
Dim ctl                As Control
Dim varItem        As Variant

On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLinkPersonToJobs", dbOpenDynaset, dbAppendOnly)

'make sure at least one person has been selected
If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least one person"
Exit Sub
End If

If Not IsNumeric(Me.List35) Then
MsgBox "Must select a job"
Exit Sub
End If

‘list0 is a multi-select listbox to select multiple persons and list35 is to select a job
If Nz(DLookup("personID", " tblLinkPersonToJobs ", "personID = " & Me.List0.Column(0) & " And jobID = " & List35), 0) = 0 Then
Set ctl = Me.List0
For Each varItem In ctl.ItemsSelected
rst.AddNew
rst!personID = ctl.ItemData(varItem)
rst!jobID = Me.List35
rst.Update
Next varItem

Else

MsgBox "Warning! The selected person or persons have already been added to this job!"
Exit Sub
End If

 

[Gasman]

So DLookUp the person's name and display that?
However you are only checking on whatever list item is selected last?
I would have thought you would need to check each as you move through the list?

 

[arnelgp]

Dim strSQL          As String
Dim db                As DAO.Database
Dim rst                As DAO.Recordset
Dim ctl                As Control
Dim varItem        As Variant

On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLinkPersonToJobs", dbOpenDynaset)

'make sure at least one person has been selected
If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least one person"
Exit Sub
End If

If Not IsNumeric(Me.list35) Then
MsgBox "Must select a job"
Exit Sub
End If

'list0 is a multi-select listbox to select multiple persons and list35 is to select a job
'If Nz(DLookup("personID", " tblLinkPersonToJobs ", "personID = " & Me.List0.Column(0) & " And jobID = " & List35), 0) = 0 Then
Set ctl = Me.List0

'arnelgp
'holds the name of the person where he/she has already the job
Dim msg As String
For Each varItem In ctl.ItemsSelected
    With rst
        .FindFirst "PersonID = " & ctl.ItemData(varItem) & " and jobID = " & Me.list35
        If Not .NoMatch Then
            msg = msg & ctl.Column(1) & vbCrLf
        Else
            .AddNew
            !personID = ctl.ItemData(varItem)
            !jobID = Me.list35
            .Update
        End If
    End With
Next varItem

If Len(msg) <> 0 Then
    msg = "The following person(s) was/were not added since they already have the job!" & vbCrLf & vbCrLf & msg
    MsgBox msg, vbInformation + vbOKOnly
End If

'Else
'
'MsgBox "Warning! The selected person or persons have already been added to this job!"
'Exit Sub
'End If