Message to inform a record already exists (1 Viewer)

lithium1976

Registered User.
Local time
Yesterday, 21:03
Joined
May 28, 2010
Messages
12
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?

Code:
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

Enthusiastic Amateur
Local time
Today, 05:03
Joined
Sep 21, 2011
Messages
10,838
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

..forever waiting... waiting for jellybean!
Local time
Today, 12:03
Joined
May 7, 2009
Messages
16,775
Code:
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
 

Users who are viewing this thread

Top Bottom