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:
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:
I get the error Invalid use of null.
Lastly, if I have the following:
I get the msgbox no matter what course I select.
As always, really appreciate everyone's help.
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:
Code:
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:
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:
Code:
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.