Question

How Can i restrict the user to insert next record

VBA, Access 2000, Microsoft Access, Visual Basic for Applications

Hai all,



I have a requirement that , i have to display the data from a table in the subform in a datasheet view mode. I should validate the fileds in such a way that i can restrict the user to insert a next record when the one of the fields in the previous record is null. And also i should pop up a msg like ' Must enter value for this field' and the focus should be in that field.


how can i restrict the user to insert a new record until the user provides the value for one of the fields in the previous record



Thanx,

Hello,
you can accomplish to this in two ways:
1) If the db is on a SQL server you can set this field to be NoNull so no null values are allowed.
2) You can do it via code by checking if the length of the field is major than 0 or not (e.g. If Len(mytext.text) > 0 then...)

Checking the field length will not work. A field with a length of 0 and a NULL value are different values. There should be an ISNULL or IS_NULL function within VBA that you can use to see if the value is NULL or not.


Traditionally, when using the Jet database motor, it was possible in Access to specify if a field is "Required" or not, in addition to its ability to store nulls.
I don't know if this still holds for the database you are using, but if yes then simply specify NOT NULL in the table creation (as Alessandro said), and/or don't allow a column to be empty.
Notice that specifying NOT NULL for a column is a data modeling decision that should not be taken in isolation, as it may have side effects. The first one is that for this option to take effect there must be no existing null values in the column else the option should be rejected or the table disabled.
--
Juan Lanus