Report filter form using multiple comboboxes.

[Stephenson]

Hello all,



I'm trying to create a form, frmFilterForm to open report, JobReport and filter the records based on comboboxes in frmFilterForm. I have three fields I'd like to use as filters. cboDate, cboJobName, and cboService. Currently I'm working with the following code, but getting strange results.

Private Sub cmdOpenReport_Click()    
    Dim strWhere As String
    strWhere = "Service='" & Me.cboService & "'"
    DoCmd.OpenReport "JobReport", acViewPreview, , WhereCondition:=strWhere
End Sub

When this is run it will open the report and show the correct service text, but it will only do this once. It also replaces the Service text in Table Service with the ID number. It will continue to change that one field with any service I choose to filter with. Both frmFilterForm and JobReport are based on a query that shows all the results from EmployeeWorkLog and those records that match from tables Service and Jobs. One of the fields in EmployeeWorkLog is DateWorked.

If you see the errors in my ways please help me out.

Thanks for having a look.

[pbaldy]

It sounds like the combo is bound. Based on how you're using it, it shouldn't be.

[Stephenson]

pbaldy,

You are correct, the combo was bound. I made it unbound and now when I click to open the Report it shows no records at all.

For the combo I'm using the record source

SELECT Service.ID, Service.Service FROM Service;

For the Report I'm using the record source

SELECT [Employees].[FirstName] & " " & [Employees].[LastName] AS EmployeeName, Jobs.JobName, Equipment.Model, Service.Service, Labor.Labor, EmployeeWorkLog.LaborHours, EmployeeWorkLog.EquipmentHours, EmployeeWorkLog.Notes, EmployeeWorkLog.DateWorked, Jobs.IDFROM Service RIGHT JOIN (Labor RIGHT JOIN (Jobs RIGHT JOIN (Equipment RIGHT JOIN (Employees RIGHT JOIN EmployeeWorkLog ON Employees.ID = EmployeeWorkLog.EmployeeID) ON Equipment.ID = EmployeeWorkLog.EquipmentID) ON Jobs.ID = EmployeeWorkLog.JobID) ON Labor.ID = EmployeeWorkLog.LaborID) ON Service.ID = EmployeeWorkLog.ServiceID
ORDER BY [Employees].[FirstName] & " " & [Employees].[LastName];

Do you have any suggestions for this?

[pbaldy]

Looks like you're comparing the ID to the description. Add the service ID to the report query and use that in your code.

[Stephenson]

Paul,

Thanks for the help.

I've added the Service ID to the reports query and changed the code to this.

Private Sub cmdOpenReport_Click()    
    Dim strWhere As String
    strWhere = "ServiceID='" & Me.cboService & "'"
    DoCmd.OpenReport "JobReport", acViewPreview, , WhereCondition:=strWhere
End Sub

Now it asks me to enter a parameter value for ServiceID. Have I put it in incorrectly in the code?

[pbaldy]

More like the wrong field name. Based on this:

SELECT Service.ID, Service.Service FROM Service;

It should just be ID, or if there are more than one field with that name then Service.ID

[Stephenson]

I've replaced ServiceID with Service.ID. It is now telling me error 3464, data type mismatched in criteria expression. Should I not have a filed name the same as the table name?

[pbaldy]

Try

strWhere = "Service.ID=" & Me.cboService

[Stephenson]

Sure enough that fixed it.

Thank you for the great help Paul.

[pbaldy]

Happy to help!