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

[Heathey94]

In both instances, we're looking at the ID and creating an error message if it's already on there, but with DocumentPK (and LiberiID and LoginID), we're "undo"-ing it if the ID is already on there. Now I think about it, however, maybe Cancel would be better. Am I right in thinking Undo wipes everything on the screen, whereas Cancel undoes the last action?

[aytee111]

Yes, I told you to remove all those undo's, they are horrible. And no, cancel stops the action from happening (when Access gives you that option), doesn't undo anything.

You are getting lost in all of this back and forth. Go back to what you want to happen - check if it has been entered before (the called subroutine) and if it has then clear it out give them an error message.

[Heathey94]

Did you? I'm losing track. ok, Cancel is probably what I want to use then, but I still haven't had to define it anywhere else, so have nothing to reference.

[aytee111]

Sorry but it is a reserved Access parameter! You have to work around it by using other methods. Yes it would be nice to have, you will see complaints all over the internet about this, even setting focus doesn't work in this situation so it is hard to do. All you can do is clear it out (without using undo!) and the user will have to go back to it themselves.

[Heathey94]

What if you're on a record and try to change the ID to another ID, but that is already in the system? Then it needs to revert back to how it was (which is why undo was working)

[aytee111]

Except that undo wipes out everything the user has entered up til then. If you need to revert then use name.oldvalue.

[Heathey94]

"Invalid use of property"?

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

[aytee111]

equal sign

[Heathey94]

BehaviourID=BehaviourID.oldvalue?

[aytee111]

Perfect!

(too short!)

[Heathey94]

That worked! However... for BehaviourID, if you select a behaviour which has already been entered, it creates an endless loop with the message box continuously appearing... this is my code:
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."
 ChangeBehaviour(AssessmentID, BehaviourID, ResponseID) = True
 Else
 ChangeBehaviour(AssessmentID, BehaviourID, ResponseID) = False
 End If
End Function

On the Form:

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

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

[aytee111]

That is because you are doing the check both before and after. The before is not necessary, they haven't entered anything yet.

Also, in the module, remove the parameters in the code: ChangeBehaviour=True/False

[Heathey94]

OK, we're getting there! Now its fine if you change a row with a behaviour already selected to a different behaviour which already appears on the list, but doesn't like it if you choose a behaviour which has already been selected from the new row, so I think it needs something, like this, what do you think?

If ChangeBehaviour(Me.AssessmentID, Me.BehaviourID, Me.ResponseID) = True Then
    If IsNull(BehaviourID.oldvalue) Then 
        Me.Undo
        On Error Resume Next
        DoCmd.RunSQL "DELETE * FROM TblAssessmentDetails WHERE ResponseID=" & Me!ResponseID & ";"
        Me.Requery
   Else
        BehaviourID = BehaviourID.OldValue
   End If
End If

[aytee111]

1 - REMOVE undo from your damn vocabulary! What is it that you are so attached to it? If you want to clear out a field then set it to "" or null, but stop wiping out everything the user has entered! Haven't you experienced websites like this - one wrong entry and everything gets cleared out? And doesn't it irritate you?
2 - If this code is in the FORM's after update then you need to delete the record, but until that point the record is still in memory and hasn't been added to the table yet.

So, remove all lines, make the first part of the IF the same as the second - just clear out the entered value.

[Heathey94]

That was because I'd copied it from the DeleteRow button... so should that be changed to null too?

And the reason I'm going down this route in the first place is because it didn't like it being null, so surely setting it to null won't work?