Why does my form field modify the row source instead of the control?

[ilikecats]

I'm pretty much learning MS Access from scratch, so I'm probably just making some silly beginner mistake.

I have two tables, Neighbourhoods and Households. I have a one-to-many relationship between the two of them. (ID > Neighbourhood).

I made a form for Households and made a combobox with this SQL in the row source:

SELECT Neighbourhoods.ID, Neighbourhoods.Order, Neighbourhoods.Neighbourhood
FROM Neighbourhoods
ORDER BY Neighbourhoods.[Order];

(The 'order' is a likely-clumsy way of making sure my options appear in a certain order.)

The control source is set to the Neighborhood field on the Households table, and the Bound Column is 1.

When I select an option in the combobox, I expect it to modify just the current record in the Households table. Instead, the changes update the currently-selected field in the Neighbourhoods table. Depending on what Bound Column I chose, I either end up with the newly-selected neighbourhood name or a number.

I'm confused on why this is happening.

I hope I explained this well enough. Thanks in advance.

 

[vba_php]

well this is an interesting problem. from your description, it seems like there's nothing wrong with what you've got going currently so it doesn't seem like what is happening, *should* be happening. the only thing you're doing that might be confusing the program is the fact that your bound column on the cbox is *1* which is the [ID] field, and that field is the joined field from both of your tables. The other thing is you've got quite an anomaly going whereby your row source is pulling data from 1 table and your control source is linked to the other table, but yet it sounds like the form itself is bound to the "Households" table. If I were a machine and you did that to me, I would feel like the cables that run from the big banks' buildings 3 blocks away from wall street to the actual stock trading floor. when you open your form and request data from different places, that would be just like the stock trading algorithms getting confused with each other and creating an endless loop of communication inside the cables they're traveling through.

what you really should do instead, if your households data is stored in the households table, then there's no need for a combo box. text box is fine. however, if your households data is in the neighborhoods table (which it really shouldn't be, beside ID), then that's another story because technically you have to write a JOIN sql statement to get the households data from the neighborhoods table. It doesn't really sound like you're set up correctly here. I would be interested in seeing your actual db to see how you got into this mess. Maybe you can also issue some more explaining so it doesn't sound so confusing as it is? (it's possible though that I've confused myself with this post and there's nothing wrong with your file).

 

[Gasman]

Is it updaring both.?
Do you have referential integrity set between them with cascading set.?

 

[jdraw]

ilikecats,

First, welcome to the forum.

I'm pretty much learning MS Access from scratch

We are all learning, so welcome to the "club".

I recommend you step back and, in plain English, tell us what it is about neighbourhoods and households as they relate to your project. Are you producing some statistics, reports..? Are the some basic "facts" we should know about your intended use of this database?

Good luck with your project.

 

[theDBguy]

When I select an option in the combobox, I expect it to modify just the current record in the Households table. Instead, the changes update the currently-selected field in the Neighbourhoods table. Depending on what Bound Column I chose, I either end up with the newly-selected neighbourhood name or a number.

Hi. Welcome to AWF!


Pardon me for jumping in, but would you mind showing us the Record Source for your form? If you're using a query that joins the two tables, I would suggest you don't do that and simply bind the form to the Households table alone.

 

[vba_php]

I would suggest you don't do that and simply bind the form to the Households table alone.

that's exactly what I think the solution is...

 

[MajP]

The control source is set to the Neighborhood field on the Households table, and the Bound Column is 1

Without answering the other questions, my first guess is it is mistakenly bound to the Neighborhood field in the Neighborhood table and not the household table.

I usually not not name my PK and FK fields this same name in both tables for this reason. I will usually have something like Neighborhood_ID in the neighborhood table and the foreign key in the child table would be Neighborhood_FK.

Although Gasman mentioned Cascade Updates, that should not be an issue. Changing a child does not change the parent only the other way around. If you change the Parent PK however then all the children with the FK will change.

When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.

 

[ilikecats]

Hi. Welcome to AWF!


Pardon me for jumping in, but would you mind showing us the Record Source for your form? If you're using a query that joins the two tables, I would suggest you don't do that and simply bind the form to the Households table alone.

That solved it! I thought the form was pulling directly from the table, but instead it was pulling from one of my queries (which, in some brilliant stroke of genius, I had hidden so I completely forgot about it). :banghead: I changed the source of Neighbourhood in the query and it solved the issue.

Thanks for all the help everyone.

 

[theDBguy]

That solved it! I thought the form was pulling directly from the table, but instead it was pulling from one of my queries (which, in some brilliant stroke of genius, I had hidden so I completely forgot about it). :banghead: I changed the source of Neighbourhood in the query and it solved the issue.

Thanks for all the help everyone.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project and Happy New Year!