My combobox is not updating the table correctly

[AccessStarter]

I have two comboboxes linked together on a data entry form. The second combobox data is populated depending on the the user's selection in the first combobox. For example:
**1st Combobox: Area_Combo
**2nd Combobox: Category_Combo

If user selects one of the option from the Area Combo box, the Category Combo box will show only selections pertains to that Area. User then selects one of the values from the Category Combobox. The problem is that when user selects a value from the Categorybox and click away, all of my existing records from my table is updated with that selection.

I only want to update the new record with the selection chosen by user at the time of the data entry NOT all existing records in the table.

Your assistance is appreciated!

 

[marianne]

can you attach your db here. save it as version 2000

 

[AccessStarter]

I apologize. I forgot to mention that I am using Access 2007. I just try to save a copy of my db in version 2000 for you but it wouldn't let me
because there are some features in the db that require my current file format.

Let me go over what I coded for each combobox to see if it helps.
------------------------------------------------------------------
Areas Table:
Area_ID (num)
Area (txt)

Categories Table:
Category_ID (num)
Category (txt)
Area_ID (num)
-------------------------------------------------------------------
Area_Combo is the independent combobox
Category_Combo is the dependent combobox

Area_Combo is displaying Area from Areas Table. Its After Update event is coded as follow:
Private Sub Area_Combo_AfterUpdate()
Me.Category_Combo.Requery
End Sub

Category_Combo Row Source is coded as follow:
SELECT Categories.Category FROM Categories WHERE (((Categories.Area_ID)=[Forms]![MyEntryForm]![Area_Combo]));

Let me know if you need anything else. Thank you so much for looking into this problem with me!

 

[marianne]

what do you mean "The problem is that when user selects a value from the Categorybox and click away, all of my existing records from my table is updated with that selection."

all of you existing records from your category table under category field is update with that selection?

 

[AccessStarter]

That is right. All of my previous records in the Categories table | Category field/column is updated with the new selection for new entry. I'm not sure why and how to fix this...

It doesn't matter if I add a new entry or go to an existing entry and change the value of the Category_Combo box. It will update the entire Category column in Categories table with that selection...so all existing date/records are affected.

 

[marianne]

you did not make any update query did you?

 

[AccessStarter]

It looks something like this....

Before entering new record or selecting Category or change category on an existing record:
[CATEGORY TABLE]
Category_ID---Category---Area_ID
1-------------Catg XZY---11
2-------------Catg ABC---22
3-------------Catg YYY---33
4-------------Catg KKK---44

Before entering new record or selecting Category or change category on an existing record:
[CATEGORY TABLE]
Category_ID---Category---Area_ID
1-------------Catg NNN---11
2-------------Catg NNN---22
3-------------Catg NNN---33
4-------------Catg NNN---44
5-------------Catg NNN---55

User selects NNN Category from the Category_Combo

 

[AccessStarter]

No, no specific update query. The only thing that consider as an "update" in this case is the Requery code I put in the Area_Combo After Update event so that the Category_Combo box can be refreshed with new Category basing on the user's selection of Area_Combo. (this is in case user changes his/her selection of the Area)

 

[AccessStarter]

Does it matter if I use Event Procedure or a Macro to do Requery? I tried both... and have been doing research/googling for 3days but no luck.

 

[marianne]

did you make a cascading update in the table relationships?

 

[AccessStarter]

All I have in the db relationship for these two table is linking of the Area_ID from two tables together. I tried a couple different join (inner join, left, right, outter)

Is this what messing up my table?

 

[marianne]

when you join the two tables on Area_ID, just try to link the two together with out any referential integrity rules

 

[AccessStarter]

Thank you, Marianne! I will try that and let you know how it goes tomorrow.

 

[AccessStarter]

I tried linking it without any referential integrity and still didn't work. I played with this again today and it seemed like the problem was coming from "Requery."

Here was what I tested:
If I took out the Requery code in the first combo box's After Update then I was able to select my option in the second combo box and my table stored that choice correct for that single record. The issue was that now my second combox was not populating the correct choices if user changes the selection in the first box. I can only correct this problem by putting the requery code back in the first box after update event but then i ran into the same issue again.

There seems to be a conflict here... I'm very confuse. Is there any easier way to get the two boxes link properly and user can store their selection in the table? I don't have a reference to how it's done, as long as I can make this work.

 

[AccessStarter]

I went a head and created a similar dummy db in Access 2000 and uploaded it here. Can you tell me how I can store user selection (Area & Category) to AreaCategory table?