Report does not filter using a combo box look-up

[WAG1]

Hello All



I am having code problems with the following. I hope someone onhere can help!

I have a report that I use to track who the given owner is of every tool in ourcalibration database.
The table where the information is stored in called tbl-1-Calibration1.
I have a query (qry-1-ToolOwner) that pulls the following fields ofinformation from the table.

ID Auto Number Field
ToolOwner1 Short Text Field
SerialNo ShortText Field
ModelNumber ShortText Field
ToolIdentification1 Short Text Field
Location1 Short Text Field

The query runs perfectly and supply's the information on every tool in thedatabase. From this query I created a report(rpt-1-ToolOwner).
When I run the report I again get exactly what the query is asking for and thatis where my problem begins.

I need to filter this report using a combo box where I can look up theemployees name and then the report will show me just the tools owned
by that specific employee. I have tried putting the combo box on mymenu form with a command button. I have directed the combo box to
get its information from a table called (tblEmployee) using Column (Employee)which is a “Short Text” field that list all employees. When thereport runs it either retrieves the entire database or none of it depending onwhat it is look at in the code at the time.

I have also directed the combo box to get its information from the table( tbl-1-Calibration1) in the column called ToolOwner1. Thisgives me a Combo Box lookup that has each employee in it many times (1 time foreach tool owned). I have tried to limitthe list to unique values without success. When the report runs it acts like there is not a filter and gives me theentire database sorted by employee which again is not what I need. I just need the one specific employee that is in the combo box.
Does anyone have any ideas on how I can fix this issue?
WAG1

[orange]

You may get some ideas from this article by Martin Green Dynamic Reports


good luck.

[John_G]

You can also use the Wherecondition option in the DoCmd.OpenReport method, which you would run using a form button. check the Help file for more information.

You would select an employee from the combo box, then include the employee ID in the where condition, something like this:

Docmd.OpenReport "rpt-1-ToolOwner",,,"Employee_ID = " & comboboxvalue

you don't need to change anything in the query or the report itself.

[WAG1]

John_G

I took your advice and made no changes to the Query or report and both still return the entire database when they are ran.

On my menu form in the OnClick event of the command button I placed the following code:

DoCmd.OpenReport "rpt-1-ToolOwner", , , "Employee_ID =" & cboToolOwnerLookup

This returned the following Run-Time error '3075': Syntax error (missing operator) in query expression 'Employee_ID =".

Then I changed the On Click event to

DoCmd.OpenReport "rpt-1-ToolOwner", , , "Employee_ID =" & comboboxvalue and ran it again, received that same error message as the first time

Please note that the combo box is unbound. Not sure if that makes a difference.

WAG1

[John_G]

Please note that the combo box is unbound. Not sure if that makes a difference.

No, it doesn't. Actually, combo boxes are normally unbound when they are used for record selection like that.

The error message suggests that cboToolOwnerLookup is Null or blank. What is the row source for your combo, and what column is it using for its value (the bound column)? As long as you make a selection from the combo, cboToolOwnerLookup should never be Null.

It is also possible that the error is being caused by some code in the report itself - when the error occurs, did you use debug to see what line of code is causing the problem?

[hapm]

Another reason could be that cboToolOwnerLookup is unknown in the context of your code. Double check the name of the control and if the code is in the module for the form the control is placed on. Be sure to have Option Explicit at the top of the module and all variables well declared to avoid typos in variable names.

[WAG1]

John_G

I was not able to get your code to work but your inputs got me to thinking and looking at other possibilities. I would up using the Apply Filter code with a combo box and a command button. Everything now works exactly as I had expected it to. Thank you so much for taking the time to get the old gray matter moving.

WAG