How do I save selected values on a form to another table?

[AccessStarter]

I am using access 2007. I have two combo boxes displaying values from two different tables. One box is depending on the other and they are on the same form. How do I store the values that user selected on this form to a third table? (see attached file for an example)
Table1: Areas
Table2: Categories
Table3 AreaCategory

Any suggestion is greatly appreciated.

(I'm new to this forum. I'm not sure if I'm posting this question under the right discussion (Forms.) I already tried to post my question in General discussion but very desperate for a resolution. Please tell me if I should post this question under Tables dicussion or where else...) Thanks!

 

[boblarson]

The sample already stores the values exactly as it should. I don't understand what you are not getting.

 

[boblarson]

If you are opening this in Access 2007, make sure to go into Design View of the form and change the default view from Split Form to Single Form, Continuous View, or Datasheet instead.

 

[AccessStarter]

Sorry for not making myself clear. If you notice from the split form. Every time I select the value, my whole CategoryName column is updated with that value.

You are correct. The combo boxes work fine, my only problem is that every time I select the category in the second box, it updates my table with that value for all existing record. This is a data entry form. I only want to save the selection the new entry/record. It should not mess wiht my existing records. What did I do wrong?

 

[AccessStarter]

The form is intentionally created as a split form because I want to give users the opportunity to sort existing records from the entry screen.

Is there a reason why I should change the default form view to a single form etc?

 

[AccessStarter]

If you open the form from my sample file and start enter 2 or 3 entries as you keep an eye on the split form at the bottom. You will see my problem.... the value of CategoryName column just wouldn't stored in the table

 

[boblarson]

If you open the form from my sample file and start enter 2 or 3 entries as you keep an eye on the split form at the bottom. You will see my problem.... the value of CategoryName column just wouldn't stored in the table

Actually, it does. Get rid of the split form view.

 

[AccessStarter]

This is sooo weird. Now that you mention it does matter. I went and checked the AreaCategory table. It did update my table correctly!

Question 1. Why is it doing this??? Why does the AreaCategory split form not reflecting the AreaCategory table which store the selected value correctly????? Is this Access limitation?

Question 2. How come my AreaCategory table is storing numeric values instead of the actual text? I have the field format set to text format in this table.

Question 3. Is there anyway I can make this work with the split form.... (from your responses, i'm guess NO but just wanted to ask anywayz...)

By the way, I appreciate your time looking into this.