Form/Subform (Subform Combo Box w/Lookup Table)

[padlocked17]

I have two forms that I am working with. frmMain and subfrmMain. subfrmMain is the subform on frmMain. subformMain is a continous form used to track the scores for that particular individual and courseID associated to that person.

I am loading a StudentID and CourseID in the frmMain from the tblResults Table. I then have the Master/Child Setup in frmMain and subfrmMain for the StudentID and CourseID. No problems there.

Based on the course ID I have several tests that are only available to that particular CourseID the individual is associated with. I am able to pull through the subfrmMain query a text box that has the correct TestID associated with data entered in manually into the tables but I'm not able to figure out how to use a combo box that will have the test that was entered manually to display that already selected in the drop-down.

I beleive I have been able to narrow down the combo box to display only the applicable tests to the corresponding CourseID, but whenever I use the Record buttons to navigate, the info in the drop-down doesn't change according to the CourseID but the test score entered for each student and different test does change on the same line.

Any ideas that could help me out?

 

[qdogfball]

I am not sure, but you might need to requery the subform in the combo boxes After update property.

An example from the sample databases on this site show this..

EX)

Private Sub lboEmployeeID_AfterUpdate()
    Me.sfrmEmployeeID.Requery
End Sub

srfmEmployeeID is the subform
lboEmployeeID is the List box (could use any type of box)

 

[padlocked17]

I've tried requerying the subform as a whole, do I need to requery the combo box specifically?

 

[CraigDolphin]

In the on_enter event of your combo box put some code like

Me.ComboboxName.Requery

 

[padlocked17]

OK, I've simplified things down for testing. I have the main form simply scrolling through the records where it's changing the StudentID and CourseID. I then have the subform's StudentID and CourseID linked with the Master/Child relationship. I've also assigned the combo box to have a control source of the EvalID so I have the name appearing in the combo box, but what I can't figure out how to do now is limit the results that appear in the combo box based on the CourseID that is associated with a Student in a table that simply puts a StudentID together with a CourseID.

I just want to filter that Combo box to only show the Test types that are applicable to the Course type.

The relationship diagram is attached.

 

[CraigDolphin]

limit the results that appear in the combo box based on the CourseID that is associated with a Student in a table that simply puts a StudentID together with a CourseID.

If you have the correct sql in your combo box then the on_enter code I suggested above should work fine.

Your sql should include a field CourseID, with a criteria like:
Forms!MainFormName!CourseID.

As I don't know what other fields you want shown in the combo, or which tables they are supposed to be drawn from, I can't guess as to the remainder of the sql.

It might be advantageous for you to post a stripped down, zipped copy of the db and a clear explanation as to which form/subform you are having trouble with if you want further assistance.

 

[padlocked17]

I have attached a copy of the stripped down DB.

I am working on frmMain and subfrmTesting.

I basically have a table that assigns a student to a class and qualification type. From there, based on the qualification type, there are only a certain number of tests that person is applicable for. The tests are listed in tblEvalsTest, the Courses are paired with the tests in tblCourseEvalsTests and the courses are assigned to students in "tblClassStudent".

On the subfrmTesting, I'm trying to get the combo box to populate with the course the score has already been entered for and also only display the applicable tests in the continuous forms for more to be entered.

I'd also like to work it in to when the user has all applicable tests with scores already entered in, the db won't allow for any more scores to be entered.

 

[CraigDolphin]

Been a bit busy but does the attached do what you expect?

(Note: I also renamed a textbox control on another subform that was causing errors because you named it 'Date' which is a reserved word in access)

HTH

 

[padlocked17]

Almost. I just need it to requery the combo boxes as soon as a new StudentID or CourseID is loaded. Should I reference this requery from the Main form when a change is detected in the StudentID.

I tried the following on the onChange of the StudentID with no success:

Forms![frmMain]![subfrmTesting].Form![cboEvalTestID].Requery

 

[padlocked17]

I've attached a new revised copy of what I was working on. I added a Combobox that would select the student's name but I'm not sure if I'm going about it correctly. I have it writing the changes to a text field in the frmMain. I didn't know if there was a better way.

 

[CraigDolphin]

Well, what I think you're after is a bit more involved that just the requery. You cannot add new records via the form because the record source for the form was drawn from multiple tables, including a many-to-many relationship, that prevents Access from knowing how to populate the underlaying tables.

I've re-jigged it to that it draws from the one table and added the classID field control (since one course can potentially have many classes). This now allows you to add results to the results table, as well as see those already there.

Incidentally, the correct reference you were looking for was Me.![subfrmTesting].Form![Combo5].Requery ...you have to use the name of the control itself, not the field it's bound to (although oftimes these are set the same).

HTH

 

[padlocked17]

Me.![subfrmTesting].Form![cboClassID].Requery

The "!" caused a problem on this one. I changed the lines to the following:

Me.[subfrmTesting].Form![cboClassID].Requery
    Me.[subfrmTesting].Form![cboEvalTestID].Requery

I've now got a combo box on the mainform that has the following code. Why wouldn't this code refresh the cobo boxes in the subform when a new student was selected?

Private Sub cboSelectStudent_Change()
DoCmd.ShowAllRecords
Me!StudentID.SetFocus
DoCmd.FindRecord Me!cboSelectStudent

'DoCmd.ShowAllRecords
Me!CourseID.SetFocus
DoCmd.FindRecord Me!cboSelectStudent

Me.[subfrmTesting].Form![cboClassID].Requery
Me.[subfrmTesting].Form![cboEvalTestID].Requery

'Set value of combo box equal to an empty string
Me!cboSelectStudent.Value = ""
End Sub

 

[padlocked17]

In regards to adding scores, I'm also getting a "The object doesn't contain the automation object "CourseID"" error. I know that this is because we haven't assigned the student to a class/qualification type yet for the new students. How would I trap this error and tell them that if the CourseID doens't exist, they need to add the student to a class?

 

[CraigDolphin]

Why don't you post your latest version and I'll have a look. Regarding your first question(s)...off the top of my head you probably have an error in your code that is stopping the vba in its tracks... is the combo bound to a field that doesn't allow zero length strings? If you put in an error handler, what error do you get? What are you actually trying to achieve with the combo box?

If you want to force the user to select the CourseID and StudentID before entering the scores then why not use the on-enter event of the subform to test for nulls in those fields and put the focus in those fields in they are empty?

 

[padlocked17]

I've attached the DB. I've got the combo box selecting a student and then populating the rest of the main form and the sub forms. I've made some comments on what I'm trying to do on the forms themselves. Essentially we are trying to make the frmMain a little more "fool-proof" and then also the same with the frmClassStudents. I haven't finished building subfrmTesting yet, but it will essentially be the same as subfrmMain.

 

[CraigDolphin]

On this form I don't want to show any historical data. I also need to exclude students from the drop down based on whether or not they are assigned to a class AND if that class closed date IS NOT NULL.

Do you mean you want to exclude the student if the class that they are assigned to has a class closed date? If I exclude students with a class closed date that IS NOT NULL, zero students appear in the list because all classes in your db so far have a null closed date.

 

[padlocked17]

Do you mean you want to exclude the student if the class that they are assigned to has a class closed date? If I exclude students with a class closed date that IS NOT NULL, zero students appear in the list because all classes in your db so far have a null closed date.

You are correct. I want the list to not allow scores to be edited or viewed from this data entry form if the class they belonged to has been closed out. I'll use a report to pull info from classes in the past. I basically want to keep it clutter free and only display the students who are in active classes and we are determining if a class is active or not based on whether or not there is a date in the ClosedDate column.

I don't have a student entered in that is assigned to a closed class at the moment. Should I populate that and re-up the db?

Thanks a ton for the help. I'm getting so close I can taste it (Thanks to you guys for doing some of the cooking)

 

[CraigDolphin]

Well, here's your db again. I've gotten your main form working (I think) and also edited the frmClasses form to do what you want (I think).

As for your other form, I have never used listboxes so you'll have to hope someone else can advise you on that.
HTH

 

[padlocked17]

Awesome. That does accomplish what I was looking for. I'll apply that same principle for the frmClasses to the frmMain and I think it will work out perfectly.

Thanks a bunch for taking the time to help me out.

 

[padlocked17]

Private Sub Form_Current()
'
If Not IsNull(Me.StudentID) Then Me.cboSelectStudent = Me.StudentID
    'Let's hide the visibility of the form fields if a selection isn't made on cboSelectStudent
    If Me.cboSelectStudent & "" = "" Then
        Me.StudentID.Visible = False
        Me.CourseID.Visible = False
        Me.LastName.Visible = False
        Me.FirstName.Visible = False
        Me.MI.Visible = False
        Me.[subfrmTesting].Form![StudentID].Visible = False
        Me.[subfrmTesting].Form![cboClassID].Visible = False
        Me.[subfrmTesting].Form![cboEvalTestID].Visible = False
        Me.[subfrmTesting].Form![CompletedDate].Visible = False
        Me.[subfrmTesting].Form![Result].Visible = False
        Me.[subfrmMainEvaluations].Form![EvalDate].Visible = False
        Me.[subfrmMainBoldface].Form![txtDate].Visible = False
        Me.[subfrmMainBoldface].Form![Completed].Visible = False
    'If a selection is made from cboSelectStudent, we'll show the form fields
    Else
        Me.StudentID.Visible = True
        Me.CourseID.Visible = True
        Me.LastName.Visible = True
        Me.FirstName.Visible = True
        Me.MI.Visible = True
        Me.[subfrmTesting].Form![StudentID].Visible = True
        Me.[subfrmTesting].Form![cboClassID].Visible = True
        Me.[subfrmTesting].Form![cboEvalTestID].Visible = True
        Me.[subfrmTesting].Form![CompletedDate].Visible = True
        Me.[subfrmTesting].Form![Result].Visible = True
        Me.[subfrmMainEvaluations].Form![EvalDate].Visible = True
        Me.[subfrmMainBoldface].Form![txtDate].Visible = True
        Me.[subfrmMainBoldface].Form![Completed].Visible = True
    End If
End Sub

I tried using the above to hide data on the frmMain but I'm pretty sure I'm overlooking something to hide all fields until a name is selected from the drop-down menu on there.