Filter from form to report in adp front end

[indraT]

Hi there.

I was using MS access database and now i migrated to sql server keeping front end as it is with adp file, but most of the filter is not working. I have a form which display all the active or completed job. in the fomr i put the button when user click it will display a report related to that specific job with filter as jobnumber. in the button i have program like this

Private Sub Command46_Click()
Dim strFilter As String
Dim frm As Form

On Error GoTo Err_Command46_Click

Set frm = Forms!MenuInvoicedJobstest
strFilter = "[JobNumber]=" & frm!JobNumber
DoCmd.OpenReport "ServiceOrder", acViewPreview, , strFilter

Exit_Command46_Click:
Exit Sub

Err_Command46_Click:
MsgBox Err.Description
Resume Exit_Command46_Click

End Sub

and put filter on to yes in report but its not working .

any help please.

Indra.

 

[cmmrfrds]

Try setting the server filter.

In the onopen event of the Report.

Me.AllowFilters = True
Me.FilterOn = True
Me.ServerFilter = "your filter"

 

[indraT]

I tried this but its giving me error message saying " The setting you enter is not valid for this property".

Thanks.
Indra.

 

[cmmrfrds]

Can you show your code and what procedure the code is in.

 

[indraT]

I just open the report and click on OnOpen and in the function:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = True
Me.FilterOn = True
Me.ServerFilter = "JobNumber"

End Sub

thanks.

 

[cmmrfrds]

Private Sub Report_Open(Cancel As Integer)
Me.Filter = True
Me.FilterOn = True
'- this need to be valid field name and syntax for the where clause.
Me.ServerFilter = "JobNumber = 10" '- numeric
Me.ServerFilter = "JobNumber = '" & 10 & "'" '- char
End Sub

 

[indraT]

Do i have to specify the particular job number, if so this is not going to fit for our criteria. what i wanted is in my form it display all the active job and there is a button on each job inf. when user click that button, it should open the report only for that job number. so i canot specify one job. I am not sure wheather i understand or not.

Thanks for your help. Actually its working particall, when i put the server filter but not wroking between report and subreport again.

Any information please.

Indra.

 

[cmmrfrds]

Pass the job number to the report in the OpenArgs. This is an option on the DoCmd.OpenReport.

Me.ServerFilter = "JobNumber = '" & Me.OpenArgs & "'"

 

[indraT]

thanks for help.

Does server filter have problem with the dbo. When i migrate from access to sql, some of the querry (UDF) had problem and i had to redo that again which has dbo infornt of table or any slelct column. i am thinking when i set the server filter between form and report or report and subreport its working fine with those report which has no dbo permission, but not displaying with the subreport using query with dbo permission. Does this creating the problem.

Thanks.

 

[RandyMyers]

I have used this message to filter records on a form. The question I have, which is related here I believe, is there a way to filter on two fields with this message. I have only been able to get it to filter on one field.

 

[RandyMyers]

sorry typo...

... with this method.... not message...

 

[cmmrfrds]

It is a good practice to prefix all sql server objects with the dbo. since this will be necessary for all users that are not the database owner, but the filter is just a where clause in the sql statement generated by the Form/Report.

dbo.yourtablename
dbo.yourStoreProcedure

Randy, a filter is the where clause in the sql statement and there is no limit to 1 criteria.

Me.Filter = "id = 1 and name = 'sam'

 

[indraT]

Hi there,

thanks again for all your help, but how can i fix the prefix dbo in all the object in sql. I donot know why filtering is not working with object or querry having dbo prefix.

any inf. please.

Indra.

 

[RandyMyers]

The type of filtering I am referring to is having a popup form open ot select records to filter the record set and open another form so only the selected records a cycled through, ie work orders for a particular tech.... the following code is on the OK button of the "search" form...

Private Sub Command31_Click()

DoCmd.OpenForm "frmWorkOrdersEdit_test2"

If Me.cmbEOCID <> "" Then
Form_frmWorkOrdersEdit_test2.Filter = "EOCID =" & " '" & Me.cmbEOCID.Value & "'"
Form_frmWorkOrdersEdit_test2.FilterOn = True
Form_frmWorkOrdersEdit_test2.Filtered.Visible = True
DoCmd.RunMacro "mcrCloseWorkOrdersSearch_test"
Exit Sub
ElseIf Me.cmbEOCAsset <> "" Then
Form_frmWorkOrdersEdit_test2.Filter = "EOCAsset =" & " '" & Me.cmbEOCAsset.Value & "'"
Form_frmWorkOrdersEdit_test2.FilterOn = True
Form_frmWorkOrdersEdit_test2.Filtered.Visible = True
DoCmd.RunMacro "mcrCloseWorkOrdersSearch_test"
Exit Sub
ElseIf Me.cmbWOType <> "" Then
Form_frmWorkOrdersEdit_test2.Filter = "WOType =" & " '" & Me.cmbWOType.Value & "'"
Form_frmWorkOrdersEdit_test2.FilterOn = True
Form_frmWorkOrdersEdit_test2.Filtered.Visible = True
DoCmd.RunMacro "mcrCloseWorkOrdersSearch_test"
Exit Sub
ElseIf Me.cmbWOITStaff <> "" Then
Form_frmWorkOrdersEdit_test2.Filter = "WOITStaff Like " & " '" & Me.cmbWOITStaff.Value & "'"
Form_frmWorkOrdersEdit_test2.FilterOn = True
Form_frmWorkOrdersEdit_test2.Filtered.Visible = True
DoCmd.RunMacro "mcrCloseWorkOrdersSearch_test"
Exit Sub
Else
MsgBox "Click OK to continue", vbOKOnly, "No Filter Selected..."

End If

End Sub


The problem is this only allows one filtered field.... I have yet to get this to work with two filtered fields...

The form that is opened has a button to unfilter the records which will turn filtering off...

Any ideas of how to setup two or more filters?

 

[cjowsey]

Off the top of my head, so it is not tested or optimised, you might try something like this.

Private Sub Command31_Click()

	Dim strFilter AS String
	Dim blnFilter AS Boolean

	strFilter = ""
	blnFilter = False

	DoCmd.OpenForm "frmWorkOrdersEdit_test2"

	If Me.cmbEOCID <> "" Then
		strFilter = "(EOCID =" & " '" & Me.cmbEOCID.Value & "')"
		blnFilter = true
	End If

	If Me.cmbEOCAsset <> "" Then
		If Len(strFilter) > 0 Then
			strFilter = strFilter & " AND "
		End If
		strFilter = strFilter & "EOCAsset =" & " '" & Me.cmbEOCAsset.Value & "'"
	End If

	If Me.cmbWOType <> "" Then
		If Len(strFilter) > 0 Then
			strFilter = strFilter & " AND "
		End If
		strFilter = strFilter & ""WOType =" & " '" & Me.cmbWOType.Value & "'"
	End If

	If Me.cmbWOITStaff <> "" Then
		If Len(strFilter) > 0 Then
			strFilter = strFilter & " AND "
		End If
		strFilter = strFilter & "WOITStaff Like " & " '" & Me.cmbWOITStaff.Value & "'"
	End If

	If blnFilter then
		Form_frmWorkOrdersEdit_test2.Filter = strFilter
		Form_frmWorkOrdersEdit_test2.FilterOn = True
		Form_frmWorkOrdersEdit_test2.Filtered.Visible = True
		DoCmd.RunMacro "mcrCloseWorkOrdersSearch_test"
	Else
		MsgBox "Click OK to continue", vbOKOnly, "No Filter Selected..."
	End If

End Sub

This should allow you to use any combination of the combo boxes.