Problem Populating Fields in Subform

[forumer]

Hello guys,




I have database as follows,

1- student table: stID (PK), firstName, lastName

2-course table: courseID (PK), courseName, courseDescription

3- studentCourse: stID(FK),courseID (FK)

Now, I have created a main form based on student table and a subform based on studentCourse table. However, because I needed to populate the courseName and CourseDescription in the subform I changed its record source to a query (stID, courseID, courseName, courseDescription) so that when I move between the subform records the courseName and courseDescription still show in the subform.

To populate the courseName and courseDescription in the subform I changed the courseID to a combobox so that it brings the courseName and courseDescription from the course table and show it in the subform.

Everything is working fine so far BUT when I choose to input a courseID manually into the combobox, it does not populate the courseName and courseDescription as it does when I choose the courseID from the combobox.

Any help will greatly be appreciated.

Thanks.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[forumer]

Anyone willing to help, guys?

[June7]

Can't determine cause just from your description.
I offered to debug db.

[forumer]

Here is the database attached if anyone wants to help.
http://www.filefactory.com/file/7kfy...egSystem.accdb

[forumer]

This is the link again if the first one doesn't work. Please try to help.

http://www.filefactory.com/file/636b...egSystem.accdb

[June7]

You have a query as RecordSource for the subform. The query joins Course and StudentCourse tables with INNER join. This requires related records in both tables for any to display. Change the query to RIGHT join.

SELECT studentCourse.stID, studentCourse.courseID, course.courseName, course.courseDescription
FROM course RIGHT JOIN studentCourse ON course.courseID = studentCourse.courseID;

Could put that SQL statement directly in the RecordSource property.

CourseName and CourseDescription textboxes should be Locked Yes and TabStop No. The stID textbox on main form should be Locked Yes and TabStop No or not even visible.

Delete the Change event code behind the Course combobox.

The Master/Child links generates an error. Never seen that before.

Why does the main form trigger an input popup when moving to a new student record? Never seen that either.

I've never seen errors like that. Everything I tried in effort to fix failed. I am inclined to think something about the existing objects is corrupted beyond repair. I built new tables and forms and tested form/subform behavior without any code and the Master/Child links work. And a Course combobox also behaves properly. I suggest you do the same.

[forumer]

Thank you very much for solving the problem, June7. Interestingly, now error accured as you explained. Everything works fine. I just now want to now the difference between the "INNER" and "RIGHT" It never crossed my mind that the error might have been caused by getting the wrong word in the query!!

Also, do you think having an empty form as a background running at the beginning of the application start,as I did in the copy I sent you, to hide the Access Design Window is a good idea? The only idea? Do you have a better one, please?

Again thank you very much, June7.

[June7]

It's certainly a much simpler approach than other methods to achieve the same effect.

However, since the form can be moved, it really doesn't hide anything. Set form Moveable property to No. Also, right click on the form header offers shortcut menu with Close option. This also reveals the Access app. Database optional settings can disable shortcut menus.

[forumer]

It's certainly a much simpler approach than other methods to achieve the same effect.

However, since the form can be moved, it really doesn't hide anything. Set form Moveable property to No. Also, right click on the form header offers shortcut menu with Close option. This also reveals the Access app. Database optional settings can disable shortcut menus.

I've just now realised that the "INNER" or "RIGHT" has nothing to do with the cause of the problem at all. It works fine with both "INNER" and "RIGHT". The real cause of the problem was actually the ON CHANGE event which is behind the combobox in the subform. When you asked my to remove it, everything works fine.

I'll mark this thread as solved.

Thank you for your help, June7