J
Joe Leon
The following is a session with Ken Snell where he helped me with a prior
posting.
The posting was titled:
"Combo Box text display problem based on AfterUdate...
Now that it is all working, the problem I have is that I would like for the
drop down lists to display in alphabetical order (sorted). I have not been
able to figure out how to do this.
Please help... THANKS, Joe...
---------------------------------------
I have three tables defined as follows:
tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text
tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text
tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text
I have 3 combo boxes, cboStore, cboManager, and cboEmployee
The AfterUpdate for cboStore is as follows and and the store picked is used
to filter the managers name that will display in cboManager
Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String
sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub
The AfterUpdate for cboManager is as follows and the manager name picked is
used to filter the employee name that will display in cboEmployee
Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String
sEmployeeSource = "SELECT
[tblEmployee].[lngEmployeeID],[tblEmployee].[lngManagerID],
[tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery
End Sub
The cboStore displays the strStoreName (Text), the cboManger displays the
strManagerName (Text) based on the store picked, but the cboEmployee displays
the lngMangaerID (Number) instead of the strEmployeeName (Text)…. What’s
wrong??? Thanks….Joe…
Ken Snell (MVP)
7/8/2006 6:39 PM PST
Sounds like the column widths property is not properly set. It should be
this:
0";0";1"
Or use whatever width for the third colum that you want. Also be sure that
the column count property is set to 3.
--
Ken Snell
<MS ACCESS MVP>
Joe Leon
7/10/2006 6:05 AM PST
Here's a follow on question.... Now that I have this working I tried placing
the StoreName, ManagerName, and EmployeeName in a table after the combo box
selction. But what I am storing is not the text but the ID. What am I doing
wrong?
Thanks...Joe...
Ken Snell (MVP)
7/10/2006 7:00 AM PST
You should be storing the ID, not the name. The bound column of the combo
box is the column whose value is actually the value of the combo box. In a
relational database, you store the related field value, and then you use a
query to get the actual name when you want it for another form or report.
This means that you can update a store name in the store table, and not need
to update it in all the other records where you'd stored a name instead of
the StoreID.
So your setup is working correctly.
--
Ken Snell
<MS ACCESS MVP>
Ken Snell (MVP)
7/10/2006 11:10 AM PST
The query would be something like this:
SELECT S.StoreName, M.ManagerName, E.EmployeeName
FROM ((YourTableName AS Y INNER JOIN StoreTable AS S
ON Y.Store.=S.StoreID) INNER JOIN ManagerTable AS M
ON Y.Manager = M.ManagerID) INNER JOIN
EmployeeTable AS E ON Y.Employee=E.EmployeeID;
--
Ken Snell
<MS ACCESS MVP>
Ken Snell (MVP)
7/10/2006 8:34 PM PST
The S, E, M, and Y are table aliases... shorthand references rather than
writing the entire table name each time.
You need to change the table structure for tblPick. The Store, Manager, and
Employee fields all need to be Number (long integer) data types, not text.
You need to make these changes so that the query will work.
OK, the query would be something like this:
SELECT S.strStoreName, M.strManagerName, E.strEmployeeName
FROM ((tblPick AS Y
INNER JOIN tblStore AS S
ON Y.Store.=S.lngStoreID )
INNER JOIN tblManager AS M
ON Y.Manager = M.lngManagerID)
INNER JOIN
tblEmployee AS E ON Y.Employee=E.lngEmployeeID;
--
Ken Snell
<MS ACCESS MVP>
Joe Leon
7/25/2006 11:56 AM PST
It's been a while.... just got back from a trip and started working on this
again...
Here is the problem I have. All seems to work ok except that when I write
the record go to the next record and come back to the first one, the list
boxes are not displaying the data. I know the data is there because the query
shows it. What am I doing wrong?
Ken Snell (MVP)
7/25/2006 8:47 PM PST
You probably need to requery the listbox in the form's Current event
procedure:
Private Sub Form_Current()
Me.NameofListBox.Requery
End Sub
I don't recall the exact setup on your form, so this advice may be a bit off
the mark. Let me know if it is.
posting.
The posting was titled:
"Combo Box text display problem based on AfterUdate...
Now that it is all working, the problem I have is that I would like for the
drop down lists to display in alphabetical order (sorted). I have not been
able to figure out how to do this.
Please help... THANKS, Joe...
---------------------------------------
I have three tables defined as follows:
tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text
tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text
tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text
I have 3 combo boxes, cboStore, cboManager, and cboEmployee
The AfterUpdate for cboStore is as follows and and the store picked is used
to filter the managers name that will display in cboManager
Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String
sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub
The AfterUpdate for cboManager is as follows and the manager name picked is
used to filter the employee name that will display in cboEmployee
Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String
sEmployeeSource = "SELECT
[tblEmployee].[lngEmployeeID],[tblEmployee].[lngManagerID],
[tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery
End Sub
The cboStore displays the strStoreName (Text), the cboManger displays the
strManagerName (Text) based on the store picked, but the cboEmployee displays
the lngMangaerID (Number) instead of the strEmployeeName (Text)…. What’s
wrong??? Thanks….Joe…
Ken Snell (MVP)
7/8/2006 6:39 PM PST
Sounds like the column widths property is not properly set. It should be
this:
0";0";1"
Or use whatever width for the third colum that you want. Also be sure that
the column count property is set to 3.
--
Ken Snell
<MS ACCESS MVP>
Joe Leon
7/10/2006 6:05 AM PST
Here's a follow on question.... Now that I have this working I tried placing
the StoreName, ManagerName, and EmployeeName in a table after the combo box
selction. But what I am storing is not the text but the ID. What am I doing
wrong?
Thanks...Joe...
Ken Snell (MVP)
7/10/2006 7:00 AM PST
You should be storing the ID, not the name. The bound column of the combo
box is the column whose value is actually the value of the combo box. In a
relational database, you store the related field value, and then you use a
query to get the actual name when you want it for another form or report.
This means that you can update a store name in the store table, and not need
to update it in all the other records where you'd stored a name instead of
the StoreID.
So your setup is working correctly.
--
Ken Snell
<MS ACCESS MVP>
Ken Snell (MVP)
7/10/2006 11:10 AM PST
The query would be something like this:
SELECT S.StoreName, M.ManagerName, E.EmployeeName
FROM ((YourTableName AS Y INNER JOIN StoreTable AS S
ON Y.Store.=S.StoreID) INNER JOIN ManagerTable AS M
ON Y.Manager = M.ManagerID) INNER JOIN
EmployeeTable AS E ON Y.Employee=E.EmployeeID;
--
Ken Snell
<MS ACCESS MVP>
Ken Snell (MVP)
7/10/2006 8:34 PM PST
The S, E, M, and Y are table aliases... shorthand references rather than
writing the entire table name each time.
You need to change the table structure for tblPick. The Store, Manager, and
Employee fields all need to be Number (long integer) data types, not text.
You need to make these changes so that the query will work.
OK, the query would be something like this:
SELECT S.strStoreName, M.strManagerName, E.strEmployeeName
FROM ((tblPick AS Y
INNER JOIN tblStore AS S
ON Y.Store.=S.lngStoreID )
INNER JOIN tblManager AS M
ON Y.Manager = M.lngManagerID)
INNER JOIN
tblEmployee AS E ON Y.Employee=E.lngEmployeeID;
--
Ken Snell
<MS ACCESS MVP>
Joe Leon
7/25/2006 11:56 AM PST
It's been a while.... just got back from a trip and started working on this
again...
Here is the problem I have. All seems to work ok except that when I write
the record go to the next record and come back to the first one, the list
boxes are not displaying the data. I know the data is there because the query
shows it. What am I doing wrong?
Ken Snell (MVP)
7/25/2006 8:47 PM PST
You probably need to requery the listbox in the form's Current event
procedure:
Private Sub Form_Current()
Me.NameofListBox.Requery
End Sub
I don't recall the exact setup on your form, so this advice may be a bit off
the mark. Let me know if it is.