Hi!

I have a form for a table with ProductNr in it (as primary key together with another field), with validation rules both in the table and in the form. These rules specify that Null and 0 are illegal values for ProductNr, which is shown in a dropdown list.

I have 0 as default value in the form when entering the form for a new record. In this way, I was hoping to force Access to not let the user exit the ProductNr field without an error message about illegal value. But not enough with this happening, there is actually created a new record with an illegal value for ProductNr and none of the other default values for other fields are set (all are Null).

This shouldn't happen, should it? Anyone got any idea how to avoid this problem?

Best regards,

Knut Hunstad
Trondheim, Norway

Knut,

Assuming that the name of the textbox on your form would be called something like txtProductNr, you can put code into OnExit() like this:

Private Sub txtProductNr_Exit(Cancel As Integer)
   If IsNull(Me.txtProductNr) Then
      MsgBox "Product Number must be specified"
      DoCmd CancelEvent
   End If

   If Me.txtProductNr = 0 Then
      MsgBox "Product Number cannot = 0"
      DoCmd.CancelEvent
   End If

End Sub

That will force the user to enter a value into that field that is not null and not = zero before exiting the field. The user can always hit the escape key to undo whatever was typed within the field.

Hope that helps.

Warren :D

It is best to verify how the table validation rules work in a table view of the data. Assuming an autonumber field exists, entering any value in any field will create a record notwithstanding that any validation rules are not yet met. In fact, you may tab or click in any invalid field and move out while the field contains an invalid value. Moving off the record should trigger an error. This should demonstrate when table validation rules are evaluated.

It appears you are using a bound form from your description and in that case, it would appear that Warren's suggestion should work to prevent a user from exiting a control without entering a non-zero value. Methods of the DoCmd object are the same as running the corresponding actions in a macro. Therefore, calling DoCmd to cancel an event is using code to call a macro to do something that can be done in code directly by setting the Cancel parameter. A rewrite of his suggestion:

Private Sub txtProductNr_Exit(Cancel As Integer)
   If NZ(Me.txtProductNr, 0) = 0 Then
      MsgBox "Please specify a valid Product Number"
      Cancel = True
   End If
End Sub

I've used the NZ function to convert nulls to zero to shorten the code but you could test both conditions (Null or 0) in a single line but the most efficient evaluation will probably result from the case where you check the most likely failure first and only check the second test if the first passes:

Private Sub txtProductNr_Exit(Cancel As Integer)
   If Me.txtProductNr <> 0 Then
       If IsNull(Me.txtProductNr) Then
           MsgBox "Product Number must be specified"
           Cancel = True
       End If
   Else
       Cancel = True
       "Product Number cannot = 0"
   End If
End Sub

If you set the default value of the control that displays the field value to zero, proof of failure will nearly always happen on the first test for zero without needing the second test for null. Although this is splitting microseconds, it is good practice to write the most efficient code possible for those times when such snippets run in loops repeated many millions of times.



Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]