CODEWITHSUNDEEP
sqlsql-servervalidationms-access
AngelicCore
AngelicCore

Reputation: 1453

Validate whole row/record in access

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.

enter image description here

Upvotes: 0

Views: 369

Answers (1)

Dale M
Dale M

Reputation: 2473

You need to appreciate the difference between Access and SQL Server.

  • Access is a single user integrated database/user interface - simple to use simple to program often by the same person.
  • SQL Server is a high powered multi-user database ONLY that relies on something else to provide the user interface: a Windows Forms Project, a Website or (in your case) Access. It expects that its programmers and its users will be different people with vastly different skill sets and that the former need to protect it from the latter.

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

Related Questions