Filter one field based on value in another

[snu]

I have a form frmStudentExams. In the form the user will select the current semester from a drop-down list. I would like the value selected to affect another field, Course.

Course currently lists: Instructor Last Name, First Name (Course Name - Section) Semester

in a drop-down list.

I would like only the courses that fall in the previously selected semester to appear in the drop-down list to limit the length of the drop-down choices. How do I go about doing this?

 

[pbaldy]

This type of thing?

http://www.baldyweb.com/CascadingCombo.htm

 

[snu]

Well, I really am not a strong user of SQL or VB. And in looking at what you describe I'm not entirely sure that is the solution I am looking for as what I really want to do is only display certain info in the drop down list so I need to somehow work from within the row source info. I am not sure though as I am not that good with SQL.

Is there a way to do it w/o using it?

 

[snu]

If I were to use that code where do I put it? In a module? I think I probably shouldn't go that route since I have no idea what I'm doing.

 

[missinglinq]

If you don't go that route the old saying "You can't get there from here" pretty much holds true!

Code for Cascading Comboboxes goes in the AfterUpdate event of the first Combobox.

 

[pbaldy]

One of the methods uses a criteria in a query, which only requires a requery of the second combo. You can do that with a macro if you're more comfortable with that.

 

[snu]

Well, I'm willing to try either but I would require a lot of hand-holding to get there I think. Baldy in your link to the cascading lists could you tell me how my field names would fit in there? And where/how do I put the code in?

You can see how basic my level of understanding is. I'm willing to learn though. I have created a module before using SQL but I really just stole the code somewhere.

 

[snu]

Also does it matter that I will have several fields represented in one of my drop down lists?

 

[snu]

I looked at the sample and could probably copy the code, but since my drop down list contains data from multiple fields, how do I adjust for that in my code.

For example, I have it listing all of the following in the 2nd Combobox:

FacultyLastName, FacultyFirstName (CourseName - Section) Semester

If I copy the code you have will it still work? In your example it is only pulling one field.

 

[snu]

I think it has to do with what I have in the field row of my row source query:

CourseTitle: [FacultyLastName] & ", " & [FacultyFirstName] & " (" & [CourseName] & " - " & [Section] & ") " & [Semester]

Then I followed your example and wrote:[Forms]![sbfrmExams]![cboSemester]
in the Criteria row and added in the code.

I checked my names for my comboboxes and they were all correct.

 

[pbaldy]

That shouldn't matter, as long as you filter by semester. Can you post the db with that effort, with no password?

 

[snu]

Oops. Yes I forgot to remove the password. Here is the db with my attempt to use the first example. The form is sbfrmExams.

 

[snu]

I also tried moving one column over in the query and entering Semester with the formula in the criteria instead of putting it under the CourseTitle column and that didn't work either.

 

[pbaldy]

Does this do what you want?

 

[snu]

Thanks, it works in the subform but when I try to do it in the mainform/subform it doesn't work. Do I need to make the changes in the mainform as well as the subform? I thought when I made a change to the subform it automatically applied it to the mainform as well.

 

[snu]

When I try to use the drop-down in the mainform/subform I get this error:

Forms!sbfrmExams!cboSemester

 

[pbaldy]

The syntax to refer to a subform changes:

http://www.mvps.org/access/forms/frm0031.htm

 

[snu]

Okay, based on what I read on your link I changed the programming from within the mainform/subform to what you see below. It did not work. Can you see any error in what I've done?

Option Compare Database

Option Explicit

Private Sub Semester_AfterUpdate()
Me!sbfrmExams.frmStudents_Exams.cboCourseTitle = vbNullString
Me!sbfrmExams.frmStudents_Exams.cboCourseTitle.Requery
End Sub

Private Sub cboSemester_AfterUpdate()
Me!sbfrmExams.frmStudents_Exams.cboCourseTitle = vbNullString
Me!sbfrmExams.frmStudents_Exams.cboCourseTitle.Requery
MsgBox Me!sbfrmExams.frmStudents_Exams.cboSemester
End Sub

On here it looks like there is an extra space in requery but there isn't in my file.

As I've said my knowledge of VBA is quite basic.

 

[pbaldy]

Check that link again. frmStudents_Exams is the main form, isn't it? Where it says "Form" in the link, that should be left alone. Like

Me!sbfrmExams.Form.cboCourseTitle.Requery

 

[snu]

Okay tried this one. I am not sure if it goes in the mainform or subform so I tried both and neither worked.

Option Explicit

Private Sub Semester_AfterUpdate()
Me!sbfrmExams.Form.cboCourseTitle = vbNullString
Me!sbfrmExams.Form.cboCourseTitle.Requery
End Sub

Private Sub cboSemester_AfterUpdate()
Me!sbfrmExams.Form.cboCourseTitle = vbNullString
Me!sbfrmExams.Form.cboCourseTitle.Requery
MsgBox Me!sbfrmExams.Form.cboSemester
End Sub