fill subform field based on main form value

[noretoc]

This is probably another easy one, but I am having trouble with it. I have a main form (main) and a subform called items, each using a table of the same name as the source. The main form has a field called name. The subfom has a field called settlement. These are the fields linked to control which values from the table items are shown. The items subform is editable. I want to be able to enter a record in that subform, and have the settlement field on the table for that record automatically update to the name value of the current record on the main form. This way when i step out of that record on the main form and go back, it is going to pull any items that i entered into the subform previously.

[June7]

A main form and a subform should not be bound to the same table. This makes no sense.

[noretoc]

I was unclear on that. Actually let me rephrase the whole question, as I think I was trying to get the form to do something it should be doing alread. I'll attach a copy of the DB.

The form called main, has a tab control on it. On one tab is a sub form called NPC, the other tab is a subform called cityitem. Each of these subforms are linked to tables with the same name as the subform. Each of the tables is joined to the settlement table (Which the main form is based off of.) The field "name" in the main table (settlements) and on the main form is joind to the field "Settlement" on each of the subforms.

What I believe should happen, is when I enter a record in the subform cityitem, the "settlment" field should automatically reflect the same value as the "name" field in the current record of the main form. This links that record in the subform to the record in the main main form, so when I go back to this record, is shows any items that I entered. This is not happening on either of the subforms. instead the "Settlement" field displays Main (the name on the main form). It does not update to reflect the value in the "name" Field on the Main form.

When I fisr made the subform NPC, it was working as expected. If I entered a record on the NPC subform, the "settlement" field on that form automatically filled with the "name" on the main form. This connection has somehow broke though and I can't seem to get it back. I have checked the Master Field and Child Field on both subforms and they both are correct. I also checked the relationship on the underlying tables, and that seems to be fine. I am at a loss, but am probably missing simething simple.

[June7]

Problem is Name is a reserved word in VBA/Access. Should not use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. If do, must enclose in []. Set the MasterLink to [Name]. Wait, that seemed to work for a minute. I suggest best fix is to rename the field in Settlement. The name change should pass into the queries and MasterLink properties. Will have to manually change the ChildLink on the Totals subform.

The 3 Magic tables should be 1 table with another field for MagicClass (major, medium, minor).