Combo Box selection is updating more than one record at a time.

[jerepois]

I was not sure where to post this but since I am dealing with forms and their controls I figured why not post in the forms section.

Now to get to the point. I have several combo boxes on a form. We will only look at one because if we can solve one I can apply the solution to the rest.



combo box name: SCOPE_CATEGORY (Picture#4)
control source: ScopeCategoryName (Picture#4)
record source: SELECT tblScopeCategory.ScopeCategoryName, tblScopeCategory.ScopeCategoryID FROM tblScopeCategory ORDER BY tblScopeCategory.ScopeCategoryName; (Picture#4)

When I open in Form View and select a record (Picture#1), I see that my combo box is pulling the right record information.
Lets say I want to change the Scope from Sustaining Capital to Major Projects. I go to my Combo box and select Major Projects (Picture#2).
After the update the record changes.(Picture#3) But you now can also see that 3 records down the ScopeCategoryName has also change to Major Projects (Picture # 3).

It seems that when I make a selection the choice is applied to more than one record even though they all have different PKs.

I have attached several pictures that should provide enough information to help me start debugging.
Attachment 17369
Attachment 17368
Attachment 17367
Attachment 17372
Attachment 17371

[jerepois]

Here is a picture of the forms Query In design mode.

[ItsMe]

Sounds like a referential integrity rule that was set via options from the Relationships Window.

[jerepois]

tblWorkPackage and tblWorkList are a 1 to 1 relationship and everything else is a 1 to many relationship. Everything is enforced in the back end file.

[June7]

I suspect you have the combobox bound to the wrong field. Looks like it is bound to ScopeCategoryName field from tblScopeCategory instead of ScopeCategoryID field from tblWorkList.

Which means you are changing the value in tblScopeCategory record. Do you now see multiple records in that table with the same category name?

Change the combobox properties:
RowSource: SELECT ScopeCategoryID, ScopeCategoryName FROM tblScopCategory ORDER BY ScopeCategoryName;
ControlSource: ScopeCategoryID (from tblWorkList)
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"

[jerepois]

June7, You truly are an expert. I am not sure what I was thinking when I was creating my forms record source. I completely understand where I went wrong. I can't believe I spent 2 hours trying to figure this out and over looked that I was assigning the wrong sourceID.