Access 2010 VBA code to set a form field to null

[Wingfoot]

Hi

I'm trying to write some simple code that deletes the contents of a field and opens a message box if the field's number is greater than that in another field (see below):

Private Sub Confirmed_Cartons_AfterUpdate()
If [Confirmed Units] > 0 And [Confirmed cartons] > [Confirmed Units] Then [Confirmed cartons] = "" & MsgBox("Cartons and sets cannot be greater than units!")
End Sub

What actually happens is where the [Confirmed cartons] field is greater than the [Confirmed Units] field, it sets the field's value as 1 rather than deleting the number altogether which is what I want it to do.

Please could somebody enlighten me.

Thanks

 

[Joe4]

You cannot do two separate actions on a single line (and you certainly don't want to use "&" which is concatenate, which is a text function).

Try:

[COLOR=#333333]Private Sub Confirmed_Cartons_AfterUpdate()

[/COLOR]    If (Me.[Confirmed Units] > 0) And (Me.[Confirmed cartons] > Me.[Confirmed Units]) Then 
        Me.[Confirmed cartons]=Null
        MsgBox "Cartons and sets cannot be greater than units!"
    End If

End Sub

 

[Wingfoot]

That works - Thanks!