Problems with TabControls and SubForm

[jmgibson]

Hi everyone. I'm in the process of creating a way where a user can go in and select (via combo box) a Job title and then assign that Job Title a educational course or courses. Since there are multiple categories of classes (Basics, Supervisory, etc), I created a tabcontrol on my Main Form, and I'm using a sub form to present the classes for that category.

To avoid having redundant subforms (one for each category of class), I've setup a query that that looks for the active Category name for each tab, so when the query runs, it pulls only the classes for that category.

The problem I'm having is when the user goes in, they are presented a screen where they select the Job Title on the main page, then they go through each tab (category of classes) and setup course that that job title needs to take. As long as I stay on that job title, it works fine, but as soon as I change to a different job title, the courses don't present correctly by category, and the drop down I'm using to list available classes (also restricted by the tab category, stop working). My initial thought was that I needed to add a me.requery statement so the forms refresh, but that doesn't seem to work.

I've added the file to my yahoo page, so it should be accessible. The primary form of interest is title "MainForm" which has the subform embedded.

Any help would be extremly appreciated!!! Thanks, Mark

 

[MajP]

in your combo0 event and the tabcontrol change events you need to add

Me.frmTestSub1.Form.Combo1.Requery

 

[jmgibson]

Majp,

Thanks for the suggestion. I've incorporated the line of code above in both change events, but it seemed to make the problem worse. After adding courses to two different roles, the data presented in the sub-form (based on the combo on the main page) seems random in what it presents. It will bring back the data on one of the two tabs for the selected entity, but not both tabs. I've uploaded a new copy of the DB. Would you mind taking a look. Thanks again!!

 

[MajP]

It is working correctly, it just might not be doing what you want it to do. Here is your query without the where clause

courseID courseName	       courseCatVar
1	Basics 101	        varBasics
4	Basics 102	        varBasics
500	Basics 103	        varBasics
5000	QA 101	                varQA
501	QA 102	                varQA
55	QA 103	                varQA
55858	QA test for senior PM	varQA

Therefore regardless of the job title the pulldowns show Basics 101,102,103 when your on the Basics tab, and QA101,102,103 when your on the QA tab. But I am assuming you are expecting something else to happen.

What you have does not makes sense to me. Your main form is basically a table of job titles, and the subform contain information on employees and course completion. The two are linked by job title. What do you think is supposed to happen? What do you want to happen? What relationship are you trying to make? What do you want the combo to show.
I have no idea.

Your tables seem like you are missing something. Are certain jobs required to take certain training? If so I do not think you have the correct table set up. Your table called required training looks like a completed training table. I would think you need

tblRequiredTraining
titleID
courseID

That simply lists job titles and required courses.

I would think that employees are linked to job descriptions. Each job description has a required training.
Each employee needs to complete training for their job. I would then think I would need a main form for an employee with a subform to enter completed training. The pulldown for a given employee would only show those required courses for that employees job description. As an employee completes a class the course is no longer included in the pull down choices. Is this where you are going?

 

[jmgibson]

Overall, I'm attempting to accomplish two functions related to the courses ; (1) assign specific courses to a specific job title (i.e. all project mgrs may be required to take basic 101, 102, 103); and (2) assign courses by employee (i.e. bob a project manager, must take Basic 101, 102. 103, but his supervisor also want him to take QA 101 which is not a normal PM class. Note, I have not developed the employee/course function. I'm waiting to finish the title/course function before going any farther. I plan to use the same design solution that I’m developing to assign courses by title, but I will swap the title drop down with an employee drop down (and leverage the same sub-forms)

So if I went through and assigned the ProjMgr to 2 courses and an employee who is a PM to a specific course (QA101), it would look like this (in the example DB, I deleted the employee table that defines a person by their empnum).

ReqTrainingID courseID EmpNum titleID

246 1(basic 101) - 1

247 4 (basic102) - 1

248 500 (basic103) - 1

249 5000(QA101) 28541 -


Right now, I can set job titles up with a specific course/courses. This works as the data persists to the proper table. What isn't work right now is the following.

When someone accesses the main form and selects a job title from the drop-down, the current courses should appear (in list with check mark) in the active tab. The user should be able to click on the next tab and have the required course for that category appear. And of course, they should be able to go back to the original tab. This is what's broke. It's almost like the query gets confused as to what it should show (the qry responsible for presenting the sub-form data relies on the tabname).

For example, if you access the TestDB example and select "discipline manager" from the drop down, the Basics tab reflects the proper course, but the QA tab still shows the basics and not the QA course this role is currently assigned. Additionally, the drop down in the QA tab (within the sub form) should show me the QA courses but it still shows me the Basic courses.

Thanks!

 

[MajP]

In the new database I can not recreate your problem. If I am on the basics tab my pull down only shows the Basic courses ,if I am on the QA tab the pull down only shows the QA courses. I tried going back and forth between tabs and changing the title and I still get only this behavior. This is what i see on the basic tab

this is what I see on the QA tab

Is that correct, or should it be something else. If you are seeing something else maybe try placing a do events before the requery. Possibly with a faster machine the query executes before the value of the control updates.