0

My problem is that currently, I have an order / delivery system on microsoft access. At the moment, a quantity for a particular delivery line can be larger than the balance on the order.

For my database, an order can have many product lines, and a balance is calculated (quantity ordered - quantity delivered) for each product line. At the moment, when I go to create a delivery, that has many delivery lines (delivery lines is a table used to break down the many-to-many relationship of product lines and deliveries), I can input a quantity that exceeds the calculated balance of a product line.

How would I go about creating a validation rule of a value where it cannot exceed a calculated value (calculated from values from another table) in a data entry form.

shaunD1972
  • 3
  • 1
  • Validation rules can only depend on the current table. An alternative is to use **Data Macros**. They are also limited in what calculations they can do, but they can at least query another table. However, a quick review shows that standard built-in behavior only allows basic lookup, not aggregate queries necessary for balance calculations. I am aware of some clever workarounds to call custom functions, but be aware that they fail if the database is manipulated outside of the Access application (i.e. ODBC, DAO code, etc.) and it is not supported officially. – C Perkins Oct 22 '18 at 04:55
  • 1
    Best advice is to use form code. BTW, a conceptual problem with enforcing balance calculations in the database comes with edits for historical reasons, or perhaps audits for loss and theft. What happens if an order is placed and then after the fact it is found the database did not match reality? If the database balances and quantities are then updated, the order could then become strictly invalid according to database validations rules. One must then figure out proper ways to handle such validation exceptions... and it disallows edits without fundamentally changing the order, etc. – C Perkins Oct 22 '18 at 04:59
  • It seems like a good idea, but can cause nightmares with maintenance. Instead, it is better to assume possible balance exceptions and to develop a systematic way to handle those exceptions. In the mean time, proper Access form logic (and API logic using good patterns) can prevent invalid data for immediate data entry. – C Perkins Oct 22 '18 at 05:01
  • What research did you do already? I am getting many hits on StackOverflow for validation rule and data macro questions that might be helpful. See https://stackoverflow.com/questions/50391371/how-to-set-a-validation-rule-on-a-field-in-access-using-sql-query. Back to my earlier disclaimer... I'm not certain that all of the answers will work universally... like the CHECK constraint added via ADO code. – C Perkins Oct 22 '18 at 05:10
  • A check constraint can certainly work, and is generally advisable over data macros imo (data macros are less portable (often can't be recreated), form code allows people to ignore validation rules if they're allowed to directly work with tables.) If you're more specific, I might be able to write up a more detailed answer. – Erik A Oct 22 '18 at 07:27
  • @ErikvonAsmuth I learned something new and tried CHECK constraints. See [this answer](https://stackoverflow.com/questions/11965502/constant-errors-with-check-constraint-in-ms-access-using-sql) for simplified creation without requiring ADO. I see the benefits, but these fulfill a role somewhere between Validation Rules and Data Macros. Check constraints are essentially true or false checks, whereas specific Data Macros provide a wider variety of actions with CRUD operations. Not sure why Data Macros are "less portable" since they can be copied and updated altogether as XML blocks. – C Perkins Oct 22 '18 at 17:47
  • 1
    @CPerkins `CurrentProject.Connection` is an ADODB connection object. CHECK constraints require either ADO or changing the database settings to use SQL server compatible syntax. I also suggested using `CurrentDb.Connection.Execute`, just didn't share the VBA. Data macros are less portable since they can't be implemented in most RDBMSes and need to be completely rewritten to triggers, while a CHECK constraint is somewhat standardised. My main consideration for portability often is: can I upscale this to use SQL server/Postgres/Oracle as a backend. – Erik A Oct 22 '18 at 18:43
  • @ErikvonAsmuth What a mess Access is. I was under the impression that Access essentially abandoned ADO a few versions back and went back to DAO as its "internal" library objects. I only posted comments in an attempt to share useful info, but was also unsure of exactly what the OP needs. If the OP ever responds, I'll let you answer. – C Perkins Oct 22 '18 at 20:51
  • Thank you for all the responses. With all these options i'm finding it difficult to narrow down which one would be most suitable, however check constraints seem like the popular option. Sorry for not being clearer in my question; i'll try to give a clearer description: – shaunD1972 Oct 23 '18 at 11:08
  • I currently have a "create delivery" form that has a combo box with all the current customers. Once the user has selected a customer, all of their product lines are shown in a subform. There is then a second subform (delivery lines), that allows the user to pick which product lines they want to add to the delivery by entering a product line ID and a quantity (of that product that they want to deliver). Multiple delivery lines can be on one delivery. – shaunD1972 Oct 23 '18 at 11:17
  • The issue I have is at the moment, a user can simply enter a larger quantity on the delivery lines than the balance that is left on the product line. The validation is all for the user as the balance is never stored and a quantity entered that is larger than the balance does not cause any issues with other parts of the system. My original ideas were to just have some kind of validation or restriction on the form. – shaunD1972 Oct 23 '18 at 11:17
  • Screenshot link: https://gyazo.com/ac06658e906545c2771845bb3ae8feff – shaunD1972 Oct 23 '18 at 11:23

0 Answers0