From your relationships, it looks like your popup menu refers to Status_Member::_kfStatus. That's a field in the Status_Member table so selecting a value in it will create a new record in Status_Member if a related record in Status_Member does not already exist. That would seem what should take place here given that design.
Thanks, that makes sense. But this is where I get confused. I want to have a picking list for membership status and have chosen to do it by setting up a related table. This (seems to have) worked for me before without this problem.
I suppose I could have a custom value list instead but then I would not be able to produce reports that lists the different categories of membership. I have tried doing that and I cannot get a list for each status.
You don't need a related table just to get a value list. Nor do you need a related table in order to set up a value list with the "use values from a field" option. The table that serves as the source of values does not need to be related to your layout's table at all in some cases.
From what you describe, you need to add a field for the status to your member table and format it with the value list instead of entering data into a field from a related record.
The combination of a status table, a status_member table and member table is typical of a many to many relationship where you want to link one member record to many status records and one status record to many member tables.
Thanks, I understand. You prompted me to do a bit of learning by trial and error. I compared this database with other tables where I have had many-to-many relationships with success. I see the problem. In previous databases, I have related records to each other via portals. This does not create a new record. With the database we are discussing, I used a simple field and that does lead to creation of new records.
What prompted me to try this out was that I want to have a separate table with status because (1) I want to create lists of members according to status and I cannot get that to happen with a custom value list (2) a member may change status and I want to create a status history eventually. Can you point me in the right direction?
(1) I want to create lists of members according to status and I cannot get that to happen with a custom value list
Such can be done from a custom value list. you can get such lists using a custom value list or a table based list and this does not require a many to many relationship to do.
(2) a member may change status and I want to create a status history eventually. Can you point me in the right direction?
This also does not require a many to many relationship, just a one to many relationship from members to status history. You'll need either a portal or a script however if you want to record the status of each member at different points of time in their "history". That added table would only be needed to record current and previous status values. A Portal sorted to list the most recent status first would be the simplest. A script that takes the members current status and logs it in the status history table by creating a new record might also be used.
Thanks, I tried (1) & (2) and they worked.