Multicolumn combobox doesn't display a column data (1 Viewer)

[aman]

The multicolumn combobox doesn't display

The combo box named "cboStaff" has

1. column count 11
2. column width property set to as below:
0cm;3cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm;3cm

3. The rowsource of a combobox is set to query "qryAccount" and it is as below:

 SELECT tblstaff.[Staff Number], tblstaff.[Staff Name], tblstaff.[Start Date], tblStaffStage.[End Date], tblRole.[Role Title], tblLineManagerDetails.Current, tblstaff_1.[Staff Name], tblstaff.RoleID, tblstaff.strUser, IIf([tblStaff].[AuthorisedAgent]=True,"Yes","No") AS AuthPer, tblStages.Stage
FROM (tblstaff AS tblstaff_1 RIGHT JOIN ((tblRole RIGHT JOIN tblstaff ON tblRole.RoleID = tblstaff.RoleID) LEFT JOIN tblLineManagerDetails ON tblstaff.[Staff Number] = tblLineManagerDetails.[Staff Number]) ON tblstaff_1.[Staff Number] = tblLineManagerDetails.[Line Manager Staff Number]) INNER JOIN (tblStaffStage INNER JOIN tblStages ON tblStaffStage.StageID = tblStages.StageID) ON tblstaff.[Staff Number] = tblStaffStage.[Staff Number]
WHERE (((tblStaffStage.[End Date]) Is Null) AND ((tblLineManagerDetails.Current)=True))
ORDER BY tblstaff.[Staff Name];

My problem is that the last column "Stage" is showing blank in the combobox. WHen I run the query then there is data in that column but not sure why for all the records that column is blank???

ANy help will be much appreciated.

Thanks

 

[aman]

Just to test if all the columns value appear in combobox I changed the following:

1. column width : 2cm;2cm;2cm;2cm;2cm;2cm;2cm;2cm;2cm;2cm;2cm

Now in the combobox I can see all the columns upto RoleID . But the next column after this appears as "Expr1008" and under it has the same data as the next column strUser.

After struser, the next columns are blank.

 

[CJ_London]

you are using left and right joins so you can expect blank fields - are you sure if you scroll down the columns remain blank? Have you tried ordering by Stage Desc to ensure any values appear at the top

 

[aman]

CJ_London, when the query is run then I can see the data in all the columns . The column Stage stores competency of the staff so its always "Competent","Non Competent" or "N/A".

And also very strange the column "AuthPer" is showing blank too in the combobox. There is another column appearing named "Expr1008" after "RoleID" and before struser which is not in the query and it stores all the data that struser has.

 

[Minty]

What happens if you take that query and paste it into the rowsource of the combo box instead of using the saved query?

 

[aman]

should it make any different Minty?

 

[aman]

Unfortunately it gives the same result even if copy the whole query in the rowsource property of the combobox.

 

[aman]

there is something in the form open event that is causing the problem. It is setting the rowsource of the combobox .I will take a look and get back to you.

 

[CJ_London]

just to complete the background, what version of access are you using? and are the referenced tables in an access backend or SQL server/something else?

And presumably the list width is set to 22cm or more?

Also, if you create a temporary textbox with a control source of

=cboStaff.column(??)

replace ?? with an appropriate column number - what does it show?

Finally have you deleted the combo and recreated it, just in case it is corrupted?