Using a Module as a field name

[jdwm2310]

Hi,

I created a module called VP, this module assigns a person's name to specific cost center. I created a query, with an expression of VP in the field name.
For example:
Cost center: 32076 (Paul O'Neil)
32077 (Paul O'Neil)
33001 (Bill Gates), so if I put Cost center 32076 in any form Paul O'Neal's name will be shown.

My question is as follow:
I am creating a form to view a report. In this form I have a combo box, text box and a preview report button.
In the preview report button I have a coding of:
Private Sub Command37_Click()
On Error GoTo Err_Command37_Click

Dim stDocName As String
Dim strCrit As String

stDocName = "Consultant Report"
strCrit = "[VP] = '" & Me.Text2 & "'"

DoCmd.OpenReport stDocName, acViewPreview, , strCrit


Exit_Command37_Click:
Exit Sub

The problem is that my combo box is showing a list of the name based on each cost center, for example, Paul O'Neal, Paul O'Neil, Paul O'Neil, Bill Gates, etc. I just want to see a list of name. For example, Paul O'Neil, Bill Gates, etc.

Please Help

 

[Nancy2]

Assuming you only have the name in the row source of the combo box, Add the word DISTINCT after the SELECT in the row source. This will return only unique records from the SQL string feeding the row source.

If you have the cost center field included in the row source, eliminate it.

 

[jdwm2310]

That't the problem the combo box does not have a field name but an expression I created based on the module.
The module assigns name to each cost center. So if a person has 5 cost center his name is going to be repeated 5 times. I don't want to see his name repeated 5 times on the combo box.....In the combo box I just want to view a list of all the names regardless of the how many cost center assigned to them,and then when I click on preview report I can see all of the records for that specific selection. Any suggestions????

 

[Nancy2]

Did you try deleting the expression which fills the cost center from the row source?