query not working in report but works independently

[Daniel M]

I have a query that i cannot seem to get working inside a report. The query
works independently when i run it but not in the form. Let me explain the
design a little first.

My table is simple, employeeid/username/firstname/lastname. My form has a
drop down box employees that has a query...

SELECT DISTINCTROW Employees.*
FROM Employees
ORDER BY Employees.Username

My form lists only the username column.

In the report if i put the control source as
=Forms!AssetEntry!employees.value I would assume to get the username. I do
not. i get the employeeid (a numerical value). Because i didnt know a work
around for this i created a query...

SELECT username
FROM employees
WHERE employeeid=Forms!AssetEntry!employees;

If i run the query with the form open it works fine. If i run the report it
does not. I am left with #name?

any help would be appreciated. Thanks!

 

[Ken Snell \(MVP\)]

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

I have a query that i cannot seem to get working inside a report. The query
works independently when i run it but not in the form. Let me explain the
design a little first.

My table is simple, employeeid/username/firstname/lastname. My form has a
drop down box employees that has a query...

SELECT DISTINCTROW Employees.*
FROM Employees
ORDER BY Employees.Username

My form lists only the username column.

The above query returns all fields in the Employees table, in the order that
they are listed in the table's design view. Perhaps your query should just
return the username field if that is all you want:

SELECT DISTINCTROW Employees.UserName
FROM Employees
ORDER BY Employees.Username


Otherwise, I'm guessing that your combobox's BoundColumn and ColumnWidths
properties are not properly set up for using a query that returns multiple
fields and you want to use just one of them. Assuming that your EmployeeID
field is the first field in the table, and that your combobox's BoundColumn
value is 1, the combo box will contain the value of the EmployeeID field and
not the value of the Username field.

The BoundColumn property tells the combobox which field contains the value
that is assigned to the Value property of the combobox. If Username is the
desired field, and it's the 4th field in the table, for example, the
BoundColumn value should be 4.

The ColumnWidths property tells the combobox how to display all the fields
that are in the RowSource query. It's a semicolon-delimited list of numbers
and a unit of measure with each number to show the unit of the width
(default in US is " for inches), with the order of numbers corresponding to
the order of fields in the RowSource query. If you do not want a field to
show in the dropdown list, set the appropriate number to 0. When the
dropdown list is not showing, the combobox will display the first visible
field in the combobox, regardless of whether that field is the BoundColumn
field or not.

In the report if i put the control source as
=Forms!AssetEntry!employees.value I would assume to get the username. I do
not. i get the employeeid (a numerical value).

See my comments above. This likely is because of how you set up the
BoundColumn property for the combobox. If you set the BoundColumn value to
the correct number that corresponds to the Username field in the combobox's
RowSource query, the above expression should work.

Because i didnt know a work around for this i created a query...

SELECT username
FROM employees
WHERE employeeid=Forms!AssetEntry!employees;

If i run the query with the form open it works fine. If i run the report
it does not. I am left with #name?

If you tried to use the above query as the ControlSource of a textbox on the
report, you'll get the #Name? error. You cannot use a SQL statement as the
expression for a ControlSource. You'd need to use a DLookup function
instead:

=DLookup("username", "employees", "employeeid=" &
Forms!AssetEntry!employees)

 

[Daniel M]

Got it working! Thanks for the lesson. i fixed a few things and now it is
all working.

 

[Daniel M]

Ok i have another question then.

I created a query that i need run on the report but you said i have to use
dlookup instead. Can you help me with this? My query is pretty complicated
and saved as a query. I tried to put in in the control source as
=LastRefurbish!CComplaints but as you said, it does not work.

LastRefurbish query is...SELECT *
FROM assets AS t1, [SELECT SerialNumber, Max(dateentered) AS
NewestTimeStamp, Max(AssetID) AS LatestRecord FROM assets where statusid=8
GROUP BY SerialNumber ]. AS t2
WHERE t1.serialnumber=t2.serialnumber And t1.dateentered=t2.NewestTimeStamp
And t1.AssetID=t2.LatestRecord and t1.serialnumber
=Forms!AssetEntry!SerialNumber;

This returns the result i need. The CComplaints (column in table and text
box on form) field is not on the current form but was in the previous form
so i need to pull the data based on the current forms serialnumber field.

Any idea how to get this data on the report? Thanks.

 

[Ken Snell \(MVP\)]

I am a bit lost with respect to your reference to "not on the current form
but was in the previous form"? Can you tell us in words the source of data
for each "parameter" field in the LastRefurbish query? For example, are you
wanting to filter the LastRefurbish query by a serial number value on the
"current" form"?

That will help us to identify how to write the DLookup function so that you
can obtain the result that you seek.