Help with subform combo box referencing (1 Viewer)

[DDivney]

I'm trying to filter a combo box in a subform, and I almost have it, but I'm having some trouble referencing the subform combo box (access keeps asking me for a parameter value).

My problem is that the value I need is one of many in the combo box (It's a lookup field) (here is its rowsource:

SELECT [Student].[StudentID], [Student].[FirstName], [Student].[LastName], [Student].[2LastName], [Student].[HouseID], [Student].[Gender], [Student].[YearOfEntry] FROM Student

So how do I get around to referencing only ONE of those values (HouseID to be more specific)?

 

[bob fitz]

I'm not really sure what you are trying to do but you can get a value from a column of the selected row in the combo box by using it's Column property. Something like:
i = NameOfComboBoxCtrl.Column(4)
The columns are numbered from left to right starting with 0.

 

[DDivney]

Thanks for the answer! I'd seen that, but it never seems to work for me. Here is the code I'm trying to run:

Private Sub HouseID_AfterUpdate()

Me.Filter = "Me!Student_Team_sub.Form.StudentID.Column(4) = '" & Me!HouseID & "'"
   Me.FilterOn = True
   
End Sub

It just tells me there's an undefined function (the reference to the subform combo box) in the expression.

 

[bob fitz]

Can you tell us again, in different words, what you are trying to do.

 

[DDivney]

Right, sorry.

I'm making a form that creates a new team. Details for the team include the Name, the House (as in Harry Potter), the activity or sport, the tournament, and the gender. The actual team table has a many to many relationship with the student table via a junction table, which is a subform in the form I'm making.

What I'm trying to do is set up a series of filters by which combo boxes in the main form (House and Gender) will limit the available students that can be added in the subform. I'm attaching the database, take a look at the "TeamAdditionForm".

Thanks so much for your help!

 

[bob fitz]

Can you post a copy of the db in A2003 mdb format please.

 

[DDivney]

Something broke in the conversion, but I don't think it was anything too essential.

 

[bob fitz]

Not sure if this is what you want:

 

[DDivney]

Incredible! Thank you so much!

 

[DDivney]

So quickly checking, what you did was add a WHERE clause to the Rowsource, and a requery whenever the subform got focus?

 

[bob fitz]

So quickly checking, what you did was add a WHERE clause to the Rowsource, and a requery whenever the subform got focus?

Correct.

 

[bob fitz]

Hi Daniel,

You may want to alter your sub form slightly, so that a student can only be selected once for each team. You may also want to prevent users from changing the selected values, on the main form, of the “House” and “Gender” combo boxes if team members have already been selected. Other wise you could end up with students of the wrong gender, belonging to the wrong house being left in the team selection.

The db attached demonstrates these refinements