vrijdag 20 mei 2011

Null values in the composite index fields

Access does not allow you to put null values in fields that make a part of a composite index (typically foreign keys fields). In some situations that is an undesirable limitation. For instance, U may want to allow the user to keep it blank without the choice and built in some logic to handle these situations. In this case U need a work around.

For all such fields U can input a standard (default) value equal to 0 in the table designer. On your form a foreign key field will typically have a dropdown list which would not have 0 value. From the user perspective the field will be left out blank. However on the background, on insert of a new record with the "blank" value for the field, Access will put 0 in the table. (0 - well, any value U like)

SO if U want to use composite indexes to avoid double records (which is a good practice), but U would like to make the choice not obligatory, U can use this routine.

Geen opmerkingen: