Solved Issue with checking duplicate values (1 Viewer)

[nashaz]

This question is in continuation to a solved thread: https://www.access-programmers.co.u...n-a-value-to-this-object.327824/#post-1878884

For the same source and target forms, I am trying to check if the combination of PositionID and CourseID already exists in the table, if so, undo the last change.
Now there are 3 problems, based on which code I use.

The first code I have under the target form for this purpose is:

Private Sub cboCourse_BeforeUpdate(Cancel As Integer)
    
    Dim c, d As Long
          
    c = DCount("PositionID", "RoleXCourse_JT", "PositionID=" & Forms!frmTraining1.Form.cboPositionID)
    d = DCount("courseid", "RoleXCourse_JT", "CourseID = " & Forms!frmTraining1.Form.ListRole.Column(3))
 
    If Me.NewRecord And c > 0 And d > 0 Then
        MsgBox "WARNING! This course has already been assigned with this role as mandatory. Select a different course.", vbOKOnly + vbInformation, "Duplicate record alert!"
        Me.Undo
    End If

End Sub

Column(3) is the column with CourseID in the row source of the listbox. The above produced a syntax error (missing operator) in query expression 'CourseID = '.

If I have the following code:

Dim c, d, e As Long
                
    e = Forms!frmTraining1.Form.ListCourse.Column(3)
    
    c = DCount("positionid", "RoleXCourse_JT", "PositionID=" & frmTraining1.Form.cboPositionID)
    d = DCount("courseid", "RoleXCourse_JT", "CourseID = " & e)
    
    If Me.NewRecord And c > 0 And d > 0 Then
        MsgBox "WARNING! This course has already been assigned with this role as mandatory. Select a different course.", vbOKOnly + vbInformation, "Duplicate record alert!"
        Me.Undo
    End If

I get the error Invalid use of null.

Lastly, if I have the following:

Dim c, d, e As Long
                
    e = 123 'any random numeral
    
    c = DCount("positionid", "RoleXCourse_JT", "PositionID=" & frmTraining1.Form.cboPositionID)
    d = DCount("courseid", "RoleXCourse_JT", "CourseID = " & e)
    
    If Me.NewRecord And c > 0 And d > 0 Then
        MsgBox "WARNING! This course has already been assigned with this role as mandatory. Select a different course.", vbOKOnly + vbInformation, "Duplicate record alert!"
        Me.Undo
    End If

I get the msgbox no matter what course I select.

As always, really appreciate everyone's help.

 

[Gasman]

You are not checking combinations, but whether either exist, independent of the other.
In what form is this code in?

 

[Solo712]

The qualifying test cannot be written like that. Your "c" will always be positive if there is a record with a PositionID of that value irrespective of the CourseID, and vice versa. So you will always get the message if column(3) ListCourse is not Null.

Try this:

c = DCount("positionid", "RoleXCourse_JT", "PositionID=" & frmTraining1.Form.cboPositionID & _
            " AND CourseID = " & Forms!frmTraining1.Form.ListCourse.Column(3) & ")"

If Me.NewRecord And c > 0 Then...

Best

 

[nashaz]

You are not checking combinations, but whether either exist, independent of the other.
In what form is this code in?

I was under the impression that the and condition will look for combinations
this code is under the target form's course combobox, beforeupdate event.

 

[nashaz]

The qualifying test cannot be written like that. Your "c" will always be positive if there is a record with a PositionID of that value irrespective of the CourseID, and vice versa. So you will always get the message if column(3) ListCourse is not Null.

Try this:

c = DCount("positionid", "RoleXCourse_JT", "PositionID=" & frmTraining1.Form.cboPositionID & _
            " AND CourseID = " & Forms!frmTraining1.Form.ListCourse.Column(3) & ")"

If Me.NewRecord And c > 0 Then...

Best

Makes sense. Let me give this a go and get back to you. Thanks Solo

 

[June7]

Make sure there are inputs for both course and position before validating combination. Probably need code triggered by both controls. Put it in a procedure that can be called by both.

 

[Gasman]

I was under the impression that the and condition will look for combinations
this code is under the target form's course combobox, beforeupdate event.

No, that AND checks if both exist, not necessarily together.

 

[nashaz]

The qualifying test cannot be written like that. Your "c" will always be positive if there is a record with a PositionID of that value irrespective of the CourseID, and vice versa. So you will always get the message if column(3) ListCourse is not Null.

Try this:

c = DCount("positionid", "RoleXCourse_JT", "PositionID=" & frmTraining1.Form.cboPositionID & _
            " AND CourseID = " & Forms!frmTraining1.Form.ListCourse.Column(3) & ")"

If Me.NewRecord And c > 0 Then...

Best

Hi Solo

Apologies for delay in getting back. I have tried the approach you suggested but I still get the msgbox. Any ideas why this might be?
Thanks

 

[cheekybuddha]

Try it this way:

Private Sub cboCourse_BeforeUpdate(Cancel As Integer)
   
    Dim AlreadyExists As Boolean
         
    AlreadyExists = DCount( _
      "*", _
      "RoleXCourse_JT", _
      "PositionID=" & Nz(Forms!frmTraining1.Form.cboPositionID, 0) AND " & _
      "CourseID = " & Nz(Forms!frmTraining1.Form.ListRole.Column(3), 0) _
    ) > 0

    If Me.NewRecord And AlreadyExists Then
        MsgBox "WARNING! This course has already been assigned with this role as mandatory. Select a different course.", vbOKOnly + vbInformation, "Duplicate record alert!"
        Me.Undo
    End If

End Sub

 

[ebs17]

I am trying to check if the combination of PositionID and CourseID already exists in the table

Duplicates don't just fall out of the sky.

I think it's always a good way to help a user enter the correct one right away and help them with it. It's friendlier than telling him later that your input was useless because it was wrong, start over.

For example, you can offer the user the selectable combinations in a listbox, the RowSource could be something like this (inconsistency query from the complete set minus the existing set):

SELECT
   A.CourseID,
   A.PositionID
FROM
   (
      SELECT
         C.CourseID,
         P.PositionID
      FROM
         tblCourse AS C,
         tblPosition AS P
   ) AS A
      LEFT JOIN tblData AS D
      ON A.CourseID = D.CourseID
         AND
      A.PositionID = D.PositionID
WHERE
   D.CourseID IS NULL

In addition, no auto values are then used in the target table by withdrawing the entries.

 

[nashaz]

Duplicates don't just fall out of the sky.

I think it's always a good way to help a user enter the correct one right away and help them with it. It's friendlier than telling him later that your input was useless because it was wrong, start over.

For example, you can offer the user the selectable combinations in a listbox, the RowSource could be something like this (inconsistency query from the complete set minus the existing set):

SELECT
   A.CourseID,
   A.PositionID
FROM
   (
      SELECT
         C.CourseID,
         P.PositionID
      FROM
         tblCourse AS C,
         tblPosition AS P
   ) AS A
      LEFT JOIN tblData AS D
      ON A.CourseID = D.CourseID
         AND
      A.PositionID = D.PositionID
WHERE
   D.CourseID IS NULL

In addition, no auto values are then used in the target table by withdrawing the entries.

I understand what you are suggesting but the SQL is beyond my understanding, unfortunately. Is there an alternate (and possibly, simpler) way in vba?

 

[nashaz]

Try it this way:

Private Sub cboCourse_BeforeUpdate(Cancel As Integer)
  
    Dim AlreadyExists As Boolean
        
    AlreadyExists = DCount( _
      "*", _
      "RoleXCourse_JT", _
      "PositionID=" & Nz(Forms!frmTraining1.Form.cboPositionID, 0) AND " & _
      "CourseID = " & Nz(Forms!frmTraining1.Form.ListRole.Column(3), 0) _
    ) > 0

    If Me.NewRecord And AlreadyExists Then
        MsgBox "WARNING! This course has already been assigned with this role as mandatory. Select a different course.", vbOKOnly + vbInformation, "Duplicate record alert!"
        Me.Undo
    End If

End Sub

Something funny is happening, this code either does not prevent duplicates at all, and at other times triggers the message box every time I try and enter a new course

 

[nashaz]

Just to update everyone, I figured out the issue. In the multiple DCount criteria, I was checking whether the combination of Position and Course existed in the source form. What needed to be checked is if the combination of PositionID in the source form and the CourseID from "target" form exists or not. This has solved the issue.

Appreciate everyone's help! You are all total legends