Changes in form lead to wrong Changes in table

[Crewmate]

Hi there,

first of all, I apologise if any of my terminology is wrong, because English is not my first language and thus my Access is not in English as well.

I have a school project and I am pretty much panicking because I am way in over my head.

You can find my file here: https://www.dropbox.com/scl/fi/pwjdo...uxa2orini&dl=0
Sorry for the dropbox link, the file was too big for upload directly in the forum. There are quite a few tables, queries and forms but I will describe exactly where the problem is. None of the data in this real, so don't worry about privacy.

First of all: In the table with the customer data there are a few data points which are saved as a number although they represent other terms. In this case these are the three fields that are comboboxes: "Anrede" (salutation), "Bundesland" (federal state) and "Bonität" (credit score). But as an example we can just talk about the salutation. In the table "Kunden" (customers) the salutation is represented as a number from 1 to 3. These are the IDs from the table "Anrede" (salutation) where the salutaion is stores as a word if that makes sense. So 1 is "Frau" (Mrs.), 2 is "Herr" (Mr.). This is the way we were supposed to do it. I have no Idea if it is done this way in the real world.

In my database I have a problem when it comes to the way this information is displayed in a form and the affects changes have. If you check the file, your basis should be the form "formKundenübersicht" which just displays all of the customers. If you click on any of the hyperlinked numbers in the first column you will get into a form where you are able to edit all the customer information. I wanted this to be a combobox so only the given options can be chosen. The problem is, if I change the salutation here from one to the other, it does not change the salutation of this one customer. It changes it in the table "Anrede" (salutations). So if it says "Herr" in the form and I change it to "Frau" it changes the entry in the table "Anrede" itself.

I tried for hours to fix this but I don't know where to go from here. Can you help me?

If anything is unclear or if you have any questions, please don't hesitate to ask.

Thanks a lot in advance.

[davegri]

Rather confusing schema, with table names same as data names within, foreign keys same name as data field in lookup table, use of special characters in data names as well as being in German. Any how, addressing your described problem, the lookup comboboxes are not being set up properly. Here's how.
Code will update the foreign key named Anrede in table Kunden instead of field Anrede in table Anrede. See how confusing the duplicate data names and object names can be? Anrede is data type short text in table Anrede, but data type long integer in table Kunden. Same name but certainly not same data content.
Also much info about this via google and youtube.

[Crewmate]

Hi davegri,
thank you so much for taking a look at my mess. In the beginning I didn't know about naming schemes and regret not having learned to usem them properly before starting this project. I will try renaming things, but I am afraid to break something.

Anyway, thank you for your adivce. Unfortunately your solution does not seem to solve the problem. When I am for example editing the first customer and change the salutation from "Herr" to "Frau" and go back to the customer list "fromKundenübersicht" all of the salutations that used to be "Herr" are now "Frau" for all customers. When changing the option in the Combo Box it does not change the salutation in the respective customer, but it edits the the table "Anrede" (which I already changed to tabAnrede thanks to your advice).

Do you know what I mean? Would you be able to take a look at that?

[Crewmate]

I did what I should have done in the first place and created a small mock-file with only the necessary parts. Sorry that my sleep-deprived brain didn't think of this before:

https://www.dropbox.com/scl/fi/rgv6p...ouy9fmhcl&dl=0

I hope this helps a little in making it easier to understand the problem.

In this test file I deliberately did not use a combo box because I found out that this happens with or without it. So the Combobox wasn't the problem.

If you go into the main form "fromKundenübersicht", click on any of the hyperlinked IDs on the left and then change the salutation ("Anrede", second field from the top) the change will not affect the data in "tabKunden" but in "tabAnrede".

[Welshgasman]

If you now have a small file you can upload it here?

[mike60smart]

Hi Wgm

Here is the file that was in Dropbox.

[moke123]

You may want to re-think formatting your PK in the table.


If need be, do the formatting in the form for display.
Your PK should be meaningless other than to identify a unique record.

[CJ_London]

If you go into the main form "fromKundenübersicht", click on any of the hyperlinked IDs on the left and then change the salutation ("Anrede", second field from the top) the change will not affect the data in "tabKunden" but in "tabAnrede".

Think you mean 'AnredeText, second field from the bottom.
It doesn't for me. tabKunden has the new value for Anrede_FK

What I find confusing is whichever record I select, it always opens to the same one (record 9). Are you sure you are looking in the right place?

Also, you do have to save the record by moving to another record or closing the form before the table will be updated.

[davegri]

Design problem. If you try and add a record, the form will force you to input a new salutation which will ADD a record to tabAnrede:



To fix the Anrede assignment, add this code:



Note that the Test database does not load the selected ID into the called form, it always loads the first KD-ID.

[madpiet]

(Having a separate primary key for a tiny table seems like a really odd thing to do... If it's only a handful of values, why not just store the real value?) Part of me thinks this is going normalization crazy. I mean, I get it if you have a data warehouse fact table with a billion rows, but with a tiny table? what are you really saving?

[davegri]

(Having a separate primary key for a tiny table seems like a really odd thing to do... If it's only a handful of values, why not just store the real value?) Part of me thinks this is going normalization crazy. I mean, I get it if you have a data warehouse fact table with a billion rows, but with a tiny table? what are you really saving?

The strongest argument for having small lookup tables is this:
With an ongoing database, what if you want to change an existing salutation from, say Mr. to Mister? If you just start using Mister, what about the dozens or hundreds or thousands of existing Mr. ?
If you have it in a lookup table, you change it once in the table and it's automatically propagated to all existing instances throughout the database via the key relationships.