AfterUpdate() Event To Filter Sub Form

[chalupabatman]

I have a sub form that by default loads with all OS avaliable. I have added a combo box to the main form with all 4 options of OS that will display, I want the user to be able to select an OS from the combo box and the sub-form auto-filter to that selected OS.



I have this syntax - but I get a dialog box asking me to input the OS so it seems that I have not properly passed the value from the combo box to the procedure? How should I set this up so it functions as I desire?

Private Sub cboFilterStat_AfterUpdate()
	On Error GoTo Proc_Error
	If IsNull(Me.cboFilterStat) Then
	   Me.[_Test].Form.Filter = ""
	   Me.[_Test].Form.FilterOn = False
	Else
	  Me.[_Test].Form.Filter = "[OS]=" & Me.cboFilterStat
	  Me.[_Test].Form.FilterOn = True
	End If
	Proc_Exit:
	   Exit Sub
	Proc_Error:
	   MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
	   Resume Proc_Exit
End Sub

[aytee111]

You can link the subform to the main form using the OS, no code is required.

[chalupabatman]

@aytee111 - how would I do such?

[aytee111]

In design view, with the property sheet open, click on the subform to bring up the properties of the subform. There will be two link fields, master and child. Type in the name of the combobox for the master link and the corresponding name of the field on the subform for the child link. The field on the subform will be a one-to-one match with the first column in the combobox's row source.

[Bulzie]

You might need full path of subform so maybe if the value is text(change the path to be what it really is):

Forms![frmMain]![_Test].Form.Filter = "OS = '" & Me.cboFilterStat & "'"
Forms![frmMain]![_Test].Form.FilterOn = True

the quote part is OS = single double & Me.cboFilterStat & double single double

[chalupabatman]

@aytee111 - i do not see the two link field when I pull up the Property Sheet of the form. Any idea which "Tab" they would be under, format, data, event, other ?

[chalupabatman]

@bulzie - that was it exactly! Good catch on that

[aytee111]

Not the form, the subform object - click once on the subform to bring up its properties. And no, I only use the All tab! They are near the top.

[Bulzie]

Using linkmaster and linkchild is a good way also to filter with no code as aytee111 posted. And also another way, you could go into the Record Source query of the subform(if it is a table make it a query of the table) and in the OS field in criteria, put the path to the combo box on the main form. Then in the After Update in the comob box on main form, Requery the subform with: Forms![frmMain]![_Test].Requery