Access Structure Design (form process), open message box with multiple options on error

[Heathey94]

Oh, OK. It didn't like it, so I've done something wrong - I tried editing a behaviour, and it said "Compile Error: Argument not optional", highlighting the "ChangeBehaviour" in the Form_BeforeUpdate event

[aytee111]

Sorry, missed that. The subroutine has three parameters. See #174

[Heathey94]

Yes, sorry - I missed that too.

The Form Update is now fine, but when the behaviour loses focus, it says "Compile error: Variable not defined", highlighting the Cancel (from Cancel=True on the lost focus event)

[aytee111]

Is there a cancel on the lost focus event? It will be a parameter.

[Heathey94]

I'm not sure what you mean, this is my code - on the form:

Private Sub BehaviourID_LostFocus()
If ChangeBehaviour(Me.AssessmentID, Me.BehaviourID, Me.ResponseID) = True Then
Cancel = True
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ChangeBehaviour(Me.AssessmentID, Me.BehaviourID, Me.ResponseID) = True Then
Cancel = True
End If
End Sub

On the Module:

Public Function ChangeBehaviour(AssessmentID, BehaviourID, ResponseID)
Dim BehID As Long
 On Error Resume Next
 BehID = DLookup("BehaviourID", "TblAssessmentDetails", "BehaviourID=" & BehaviourID & " AND AssessmentID=" & AssessmentID & " AND ResponseID<>" & ResponseID)
 If BehID > 0 Then
 MsgBox "This behaviour has already been selected on this assessment."
 End If
End Function

[aytee111]

We're back to "what is a parameter"! It is a variable that is passed to a subroutine. Is the Cancel parameter passed to the LostFocus subroutine?

Hint: a parameter is in brackets after the subroutine name.

[Heathey94]

Oh, I didn't realise Cancel would be classed as a parameter, as I think of parameters as variables, not functions. The fact it was working on the BeforeUpdate threw me a bit too.

Speaking of variables, this code:

Public Function ChangeBehaviour(AssessmentID, BehaviourID, ResponseID, Cancel)
Dim BehID As Long
 On Error Resume Next
 BehID = DLookup("BehaviourID", "TblAssessmentDetails", "BehaviourID=" & BehaviourID & " AND AssessmentID=" & AssessmentID & " AND ResponseID<>" & ResponseID)
 If BehID > 0 Then
 MsgBox "This behaviour has already been selected on this assessment."
 End If
End Function

and

Private Sub BehaviourID_LostFocus()
If ChangeBehaviour(Me.AssessmentID, Me.BehaviourID, Me.ResponseID, Cancel) = True Then
Cancel = True
End If
End Sub

makes the "Compile error: Variable not defined" error box appear

[aytee111]

Well, where are you defining it? You have in your options that all variables must be defined.

[Heathey94]

Right.... I'm very confused. The 'cancel' was cancelling the users attempt to select a behaviour that has already been included on the assessment, by not losing the focus... resulting in an endless cycle where the user cannot unselect the combo box until they change the behaviour (or delete the row)

[aytee111]

Not sure what your question is. You have the same thing going on with your DocumentPK where you check to see if it has already been entered. If that is working correctly then just copy the method.

[aytee111]

BTW, AfterUpdate is usually used instead of LostFocus.

[Heathey94]

The Document PK is working correctly, but that's just on the code for the form, not the module - that's what is causing the confusion.

Is there any difference between AfterUpdate and LostFocus?

[aytee111]

There is no difference here to the DocumentPK, except that instead of the code being used right there it is first passed to a module and then comes back. Another word for subroutine is function and you use them all the time. DLookUp is a function and you pass it parameters in brackets. Whether you create the function or it is a standard Access function makes no difference, it is still a function where you are passing parameters with values and receiving a value back. You take your clothes to the dry cleaners or you wash them yourself - the start is the same, the result is the same, it is only the middle bit that varies.

AfterUpdate is different to LostFocus, it is better in that it is more fitted to this scenario. Don't ask me all the in's and out's, tho, you can google it. One of those things that I knew once upon a time but now just accept it.

[Heathey94]

Yes, but because it is being passed to the module, I am having to define the cancel function, which I did not have to in DocumentPK - and so I can't reference the DocumentPK. I didn't even use cancel in the DocumentPK code.

[aytee111]

So why are you using it here? Isn't this the same method as the DocPK? What is different?