Link to home
Create AccountLog in
Avatar of c9k9h
c9k9h

asked on

Select DISTINCTROW Combo Box - need to display different column on report

Hello,

I have a combo box that contains names of employees. The names and ID's are stored in a table of their own with 3 columns (EmpID, FirstName, LastName) I have the code in the properties set to:

SELECT DISTINCTROW Employees.EmpID, [Employees].[LastName] & ", " & [Employees].[FirstName] AS Expr1 FROM Employees WHERE (((Employees.Active)=Yes)) ORDER BY [LastName];

This displays their name on the form, which it should, however, when I run a report it displays their "EmpID" (which is what they enter to pull a report for themselves)

My question is: Can I have it so the report will show the actual name selected as it appears in the combo box? The code will have to be done on the reporting side as the Names are stored as "EmpID" in the main recordsource.

I'm not sure if this is possible, but thank you in advance!
Avatar of aesmike
aesmike

You're have a combo box on the report?
just make sure the column width is set to Zero for the emplid column.
Avatar of Rey Obrero (Capricorn1)
create a textbox with control source
=me.comboName.column(1)


or change the bound column of the combo to 1
simpliest way:

Add a combo box to your report. Set it up exactly as it's set up on your form. It should show you names.

If you want to grab the name from the form:

In the control source of a text box put:
=forms!Combobox.Columns(1)

ComboBox being the actual name of the control on the form.

EA


In my suggestion, the control source for the combo box on the report should be EmpID

EA
In the recordsource of the report, Join in the Employees table and create a textbox control with the controlsource =  [Employees].[LastName] & ", " & [Employees].[FirstName]

--
JimFive
What is the recordsource for your report?
Avatar of c9k9h

ASKER

JimFive:

what column would I join the two columns with?

Capricorn I've tried  your suggestion as well and have it showed one instance of the EmpID then for each record after that it showed a "#Name" It also asked for "me" when running the report/query.
sorry that was for the form { Me -> Forms!NameOFForm } it should expounded

Forms!NameOFForm.NameOFCombo.column(1)
Avatar of c9k9h

ASKER

eantar:

I created a new combo box on the report (same as on form with the recordsource pulling from the employees table like it should) added the code in the properties( =forms!Combobox.Columns(1)) where Combobox is "Custodian" <--- name of combo box on form

Will not display any data/names
<I created a new combo box on the report>

 you do not use combo box in reports
Avatar of c9k9h

ASKER

Capricorn:

I created a textbox on the report and added to the control source:: =[Forms]![frmMedRecords].[Custodian].[column](1)

still getting the "#Name"

=[Forms]![frmMedRecords].[Custodian].[column](1)


should be

=[Forms]![frmMedRecords].[Custodian].column(1)
Join them on EmpID
If your form is not open when the report is running you'll get  the #Name error.

EA
If you post your mdb we should be able to help.

Zip it and post it to EE-Stuff.com

EA
Avatar of c9k9h

ASKER

Capricorn:

Are you saying to create the text box with that code on the form itself to pull just the name into it? and then add the newly created textbox on the form to the report?
:c9k9h

the form  frmMedRecords must be open and an item in the combo Custodian must be selected before you open the form
in the control source of the textbox in the report place

=[Forms]![frmMedRecords].[Custodian].[column](1)


the form  frmMedRecords must be open and an item in the combo Custodian must be selected before you open the REPORT
Avatar of c9k9h

ASKER

Capricorn:

I've got it to work based based on just what you said in the last comment. Is this the only way it will show on the report? i have about 50 employees and would like it to show all of their names for the corresponding records Is this not possible?
<i have about 50 employees and would like it to show all of their names for the corresponding records Is this not possible?>

you have to change the record source of your report if you want to display all of them


post the record source of your report
copy the combo box exactly exactly as you have it on the form where it appears on to the report and you should see a name for each empID

Ea
also post the codes that you use to open the report.
ASKER CERTIFIED SOLUTION
Avatar of JimFive
JimFive
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of c9k9h

ASKER

Here is the recordsource for the report it is a query:
SELECT [Medical Records Requests].[Claim Num], [Medical Records Requests].LastName, [Medical Records Requests].FirstName, [Medical Records Requests].ProvName, [Medical Records Requests].DateOrdered, [Medical Records Requests].DateRec, Pending.IntCallDate, Pending.Dx, Pending.Resources, Pending.Director, Pending.DontKnow, Pending.Action, Pending.hrCall, Pending.DatePending, Pending.ICD9, Pending.DateClosed, Pending.PendingStatus, Pending.RT, Pending.WI, Pending.RN, Pending.OSP, Pending.Legal, Pending.Other, Pending.[40RT], Pending.[55RT], Pending.cRI, Pending.cWI, Pending.cRN, Pending.cOSP, Pending.cLegal, Pending.cOther, Pending.c40RT, Pending.c55RT, Pending.Custodian, DateDiff("d",[DatePending],Date()) AS NbrDays, [Medical Records Requests].PendingOrActive, Pending.ResourceBox
FROM [Medical Records Requests] INNER JOIN Pending ON [Medical Records Requests].[Claim Num] = Pending.PendingClaimNum
WHERE (((Pending.Custodian)=[Enter Name]) AND (([Medical Records Requests].PendingOrActive)='Pending'));

Open in new window

In which of these tables:   [Medical Records Requests], Pending

Is the Employee ID Stored, and what is the name of that field?
--
JimFive
Avatar of c9k9h

ASKER

The EmpID is stored in it's own table "Employees"
c9k9h,

post the codes that opens the report .

from the look of it you might need another report for the entire employee.

right now your report is dependent on what is selected from the combo Custodian in form frmMedRecords.

or we can alter the record source ( from the form frmMedRecords) to remove the filter for the employee




@c9k9h
I understand that you have an employees table with all the employee information.  However, How does your report know which Employee's info to put on each record?

--
JimFive

Avatar of c9k9h

ASKER

The Report is only dependent on the Custodian Combo box because it is the "Report By Custodian" I have another query that does not prompt the user to under the custodian name, That is the actual record source i want to use, I Copied the wrong query. As for the code that opens the report, I have a list that populates as reports are added. In the click properties (for the report) the code is pretty simple:
Private Sub lstReports_Click()
 
On Error GoTo Err_stReports_Click
 
Dim stDocName As String
 
 
stDocName = Me.lstReports.Column(0)
 
DoCmd.OpenReport stDocName, acViewPreview
 
Exit_stReports_Click:
    Exit Sub
 
Err_stReports_Click:
    MsgBox Err.Description
    Resume Exit_stReports_Click
    
End Sub

Open in new window

Avatar of c9k9h

ASKER

JimFive: In response to <How does your report know which Employee's info to put on each record?>

Its just pulls from the value entered in the custodian combo box. When i selection is made it displays the custodian's name of the form and saves the "EmpID" in the table. The report shows the "EmpID" rather than the name.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
So it's the Pending.Custodian?

SELECT [Medical Records Requests].[Claim Num], [Medical Records Requests].LastName, [Medical Records Requests].FirstName, [Medical Records Requests].ProvName, [Medical Records Requests].DateOrdered, [Medical Records Requests].DateRec, Pending.IntCallDate, Pending.Dx, Pending.Resources, Pending.Director, Pending.DontKnow, Pending.Action, Pending.hrCall, Pending.DatePending, Pending.ICD9, Pending.DateClosed, Pending.PendingStatus, Pending.RT, Pending.WI, Pending.RN, Pending.OSP, Pending.Legal, Pending.Other, Pending.[40RT], Pending.[55RT], Pending.cRI, Pending.cWI, Pending.cRN, Pending.cOSP, Pending.cLegal, Pending.cOther, Pending.c40RT, Pending.c55RT, Pending.Custodian, DateDiff("d",[DatePending],Date()) AS NbrDays, [Medical Records Requests].PendingOrActive, Pending.ResourceBox, Employees.LastName & ", " & Employees.FirstName as EmpFullName
FROM [Medical Records Requests] INNER JOIN Pending ON [Medical Records Requests].[Claim Num] = Pending.PendingClaimNum
LEFT JOIN Employees ON Employees.EmpID = Pending.Custodian
WHERE (((Pending.Custodian)=[Enter Name]) AND (([Medical Records Requests].PendingOrActive)='Pending'))
Avatar of c9k9h

ASKER

Well I have a claim number we could use that is both in the medical records requests table and the pending table... but it wouldn't be unique necessarily, meaning there can be multiple records (up to 4 or 5) for the same claim #.....

This is the query we would be using
SELECT [Medical Records Requests].[Claim Num], [Medical Records Requests].LastName, [Medical Records Requests].FirstName, [Medical Records Requests].ProvName, [Medical Records Requests].DateOrdered, [Medical Records Requests].DateRec, Pending.IntCallDate, Pending.Dx, Pending.Resources, Pending.Director, Pending.DontKnow, Pending.Action, Pending.hrCall, Pending.DatePending, Pending.ICD9, Pending.DateClosed, Pending.PendingStatus, Pending.RT, Pending.WI, Pending.RN, Pending.OSP, Pending.Legal, Pending.Other, Pending.[40RT], Pending.[55RT], Pending.cRI, Pending.cWI, Pending.cRN, Pending.cOSP, Pending.cLegal, Pending.cOther, Pending.c40RT, Pending.c55RT, Pending.Custodian, DateDiff("d",[DatePending],Date()) AS NbrDays, [Medical Records Requests].PendingOrActive
FROM [Medical Records Requests] INNER JOIN Pending ON [Medical Records Requests].[Claim Num] = Pending.PendingClaimNum
WHERE ((([Medical Records Requests].PendingOrActive)='Pending'));

Open in new window

Avatar of c9k9h

ASKER

JimFive: Getting a missing operator syntax error on that one...
c9k9h,

at this point, i think it will be better if you can zip your db and attach here.
but, before you do
tools>database utilities> compact nad repair db

zip your db ...

now change the  extension .zip to .txt

click the attach file below

click AddFile, locate the file and Submit

Avatar of c9k9h

ASKER

I got the left join to work based on EmpID and Pending.Custodian