Update Field in table based on value selected in combo box

Dear Experts,

Okay this one has me puzzled and I cannot seem to find the answer online. What I am trying to do is this. In my tblStudent table I have a field called "tblStdLSSName". I also have a table called tblLSS that contains a list of names. I would like to use a multiple item form to display all the row values. Then next to each row value I have a combo box that uses the tblLSS table to populate the combo box and assign the control source to lblStdLSSName in the tblStudent table. The theory here is when a user selects a name from the dropdown that name when then replace the name in the tblStdLSSName field. The problem is when I select a different value from the dropdown box I get an error 'beep' and nothing happens. I have attached three screenshots to give you an idea of my layout.
Any ideas on why I cannot update the field value would be most appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Probably a small sample with minimum fake data would be more helpful...by the way i like the "sleeping student"...:)
shogun5Author Commented:

Okay, I've stripped down the database with dummy data to the forms and table needed to illustrate my point. Click on the frmSplash Form (click through the error popups) and click the CHANGE LSS button on the form. What I am trying to do is to change the Literacy Support specialist for each student as needed. When I select a different LSS from the dropdown I get an an error beep and cannot make any changes.

John TsioumprisSoftware & Systems EngineerCommented:
Well i have added a little VBA to overcome the problem...is not the best solution but it works..check it out and let me know
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John TsioumprisSoftware & Systems EngineerCommented:
Well on a second glance here is a much straightforward solution
On the form change the Recordsource to
SELECT DISTINCTROW tblStudent.*, tblStudent.tblStdActive FROM tblYear INNER JOIN (tblStudent INNER JOIN tblSettings ON tblStudent.tblStdYear = tblSettings.tblSettings_SY_Year) ON tblYear.tblYearID = tblStudent.tblStdYear WHERE (((tblStudent.tblStdActive)=True)); 

Open in new window

and change the Property DataEntry from Yes to No...
Take a special attention to the keyword DISTINCTROW ... you couldn't change the field because your Recordsource was not updateable due to the fact the 2nd join you have is between fields that neither are keys...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tblStudent and tblSettings appear to have the wrong primary key.  If you don't have a candidate key, then using an autonumber is correct.  I prefer autonumber PKs in almost all instances.  But, occasionally I have a candidate key that includes multiple columns.  In those cases, I create a unique index to enforce the business rules.  It looks like you are using autonumbers because someone told you that you should but in your mind, some other column is the primary key.  Either remove the autonumber entirely and make the other field the PK or, add a unique index for the other field.  I find that when my candidate key is a single column, it is better and less confusing all around if I simply use the candidate key as the primary key.  When you use some other column as the primary key, you must delete the autonumber.  Autonumbers will eventually generate duplicates if they are not the PK.

Also, your prefixes are too long and will eventually annoy you and your successors because they require too much typing before you get to anything significant and for simple things like opening a table or query in datasheet view, all you will see is the prefix, you won't see any of the relevant part of the column name so you will spend an inordinate amount of time adjusting column sizes.  At a minimum, remove the tbl part since that is completely redundant.  Suffixes are better for several reasons in addition to what I already mentioned.  For example, I have a documentation tool that sorts all columns of all tables alphabetically so it makes it easy to see which columns appear in multiple tables and ensure their attributes are consistent.  You can't do that with a prefix since it always keeps the fields separated by table.
shogun5Author Commented:

Thanks for the helpful suggestions. I will keep these in mind as I continue the learning process in designing client side databases.

shogun5Author Commented:

Yep! That worked. Thanks for the explanation! Just curious. Why would setting the property DataEntry to 'No' be the option I want? I was thinking since I would need to change the value in the LSSName field I would want to be able to have DataEntry or 'change' for that matter. At first when I had it selected to 'yes' nothing showed on the form but when I changed to 'yes' all the records showed.

Thanks again!

shogun5Author Commented:
...I mean when I change DataEntry to 'no' all the records showed....
John TsioumprisSoftware & Systems EngineerCommented:
When you set DataEntry to "yes" you can only enter the current data...
In your case when the Dataset was not upgradeable  it would simply show all the entries since entry was not possible...
When you change to "No" you see all the entries along with the entry you wan to insert...
shogun5Author Commented:
Thanks John!

I have one more question if you are up for it. It's on the same database:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.