Combo Box automatically altering previous records (1 Viewer)

[Megacitizen]

My form has two comboboxes, the first one naming the Base where a particular school or team is from and the 2nd one the nation where that base is located. With the latter, every time I try entering a nation different from any entered into previous records, the box is automatically altering the relevant combo in all previous records to match the one newly entered.

EG, the first team comes from USA, which I enter without any problems. The 2nd team comes from Australia. When I enter Australia into Team 2's Nation combo, Team 1's nation combo automatically changes to Australia. I've experimented with other nations and the same problem is occurring.

Any code to prevent this from happening?

 

[mahenkj2]

Have you set the control source for that combo? If not, then it will appear same on all records when you navigate through records.

 

[Megacitizen]

Have you set the control source for that combo? If not, then it will appear same on all records when you navigate through records.

Just checked - it's currently unbound so I would need to select the Nations table as the control source for this box, correct ?? When I scroll down the various options in Control Source, Nations isn't there, but Nations_label is, referring to the label for the combo.

My NationID is FK in the Bases table and also the Students table. This is to allow for the scenario where a student is from a different nation than that of the base s/he is at.

 

[Megacitizen]

Thinking that the combo might be corrupted, I tried deleting it and setting up a new one. However, I still get an unbound box without the option to bind it to my Nations table.


HEEEEEEEEELLLLLLLLLLPPPPPPPPPP!!!!!!!!!!!!

 

[binbin]

Is the record source of FORM where the combo boxes are located only includes the Bases table?

What I usually do is make a query that includes the main table and the one with the foreign key and add the fields that you require on the form then make that query the record source for the form with the combo boxes. I hope I understood your problem.

 

[mahenkj2]

Yes, Just add Nartion table in record source of form. If everything now goes fine as you wish then it is OK else consider reviewing the recordsource of form.

Frankly, main table seems to be TeamMember. bases, nations and school ministry seems to be raw source for the forms data. We do not know the exact purpose and your objective, but form should have based on team member table as record source (or a query based on that). In the relevant combos, you should use bases, nations and other table as raw source to fetch the data. Then everything should go smoothly. But this all depends on how and what you want to achieve.

Also, use PK suffix for primary key fields and FK for foreign keys. Once one become expert, then he can recognize what he is looking at but beginners like us gets confused and makes mistakes.

I think I am able to explain my views.

 

[Megacitizen]

Yes, Just add Nation table in record source of form. If everything now goes fine as you wish then it is OK else consider reviewing the recordsource of form.

Frankly, main table seems to be TeamMember. bases, nations and school ministry seems to be raw source for the forms data. We do not know the exact purpose and your objective, but form should have based on team member table as record source (or a query based on that). In the relevant combos, you should use bases, nations and other table as raw source to fetch the data. Then everything should go smoothly. But this all depends on how and what you want to achieve.

Also, use PK suffix for primary key fields and FK for foreign keys. Once one become expert, then he can recognize what he is looking at but beginners like us gets confused and makes mistakes.

I think I am able to explain my views.

Nations is already in the RecordSource of the mainForm itself, but if I select that, then the other combobox won't work. I'm just wondering why Nations is not in my options in the ControlSource tab yet Nation Label is there. All other fields accompanied by labels are there, just not Nations.

Just wondering if I can make it an AutoFill box if I can get all the relevant info into my Bases table - ie when the user keys in the Base Name, the relevant Nation field is automatically populated.

Just thinking out loud here

 

[Megacitizen]

SOLVED !!!!! Or at least I hope so.

checking my relationships, I saw I had no NationID in the School or Ministry Table, so I added it in and created the relationship with the Nations table 1-many.

It's working now !!!

Spent so many days going :banghead:

 

[Megacitizen]

Not critical but....

The Main Form is now sorting all the records according to the Nation a team is from. The American bases are together, the Ozzie bases are together and the Norwegian Base is on it's todd at the bottom of the list. Just wondering if this is normal practice.

Ideally, we want the order in our list to match the order in which each team enters our focus city. The date a team enters the city is found on the Focus City subform. Can the MainForm be coded to order by that control?