Populate a listbox from a Combobox

J

JOM

I have a combobox whose rowsource as follows:
SELECT [tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames FROM
[tblEmp] select -1,'[ALL Records]' from [tblEmp];

when I select an employees name, it should be able to populate the listbox
with work dates conververted to months. The rowsource is as follows:

SELECT DISTINCT Format$([workDate],"mmm yyyy",0,0) AS Months,
[tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames,
Format([workDate],"m yyyy") AS GrDates
FROM [tblEmp] INNER JOIN [tblWork] ON [tblEmp].EmpID = [tblWork].EmpID
GROUP BY Format$([workDate],"mmm yyyy",0,0), [tblWork].EmpID, [tblEmp]!LName
& " " & [tblEmp]!FName, Format([workDate],"m yyyy")
HAVING ((([tblWork].EmpID)=[Forms]![frmWork]![cmbWork]))
ORDER BY [tblWork].EmpID, Format([workDate],"m yyyy") DESC;


I would like when I select [ALL Records] it populates the listbox with the
months all the wmployees worked....

Please help!
 
G

Graham Mandeno

A few points:

1) Your combo rowsource does not make sense unless you insert the word
"UNION" before the second "SELECT". I guess that was just a typo :)

2) Your listbox rowsource does not need to be a GROUP BY query because you
are not using any aggregare function (Sum, Count, Max, etc). The DISTINCT
predicate is enough to ensure that you don't have duplicate records.

3) Your ORDER BY clause is probably not working as you would wish. It will
group all the Januarys for every year together, then all the Octobers, Nov,
Dec, then Feb, and so on.

I suggest you do it this way:

Create a saved query "qryEmpWorkByMonth" as follows:

SELECT DateSerial( Year([workDate]), Month([workDate]), 1) AS FirstOfMonth,
[tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames
FROM [tblEmp] INNER JOIN [tblWork] ON [tblEmp].EmpID = [tblWork].EmpID;

The "FirstOfMonth" field will be the first day of the month of [workDate].

Now, in the AfterUpdate event of your combo box, put this code:

Dim sRowSource as String
sRowSource = "Select DISTINCT Format(FirstOfMonth, "mmm yyyy") as Months, "
_
& ", EmpID, ENames FROM qryEmpWorkByMonth "
If cmbWork <> -1 then
sRowSource = sRowSource & "WHERE EmpID=" & cmbWork
End If
sRowSource = sRowSource & "ORDER BY FirstOfMonth DESC"
If cmbWork <> -1 then
sRowSource = sRowSource & ", ENames"
End If
[YourListboxName].RowSource = sRowSource
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

GROUP BY Format$([workDate],"mmm yyyy",0,0), [tblWork].EmpID,
[tblEmp]!LName
& " " & [tblEmp]!FName, Format([workDate],"m yyyy")
HAVING ((([tblWork].EmpID)=[Forms]![frmWork]![cmbWork]))
ORDER BY [tblWork].EmpID, Format([workDate],"m yyyy") DESC;
Click to expand...

JOM said:
I have a combobox whose rowsource as follows:
SELECT [tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames
FROM
[tblEmp] select -1,'[ALL Records]' from [tblEmp];

when I select an employees name, it should be able to populate the listbox
with work dates conververted to months. The rowsource is as follows:

SELECT DISTINCT Format$([workDate],"mmm yyyy",0,0) AS Months,
[tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames,
Format([workDate],"m yyyy") AS GrDates
FROM [tblEmp] INNER JOIN [tblWork] ON [tblEmp].EmpID = [tblWork].EmpID
GROUP BY Format$([workDate],"mmm yyyy",0,0), [tblWork].EmpID,
[tblEmp]!LName
& " " & [tblEmp]!FName, Format([workDate],"m yyyy")
HAVING ((([tblWork].EmpID)=[Forms]![frmWork]![cmbWork]))
ORDER BY [tblWork].EmpID, Format([workDate],"m yyyy") DESC;


I would like when I select [ALL Records] it populates the listbox with the
months all the wmployees worked....

Please help!
Click to expand...
 
K

Ken Sheridan

I'd suggest adding an ORDER BY clause to the combo box's RowSource property
so that the [All Records] item comes first, and the names are also listed
alphabetically by Last Name, First Name:

SELECT
EmpID,
LName & " " & FName AS Enames
FROM tblEmp
UNION
SELECT
-1,
'[ALL Records]'
FROM tblEmp
ORDER BY ENames;

For the list box's RowSource property the following should work:

SELECT DISTINCT
tblEmp.EmpID,
Format(WorkDate,"mmm yyyy) AS Months,
LName & " " & FName AS Enames,
Format(WorkDate,"yyyymm") AS DateSortColumn
FROM tblEmp, tblWork
WHERE
tblEmp.EmpID = tblWork.EmpID
AND (tblWork.EmpID=Forms!frmWork!cmbWork
OR Forms!frmWork!cmbWork = -1)
ORDER BY
LName & " " & FName,
Format(WorkDate,"yyyymm") DESC;

Sorting by the names seems more logical than the ,presumably arbitrary,
EmpID values.

In the combo box's AfterUpdate event procedure requery the list box.
 
J

JOM

Thanks Ken, I tried the syntax it works but not as I wanted, you see when I
select [All Records], what I would like is the result to be is

2005 Jan
2005 Feb
2005 March
--------------------------
What am geting is
EmpID Months
1 2005 Jan
1 2005 Feb
1 2005 March
2 2005 Jan
2 2005 Feb
2 2005 Mar
-------------------------------------------------------




Ken Sheridan said:
I'd suggest adding an ORDER BY clause to the combo box's RowSource property
so that the [All Records] item comes first, and the names are also listed
alphabetically by Last Name, First Name:

SELECT
EmpID,
LName & " " & FName AS Enames
FROM tblEmp
UNION
SELECT
-1,
'[ALL Records]'
FROM tblEmp
ORDER BY ENames;

For the list box's RowSource property the following should work:

SELECT DISTINCT
tblEmp.EmpID,
Format(WorkDate,"mmm yyyy) AS Months,
LName & " " & FName AS Enames,
Format(WorkDate,"yyyymm") AS DateSortColumn
FROM tblEmp, tblWork
WHERE
tblEmp.EmpID = tblWork.EmpID
AND (tblWork.EmpID=Forms!frmWork!cmbWork
OR Forms!frmWork!cmbWork = -1)
ORDER BY
LName & " " & FName,
Format(WorkDate,"yyyymm") DESC;

Sorting by the names seems more logical than the ,presumably arbitrary,
EmpID values.

In the combo box's AfterUpdate event procedure requery the list box.

JOM said:
I have a combobox whose rowsource as follows:
SELECT [tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames FROM
[tblEmp] select -1,'[ALL Records]' from [tblEmp];

when I select an employees name, it should be able to populate the listbox
with work dates conververted to months. The rowsource is as follows:

SELECT DISTINCT Format$([workDate],"mmm yyyy",0,0) AS Months,
[tblWork].EmpID, [tblEmp]!LName & " " & [tblEmp]!FName AS Enames,
Format([workDate],"m yyyy") AS GrDates
FROM [tblEmp] INNER JOIN [tblWork] ON [tblEmp].EmpID = [tblWork].EmpID
GROUP BY Format$([workDate],"mmm yyyy",0,0), [tblWork].EmpID, [tblEmp]!LName
& " " & [tblEmp]!FName, Format([workDate],"m yyyy")
HAVING ((([tblWork].EmpID)=[Forms]![frmWork]![cmbWork]))
ORDER BY [tblWork].EmpID, Format([workDate],"m yyyy") DESC;


I would like when I select [ALL Records] it populates the listbox with the
months all the wmployees worked....

Please help!
Click to expand...
Click to expand...
 
K

Ken Sheridan

If you take out the references to the employee columns from the SELECT and
ORDER BY clauses it should give you just the year/month. You'll need to keep
the computed DateSortColumn in the SELECT clause so that you can refer to the
same expression in the ORDER BY clause to sort the result set by Year/Month.
You can hide this column in the list box by setting the control's
ColumnWidths property to something like 8cm;0cm (or rough equivalent in
inches).

Your example shows the sort ascending so you can remove the DESC, which I'd
included because your original query did so:

SELECT DISTINCT
Format(WorkDate,"mmm yyyy) AS Months,
Format(WorkDate,"yyyymm") AS DateSortColumn
FROM tblEmp, tblWork
WHERE
tblEmp.EmpID = tblWork.EmpID
AND (tblWork.EmpID=Forms!frmWork!cmbWork
OR Forms!frmWork!cmbWork = -1)
ORDER BY
Format(WorkDate,"yyyymm");
 
J

JOM

Thanks alot that worked

Ken Sheridan said:
If you take out the references to the employee columns from the SELECT and
ORDER BY clauses it should give you just the year/month. You'll need to keep
the computed DateSortColumn in the SELECT clause so that you can refer to the
same expression in the ORDER BY clause to sort the result set by Year/Month.
You can hide this column in the list box by setting the control's
ColumnWidths property to something like 8cm;0cm (or rough equivalent in
inches).

Your example shows the sort ascending so you can remove the DESC, which I'd
included because your original query did so:

SELECT DISTINCT
Format(WorkDate,"mmm yyyy) AS Months,
Format(WorkDate,"yyyymm") AS DateSortColumn
FROM tblEmp, tblWork
WHERE
tblEmp.EmpID = tblWork.EmpID
AND (tblWork.EmpID=Forms!frmWork!cmbWork
OR Forms!frmWork!cmbWork = -1)
ORDER BY
Format(WorkDate,"yyyymm");


JOM said:
Thanks Ken, I tried the syntax it works but not as I wanted, you see when I
select [All Records], what I would like is the result to be is

2005 Jan
2005 Feb
2005 March
--------------------------
What am geting is
EmpID Months
1 2005 Jan
1 2005 Feb
1 2005 March
2 2005 Jan
2 2005 Feb
2 2005 Mar
Click to expand...
Click to expand...
 
Top