VBA attached to Before_Update not quite working (1 Viewer)

[alan2013]

I've a simple form, with two fields : one is an unbound combo (named unbClaimDecision) with a Value List of Yes, No, and To be decided. The other is a bound combo (named InvalidClaim_Reason). Attached to the Before_Update event of the form, I have the VBA code below. It seems to be working up to a point, but somehow when I enter a claim decision of No, I get a message about the missing reason......but when I click on Ok, the form closes anyway (without letting me enter the reason).

Can anyone help with this, please ?

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Check that required fields are populated
Dim strIncompleteRecord As String

'Claim decision combo
If (IsNull(Me.unbClaimDecision) Or Me.unbClaimDecision = "") Then
Me.unbClaimDecision.BackColor = vbRed
strIncompleteRecord = strIncompleteRecord & Space(3) & Chr(149) & Space(3) & "Claim valid ?"
Else
Me.unbClaimDecision.BackColor = vbWhite
End If

'Invalid claim reason
If Me.unbClaimDecision = "No" And (IsNull(Me.InvalidClaim_Reason) Or (Me.InvalidClaim_Reason = "")) Then
Me.InvalidClaim_Reason.BackColor = vbRed
strIncompleteRecord = strIncompleteRecord & Space(3) & Chr(149) & Space(3) & "Reason for deciding that claim is invalid"
Else
Me.InvalidClaim_Reason.BackColor = vbWhite
End If

If Len(strIncompleteRecord) > 0 Then
'Raise message and prevent record update until required fields are populated
strIncompleteRecord = "The record is incomplete. Please enter all of the required fields (marked in red)." & vbCrLf & vbCrLf & strIncompleteRecord
MsgBox strIncompleteRecord, vbInformation, "Record is incomplete"
Cancel = True
End If
End Sub

 

[Gasman]

Wlak through your code with F8 line by line, to see what you have, not what you think you have, as it all depends on the data, doesn't it?

And please use code tags in future. that keeps the indentation, which I hope you are using?

 

[bob fitz]

I think you are cancelling the update but not doing anything to stop the form from closing.

How is the form being closed?
Can you post a copy of the db?

 

[NauticalGent]

Give this article a read. It is written by one of AWF's finest, @GinaWhipp .

If you have any issues, post your questions here.

 

[alan2013]

I think you are cancelling the update but not doing anything to stop the form from closing.

How is the form being closed?
Can you post a copy of the db?

Thanks, Gasman, bob fitz, and NauticalGent. On my return to this following a few days away from it, I still can't see where the problem is in the Before_Update Event. I'd really like to get this method working. I've attached the database. If anyone has the time to take a look and try to pinpoint the problem, I'd be very grateful. Thanks in advance.
To navigate : on the main menu, click on 'Claims administration'. On the 'Claims administration' menu, click 'Claims validation'. On the 'Claims validation' screen, the claims that are at step 1D will have a right' arrow button under the 'Next step' heading. (During testing, you' ll have to go into tblClaims to re-set the claims to 1D, as they are currently - due to the problem I'm having - being moved on to 1E when I don't want them to be). The fields I'm having problems validating are at the foot of the 'Claim validation, for Claim X' form. The problem arises when I set the 'Is the claim valid ?' field to 'No' and try to Save & close (using the button at the bottom-right). Initially, I receive a message stating that a reason for deciding the claim is invalid has yet to be entered. But when I click on 'Ok' on the message, the form closes prematurely, before I have a chance to enter a reason.

 

[Gasman]

strIncompleteRecord = strIncompleteRecord & Space(3) & Chr(149) & Space(3) & "Reason for deciding that claim is invalid"

As already mentioned, your are not using Cancel = True in that block of code??
You have to use it in each place you want to cancel. Is that a clue?

If you walked through the code, you would see that in a heartbeat