Prevent changes to columns

0
votes

Running MS Access 2016. Say I have a table "Models" with the following columns:

  • Model
  • Brand
  • Type

No single field is unique, but together there should only be one combination. My problem is that when I'm running a query, I can get data from these columns and alter one of the fields to something that is not valid. I don't want to run validation on the table, as I tihnk there is a more simple solution to this.

For example, if the model is "Macbok Air" and the brand is "Apple", I shouldn't be able to change the brand to anything else in the query with the lookup combo box.

I hope this makes sense, help appriciated.

A quick google search showed this reply: ". If the controlsource of the combo is a field in the forms' Recordsource then selecting a value in that combo will change the value for that field for the current record. If you want to use the selected name tin the combo to filter the form or another form, you need to leave the combo Unbound (blank Controlsource)

You can still reference the value in the combo as long as the form is open."

However, I'm not exactly sure how to do this. And I'm not using forms yet. Can anyone elaborate?

1
ms-access
Sounds like you should not store brand at all, because it's fully dependent on the model. - Erik A
Yes, it is exactly that. I want to make sure the brand allows follows the model. But somewhere there has to be that connection that binds the two together, without allowing any one of them to change. Do you know how I can do this? I'm basically looking for a lookuptable with two values. - starbyone
I would think an MCSA requires a bit more than basic understanding. I could easily write a SQL statement to validate with IF EXISTS, but I'm just not at all familiar with Access and want to try avoiding complications. A lookup table in MS SQL can never be altered by just reading the values, which is exactly it does in Access. But yeah, I'm planning to take an online course on MS Access. - starbyone

1 Answers

1
votes

if the model is "Macbok Air" and the brand is "Apple", I shouldn't be able to change the brand to anything else

But that is a business rule only humans (you) know of. The database can have no idea, that an "Apple Surface 4" should not be a valid combo.

Your only method would be to maintain a manually validated table with valid combinations, and then let the user select an ID of these combos in one or more levels.