Reputation: 1453
We are migrating Access forms/Access Database to Access forms/SQL database.
I have a problem mimicking the validation happening in access.
When the user is entering a new record and skips a required field and tries to save in access it shows a messagebox saying that some field is required (validation rule)
The problem for me though is that the same scenario raises a SQL exception for me for about the missing field..although i have a validation rule in place. The Validation rule doesn't work unless you move to that field first THEN try to escape from it or save. But it doesn't work if you for example 1)Start a new record 2)type something in the first field 3)press down arrow to save record
How can i do validation on whole row (pref without having to resort to writing code)
Example is shown in next screenshot, the last column displayed should have a value but the validation, which is there, is not enforced unless you specifically go to that field then type something wrong. As you can see the last 2 records have been saved without validation.
Upvotes: 0
Views: 369
Reputation: 2473
You need to appreciate the difference between Access and SQL Server.
What you have gone from is a 1-tier solution to a 3-tier solution, the tiers being User Interface Layer (Access) - Business Logic Layer (Access/SQL Server?) - Data Layer (SQL Server). Data validation needs to happen at each of those layers for each piece of information.
I suggest that you read up on multi-tier applications.
With respect to your particular problem - if the choice is only between "cuttings" and "seed" then this should be controlled by a foreign key relationship to a new table rather than using a text field. Your UI can use this to generate a combo-box selection which prevents invalid data getting in and the FK relationship would reject it even if it happened. Changes to business logic (e.g. allowing "sapling") could be accommodated by simply adding a record to the FK table.
Upvotes: 1