DoCmd Apply filter

[Supermansteel]

I am trying to run a Apply filter for everytime someone opens Form_CC it will only show the Test (Test_ID) they are working on. This seems to be the closest I have gotten to filtering it correctly, however it doesn't work when the form is opened. Is there something I am doing wrong on this?

Private Sub Form_Open(Cance l As Integer)
DoCmd.ApplyFilt er Form_CC.Form.fi lter = "Test_ID = 28"
Form_CC.Form.Fi lterOn = True

End Sub

[sierra7]

Hi
Firstly, are you opening the form Form_CC from a control button on another form?
If you are you should use the Access Control Button Wizard. Make sure the magic wand icon is highlighted then click on the command button tool. After you draw your button the wizard will open then make the following selections.
'Form Operations' ==> 'Open Form' , specify the form to be opened (Form_CC) then name the button cmdOpenForm_CC. This will write the following code.

 
Private Sub cmdOpenForm_CC_Click()
On Error GoTo Err_cmdOpenForm_CC_Click
	Dim stDocName As String
	Dim stLinkCriteria As String
	stDocName = "frmChart"
 
	stLinkCriteria = "[Test_ID] =" & Me!ID	
	DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmdOpenForm_CC_Click:
	Exit Sub
Err_cmdOpenForm_CC_Click:
	MsgBox Err.Description
	Resume Exit_cmdOpenForm_CC_Click
 
End Sub

I have added line 7 to define the criteria which assumes that the ID in question is a field on the current form.

If you are doing something more complicated you may need to create a Public variable (lngID) and set it so that the system 'knows' what Test_ID is being worked on and then use the DoCmd.OpenForm method described above.

I can't imagine when you would prefer the Filter technique but you could use it in the On_Current (or On_Open) event when it is simply

 Me.Filter = "Test_ID = " & lngID 
Me.FilterOn = True

I suppose opening the form with ALL records available then Filtering allows the filter to be changed (to view a different Test result) You would have to add an Unbound textbox, say called txtFilterTest_I D, then have an After_Update event ;

 Me.Filter = "Test_ID = " & Me!txtFilterTest_ID 
Me.FilterOn = True

Does this help?

S7

[Stewart Ross]

I am trying to run a Apply filter for everytime someone opens Form_CC it will only show the Test (Test_ID) they are working on. This seems to be the closest I have gotten to filtering it correctly, however it doesn't work when the form is opened. Is there something I am doing wrong on this?

Private Sub Form_Open(Cance l As Integer)
DoCmd.ApplyFilt er Form_CC.Form.fi lter = "Test_ID = 28"
Form_CC.Form.Fi lterOn = True
End Sub

Hi. The DoCmd is not necessary:

 
Form_CC.Filter = "Test_ID = 28"
Form_CC.FilterOn = True

This will apply the filter whether or not you have a record with test_id = 28, however. Filters can also be removed by the user using the default menus (Records, Remove filter) unless you have disabled those menus.

-Stewart

[Supermansteel]

Hi. The DoCmd is not necessary:

 
Form_CC.Filter = "Test_ID = 28"
Form_CC.FilterOn = True

This will apply the filter whether or not you have a record with test_id = 28, however. Filters can also be removed by the user using the default menus (Records, Remove filter) unless you have disabled those menus.

-Stewart

Thanks I really appreciate it.....works Perfectly!!!!!