Combo populate Listbox (1 Viewer)

[mumbles10]

I think I am being really dense, but I can't seem to get the listbox to populate based on the combo selection.

The combo's rowsourcetype is FIELD LIST and the rowsource is a query:

SELECT DISTINCTROW tblEmployees.[First Name], tblEmployees.[Last Name], tblEmployees.[Pulse ID], tblSystems.[System Name], tblEmployees.Email, tblDepartment.Department, tblBusinessDescp.BusinessDescription, tblManagers.Manager, tblJobBands.[Job Band], tblThirdPartyCo.ThirdPartyCompany
FROM tblSystems RIGHT JOIN ((tblThirdPartyCo RIGHT JOIN (tblJobBands RIGHT JOIN (tblManagers RIGHT JOIN (tblBusinessDescp RIGHT JOIN (tblDepartment RIGHT JOIN tblEmployees ON tblDepartment.DepartmentID = tblEmployees.[Department ID]) ON tblBusinessDescp.BusinessDescriptionID = tblEmployees.[Business Description ID]) ON tblManagers.ManagerID = tblEmployees.[Manager ID]) ON tblJobBands.JobBandID = tblEmployees.[Job Band ID]) ON tblThirdPartyCo.ThirdPartyID = tblEmployees.[Third Party ID]) LEFT JOIN tblEmployeeAccess ON tblEmployees.EmployeeID = tblEmployeeAccess.[Employee ID]) ON tblSystems.SystemID = tblEmployeeAccess.[System ID]
WHERE (((tblEmployees.[First Name])=[Forms]![frmInfo]![cboInformation]) AND ((tblEmployees.[Last Name])=[Forms]![frmInfo]![cboInformation]) AND ((tblEmployees.[Pulse ID])=[Forms]![frmInfo]![cboInformation]) AND ((tblSystems.[System Name])=[Forms]![frmInfo]![cboInformation]) AND ((tblEmployees.Email)=[Forms]![frmInfo]![cboInformation]) AND ((tblDepartment.Department)=[Forms]![frmInfo]![cboInformation]) AND ((tblBusinessDescp.BusinessDescription)=[Forms]![frmInfo]![cboInformation]) AND ((tblManagers.Manager)=[Forms]![frmInfo]![cboInformation]) AND ((tblJobBands.[Job Band])=[Forms]![frmInfo]![cboInformation]) AND ((tblThirdPartyCo.ThirdPartyCompany)=[Forms]![frmInfo]![cboInformation]));


The listbox rowsource is a query:

SELECT qryInfo.[First Name], qryInfo.[Last Name], qryInfo.[Pulse ID], qryInfo.[System Name], qryInfo.Email, qryInfo.Department, qryInfo.BusinessDescription, qryInfo.Manager, qryInfo.[Job Band], qryInfo.ThirdPartyCompany
FROM qryInfo;

I am not sure why the listbox won't list the information based on the combo selection...

Any thoughts?

 

[boblarson]

What's the SQL for qryInfo? The listbox rowsource, not counting the possibility of something in qryInfo, doesn't have any criteria based on the combo. So, unless it is in qryInfo, you need to add some.

 

[mumbles10]

What's the SQL for qryInfo? The listbox rowsource, not counting the possibility of something in qryInfo, doesn't have any criteria based on the combo. So, unless it is in qryInfo, you need to add some.

I figured out part of it... the qryInfo had AND statements instead of OR.

Here is the SQL...
SELECT DISTINCTROW tblEmployees.[First Name], tblEmployees.[Last Name], tblEmployees.[Pulse ID], tblSystems.[System Name], tblEmployees.Email, tblDepartment.Department, tblBusinessDescp.BusinessDescription, tblManagers.Manager, tblJobBands.[Job Band], tblThirdPartyCo.ThirdPartyCompany
FROM tblSystems RIGHT JOIN ((tblThirdPartyCo RIGHT JOIN (tblJobBands RIGHT JOIN (tblManagers RIGHT JOIN (tblBusinessDescp RIGHT JOIN (tblDepartment RIGHT JOIN tblEmployees ON tblDepartment.DepartmentID=tblEmployees.[Department ID]) ON tblBusinessDescp.BusinessDescriptionID=tblEmployees.[Business Description ID]) ON tblManagers.ManagerID=tblEmployees.[Manager ID]) ON tblJobBands.JobBandID=tblEmployees.[Job Band ID]) ON tblThirdPartyCo.ThirdPartyID=tblEmployees.[Third Party ID]) LEFT JOIN tblEmployeeAccess ON tblEmployees.EmployeeID=tblEmployeeAccess.[Employee ID]) ON tblSystems.SystemID=tblEmployeeAccess.[System ID]
WHERE (((tblEmployees.[First Name])=Forms!frmInfo!cboInformation)) Or (((tblEmployees.[Last Name])=Forms!frmInfo!cboInformation) Or ((tblDepartment.Department)=Forms!frmInfo!cboInformation) Or ((tblBusinessDescp.BusinessDescription)=Forms!frmInfo!cboInformation) Or ((tblManagers.Manager)=Forms!frmInfo!cboInformation) Or ((tblJobBands.[Job Band])=Forms!frmInfo!cboInformation) Or ((tblThirdPartyCo.ThirdPartyCompany)=Forms!frmInfo!cboInformation)) Or (((tblEmployees.[Pulse ID])=Forms!frmInfo!cboInformation)) Or (((tblSystems.[System Name])=Forms!frmInfo!cboInformation) Or ((tblEmployees.Email)=Forms!frmInfo!cboInformation));


Now I can get the query to run outside of the form... where I could type in the parameter... but its showing a blank in the listbox when I select the option from the combo.

 

[mumbles10]

I figured out part of it... the qryInfo had AND statements instead of OR.

Here is the SQL...
SELECT DISTINCTROW tblEmployees.[First Name], tblEmployees.[Last Name], tblEmployees.[Pulse ID], tblSystems.[System Name], tblEmployees.Email, tblDepartment.Department, tblBusinessDescp.BusinessDescription, tblManagers.Manager, tblJobBands.[Job Band], tblThirdPartyCo.ThirdPartyCompany
FROM tblSystems RIGHT JOIN ((tblThirdPartyCo RIGHT JOIN (tblJobBands RIGHT JOIN (tblManagers RIGHT JOIN (tblBusinessDescp RIGHT JOIN (tblDepartment RIGHT JOIN tblEmployees ON tblDepartment.DepartmentID=tblEmployees.[Department ID]) ON tblBusinessDescp.BusinessDescriptionID=tblEmployees.[Business Description ID]) ON tblManagers.ManagerID=tblEmployees.[Manager ID]) ON tblJobBands.JobBandID=tblEmployees.[Job Band ID]) ON tblThirdPartyCo.ThirdPartyID=tblEmployees.[Third Party ID]) LEFT JOIN tblEmployeeAccess ON tblEmployees.EmployeeID=tblEmployeeAccess.[Employee ID]) ON tblSystems.SystemID=tblEmployeeAccess.[System ID]
WHERE (((tblEmployees.[First Name])=Forms!frmInfo!cboInformation)) Or (((tblEmployees.[Last Name])=Forms!frmInfo!cboInformation) Or ((tblDepartment.Department)=Forms!frmInfo!cboInformation) Or ((tblBusinessDescp.BusinessDescription)=Forms!frmInfo!cboInformation) Or ((tblManagers.Manager)=Forms!frmInfo!cboInformation) Or ((tblJobBands.[Job Band])=Forms!frmInfo!cboInformation) Or ((tblThirdPartyCo.ThirdPartyCompany)=Forms!frmInfo!cboInformation)) Or (((tblEmployees.[Pulse ID])=Forms!frmInfo!cboInformation)) Or (((tblSystems.[System Name])=Forms!frmInfo!cboInformation) Or ((tblEmployees.Email)=Forms!frmInfo!cboInformation));


Now I can get the query to run outside of the form... where I could type in the parameter... but its showing a blank in the listbox when I select the option from the combo.

For some reason I think the rowsource of the listbox isn't correct.

SELECT qryInfo.[First Name], qryInfo.[Last Name], qryInfo.[System Name], qryInfo.Department, qryInfo.BusinessDescription, qryInfo.Manager, qryInfo.[Job Band], qryInfo.ThirdPartyCompany, qryInfo.Email, qryInfo.[Pulse ID] FROM qryInfo;

With Column Count 1 and Bound Column 1.

Not sure why its not working.

 

[boblarson]

Ah, I just caught a few things.

The combo's rowsource should be TABLE/QUERY not Field List.

The column count should be the number of columns in your rowsource query.

The bound column in this case would only be First Name which I doubt you want.

In the query you aren't going to be able to reference the combo box for all of your fields like you have been doing (I didn't notice that before). In a query you can only refer to the combo box and the bound column is what the combo's value is. So if it is on Last Name then using

Forms!frmInfo!cboInformation

will ONLY be able to pass the first name to the query for criteria in the first name field. If you want to use the same combo box for multiple data you would have to build your SQL in code (where you can refer to the column like

Forms!frmInfo.cboInformation.Column(1)

which would be the second column (because it is zero-based).


So that is why usually you use the ID field as the first column of the combo so that you can pull a record based on ID and not by other means.

 

[mumbles10]

Ah, I just caught a few things.

The combo's rowsource should be TABLE/QUERY not Field List.

The column count should be the number of columns in your rowsource query.

The bound column in this case would only be First Name which I doubt you want.

In the query you aren't going to be able to reference the combo box for all of your fields like you have been doing (I didn't notice that before). In a query you can only refer to the combo box and the bound column is what the combo's value is. So if it is on Last Name then using

Forms!frmInfo!cboInformation

will ONLY be able to pass the first name to the query for criteria in the first name field. If you want to use the same combo box for multiple data you would have to build your SQL in code (where you can refer to the column like

Forms!frmInfo.cboInformation.Column(1)

which would be the second column (because it is zero-based).


So that is why usually you use the ID field as the first column of the combo so that you can pull a record based on ID and not by other means.

Thanks for your help as usual Bob... rowsource is set to qryInfo and the rowsourcetype is FieldList... what the ultimate goal I have in mind is for a user to select the field to filter the query. Based on the combo selection the user would be able to see the choices in a multiselect listbox then selecting the filters and running the query.

Are you saying I can't do that? If I run the query outside of the form it works... I can type in any value from the qry and it returns the correct info when the parameter prompt pops up. I have the Forms!frmInformation!cboInformation setup on all the fields as OR statements...