Report pulling in key vice combobox text

[blewis81]

Combo box selection ends up as primary key numerical value vice text in report. How do I get it to report the text value selected?

[dashiellx]

Either you need to include the table that holds the corresponding value into the report's recordsource (preferred method), or you'll need to use a function to return the correct value.

[blewis81]

Thank you for the response. I may need a bit more handholding. This is an inherited DB from someone much more advanced than I (self-taught with bad habits more than likely).
The Report's record source is a query of different linked tables. In summary, there is a combo box that pulls from one of the linked tables. However, the query pulls in the key vice the combo box text. I assume I'll need the function for the correct value as I would never be able to untangle the linked tables or find what is wrong.

[dashiellx]

Can you post the SQL of the query? You can get the SQL from the View function on the Home tab when you're in the design view of the query.

[blewis81]

The text I am trying to pull in is for the ContractNumber from the linked Project table with a combo box on a form called Procurement. The linked Project table that I pull from for the combo box has fields: ProjectID (key), ProjectName, ProjectNumber, ProjectManager, pWS_para, ContractNumber, ContractSpecialist.

See SQL for the Report Query trying to fix:

SELECT Procurement.*, Procurement_Items.*, WBS.WBS_Description, Project.PWS_Paragraph, Project.ContractNumber, WBS.CostCenter, Project.ProjectManager, Project.ContractSpecialist
FROM (Project INNER JOIN (Procurement INNER JOIN WBS ON Procurement.WBS = WBS.WBS) ON Project.ProjectNumber = WBS.Project) INNER JOIN Procurement_Items ON Procurement.Procurement_ID = Procurement_Items.Procurement_ID
WHERE (((Procurement.Procurement_ID)=[forms]![frm_Procurement]![Procurement_ID]))
ORDER BY Procurement_Items.Item_ID;

[dashiellx]

OK, I see that this report's recordsource is being filtered by the value entered in the Procurement_ID.


Every field from the Procurement table is being pulled into the query, the table field you want should be in the report's recordsource.


Is the field not available in the textbox control's Control Source on Data Tab in the properties window?

[Micron]

Sounds to me like the bound combo field isn't what's being shown in the control itself. Look at the properties of the combo in design view and see if the bound column is the same as the displayed column, assuming the column count is greater than 1. If not, then you're seeing (e.g.) column 2 but column 1 is the bound column, thus column 1 is being used. Maybe you want the value to be cmbMyCombobox.Column(2)?

[blewis81]

The report's data source is the query I mentioned: "qry_Procurement_Current" The procurement table has a ContractNumber field but they are blank. However, the Project table has the ContractNumber field with data in it that I want pulled in. (Again, this is inherited so not sure why some things are set up this way).

I added ContractNumber from Project to the Query in Design view......it is pulling in to the report. it is just pulling the ProjectID key for the corresponding ContractNumber vice the contract number itself. I am extremely visual so if you need pictures....

[blewis81]

Bound Column: 1; Row Source: SELECT [Project].[ProjectID], [Project].[ContractNumber], [Project].[ProjectName] FROM Project ORDER BY [ProjectName];

I pulled in two columns Project Name and ContractNumber in the wizard. I pulled in ContractNumber first and pulled in ProjectName to act as a description. When selected only the ContractNumber shows in the field. However, when queried...the report is pulling in the ProjectID (key) vice the actual ContractNumber value.

[blewis81]

So, I started playing around with the bound column thing (as mentioned above) and when I changed the number I was able to have the correct value pull into the report!!! So, I guess I can consider this SOLVED...unless it will cause bigger issues down the road???

[Micron]

Seems like you caught on to what I was saying. You should follow up by researching on combo boxes and their columns. The gist of it is, the order of fields/items in its row source (e.g. query) dictates what the order of the columns should be. If you need to use an autonumber field as ID and that comes first and LastName comes next, then a combo with 2 columns showing might show (ignore the dots)
1.....Smith
2.....Brown
If both field widths are sufficient, both values will show. In a case such as this, typically the 1st is set to 0" so that it's hidden, and the 2nd is wide enough to show only the last names. However if the bound column is 1, that's the ID field, which is the one you don't show users. Most often you use what you can't see, but not always. More rare is to use column 0, which only provides the index (basically the row number of the list).

[blewis81]

Just wanted to say a huge THANK YOU to all those who responded. Y'all are seriously the BEST!!!