PMFJI,
A few observations:
1) For the subform "LowRiskBehaviours":
You have this code in the lost focus event of the combo box "BehaviourID""
Code:
Private Sub BehaviourID_LostFocus()
Dim BehID As Long
BehID = DLookup("BehaviourID", "QryCheckAddLowRiskBehaviours", "BehaviourID=" & Me!BehaviourID)
If BehID > 0 Then
MsgBox "This behaviour has already been selected."
Cancel = True
End If
End Sub
You cannot use the line
in the LostFocus event because it is NOT a cancel-able event.
Here is the control before update event
Code:
Private Sub BehaviourID_BeforeUpdate(Cancel As Integer)
End Sub
See the difference??
Even if you cancel the "BehaviourID" selection, a new record has been created and the "BehaviourID" is required..... thus the error...
2) I would expect the DCount() function to be used instead of DLookup(). (To me it makes more sense...)
In the query "QryCheckAddLowRiskBehaviours", the "BehaviourID" should not have a criteria.
A) Which record is to be used in the query for the "BehaviourID" if you have 3 behaviors selected?
B) The "BehaviourID" is provided in the DLookup (DCount) criteria argument.
Or instead of DLookup/DCount, you could open a recordset in the Sub ..... wouldn't need the saved query "QryCheckAddLowRiskBehaviours" unless it is used in other forms/reports
3) The query "QryCheckAddLowRiskBehaviours" has criteria for the two fields.
AssessmentID = [Forms]![AddLowRiskAssessment]![AssessmentID]
BehaviourID = [Forms]![AddLowRiskAssessment]![BehaviourID]
But the combo box "BehaviourID" is on a SUB FORM, not the main form.
The combo box "BehaviourID" cannot be found on the main form when the query executes, so the query always returns 0 records. Because the Function is DLookup, a record is not there, so NULL is returned. (You could wrap the DLookup() in the NZ function to convert the NULL to a zero.)
I'm not sure this is correct, but maybe the criteria for the "BehaviourID" field should be
[Forms]![AddLowRiskAssessment]!Present.Form![BehaviourID] ("Present" is the name of the sub form CONTROL)
(see http://access.mvps.org/access/forms/frm0031.htm)
--------------------------------------------------
BTW, these two lines should be at the top of EVERY module:
Code:
Option Compare Database
Option Explicit
"Option Explicit" is missing in 6 modules.......