Subform lost focus code doesnt prompt user

[shabbaranks]

Hi all

Im trying to get it so a field within a subform can't be left blank, and if is a message box will prompt them to enter data. I cant change the properties of the table so the field is mandatory as there is already some data in it. My code is

Private Sub Cost_Code_LostFocus()
If Forms![MainForm]![SubForm.Form![costcode].Value = "" Or IsNull Then
MsgBox "Please make sure you add a cost code"
End If
End Sub

But I can quite happily tab in and out of that column in the subform without adding anything or getting a message - any ideas?
Thanks

 

[bob fitz]

Use the forms Before Update event to validate data. "Cancel" the event if validation fails.

 

[shabbaranks]

Thanks for the reply, I tried with code

Private Sub Cost_Code_BeforeUpdate(Cancel As Integer)
If Forms![Mainform]![subform].Form![Cost Code].Value = "" Or IsNull Then
MsgBox "Please make sure you add a cost code"
Cancel = True
End If
End Sub

But I can still leave this field blank without any messages popping up. If I use the immediate window to validate that field its returning null, likewise if I change the value its displaying the correct return so it must be something else?

 

[vbaInet]

shabba, I've seen you write this IsNull code before and you were corrected as to the proper format. I will remind you again. If I recall correcly, John Big Booty said:

If Forms![Mainform]![subform]![Cost Code].Value = "" Or IsNull(Forms![Mainform]![subform]![Cost Code].Value) Then
    ... msgbox here ...
End If

You can also write it like this:

If Len(Forms![Mainform]![subform]![Cost Code].Value & vbNullString) = 0 Then
    ... msgbox here...
End If

Or this:

If Nz(Forms![Mainform]![subform]![Cost Code].Value, vbNullString) = vbNullString Then
    ... msgbox here...
End If

 

[shabbaranks]

Thanks I have corrected my code, but its still letting me bypass the before update validation. Is there anything else that would cause this to ignore the rule? As I said, I definitely am referencing the correct field as the Immediate window is telling me so if I question it.

 

[vbaInet]

What code did you use? Copy and paste it here.

 

[shabbaranks]

Its working now, but only if I select\enter a value in the field then delete it and then either tab or enter away from that field. And it only happens once so I can then carry on without having that field populated.

Code is

Private Sub Cost_Code_BeforeUpdate(Cancel As Integer)
If Nz(Forms![MainForm]![SubForm].Form![Cost Code].Value, vbNullString) = vbNullString Then
MsgBox "Please enter a cost code"
End If
End Sub

 

[vbaInet]

Like I asked previously, what code did you use?

 

[shabbaranks]

Must have edited before you responded as I forgot to put code in

 

[vbaInet]

I suspect your field is not returning Null or zero-length string. There must be a character there that's causing the validation to fail. What about the Len() variant?

 

[shabbaranks]

Im sure it is returning a null string as, if I leave the field blank and then query the immediate window it returns Null. And yes I tried the Len() variant and that produces the same result

 

[bob fitz]

Excuse my intervention, but shouldn't the code have cancel = true?

Private Sub Cost_Code_BeforeUpdate(Cancel As Integer)
If Nz(Forms![MainForm]![SubForm].Form![Cost Code].Value, vbNullString) = vbNullString Then
[B][COLOR=red]Cancel = True[/COLOR][/B]
MsgBox "Please enter a cost code"
End If
End Sub

 

[shabbaranks]

Excuse my intervention, but shouldn't the code have cancel = true?

Private Sub Cost_Code_BeforeUpdate(Cancel As Integer)
If Nz(Forms![MainForm]![SubForm].Form![Cost Code].Value, vbNullString) = vbNullString Then
[B][COLOR=red]Cancel = True[/COLOR][/B]
MsgBox "Please enter a cost code"
End If
End Sub

I dont think its the code thats the problem, as if I select a value and then delete it I get the message box. It just doesnt seem to trigger the code when going through the routine of data entry

 

[bob fitz]

I dont think its the code thats the problem, as if I select a value and then delete it I get the message box. It just doesnt seem to trigger the code when going through the routine of data entry

And you may be right, but if you don't try it we may never know. As far as I can see, there is nothing to stop the update of the record without it though.

 

[shabbaranks]

Fair point, but I have got it to work....

Private Sub Cost_Code_LostFocus()
If Nz(Forms![MainForm]![SubForm].Form![Cost Code].Value, "") = "" Then
MsgBox "Please enter a cost code"
End If
End Sub

Seems to work - thanks for all the effort with this one

 

[bob fitz]

My mistake. I thought you were using the code in the Forms Before Update event as I suggested in my first post. I still think it would be better there.

 

[vbaInet]

You need the Cancel = True in the control's Before Update event like bobfitz rightly pointed out. The Lost Focus event is not the right event for validation.

 

[bob fitz]

vbaInet
Actually, I suggested that it should go in the Forms Before Update event.

 

[vbaInet]

Yea, I noticed. I was talking about the Cancel = True part that you mentioned. Since the OP has chosen to use the contro's Lost Focus event I suspected he/she might want it during control level validation. But yes, form level validation is preferrable.

 

[shabbaranks]

Thanks guys, if I understand vbaInet correctly - they are right in thinking I need it during control level validation. If its based on the main form the user wouldnt be prompted correctly as they wouldnt have got to the subform data entry at that point.

Apologies for my mis-understanding, when you said Form I thought you realised I was working on a subform not a main form. Probably my lack of technical wording, and I think I'll stick with the control "lost focus".