asked on

No data from combo box

Experts,
In my form I have a combo box where the row source is based upon this query:

SELECT DISTINCT tblStore.StoreNumberID, 
tblStore.StoreNumber, "# " & [StoreNumber] & " "
 & [StoreAddressOne] AS Store,
 (Select tblEmployee.EmployeeFirstName
  FROM tblEmployee
 INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
 And tblDesignatedEmployee.EmployeeTypeID=1) AS
 DistrictManager, (Select tblEmployee.EmployeeFirstName 
 FROM tblEmployee
 INNER JOIN tblDesignatedEmployee 
 ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID And tblDesignatedEmployee.EmployeeTypeID=2) 
AS StoreManager, (Select tblEmployee.EmployeeFirstName
  FROM tblEmployee INNER JOIN tblDesignatedEmployee ON 
 tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID 
And tblDesignatedEmployee.EmployeeTypeID=3)
 AS AssistantManager, (Select tblEmployee.EmployeeFirstName
  FROM tblEmployee 
INNER JOIN tblDesignatedEmployee
  ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
 Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
 And tblDesignatedEmployee.EmployeeTypeID=5) AS AreaSupportManager
FROM tblStore;

Open in new window

In my form I have 4 text boxes that receive data from the above query as follows:
=[StoreID].[column](3)
=[StoreID].[column](6)
=[StoreID].[column](4)
=[StoreID].[column](5)
I get the data I'm looking for in the first text box but nothing in the other three?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

Shouldn't it be like this according to your four column select statement?

=[StoreID].[column](0)
=[StoreID].[column](1)
=[StoreID].[column](2)
=[StoreID].[column](3)

mbizup

You only have four columns selected, but you are referring to columns higher than that.

Rey Obrero (Capricorn1)

the query you have above will show 7 columns

StoreNumberID StoreNumber Store DistrictManager StoreManager AssistantManager AreaSupportManager

so you need to set the Column Count property of the combo box to 7

Rey Obrero (Capricorn1)

see this, look at form "frmBonusEarned" combo storeid
StoreBonusRev.mdb

Frank Freese

ASKER

you nailed it capricorn - I forgot to change the number of columns. Everything is ok. thank you