[AccessD] Add All to List

Virginia, this kicked my butt when I first started messing with it. The MS samples in Help confused me more :).

Picture it this way:

You have 2 tables, tblEmployees and tblCalls

sample data:

	tblEmployees: (EmployeeID, EmployeeName)
		1	Bob
		2 	Joe
		3 	Tim

	tblCalls: (CallID, CallDate,  PhoneTechID, Notes) (I'll eliminate other fields to avoid confusion)
	1	2/5/04	3	"test data entered by Tim"
	2	2/5/04	2	"test data entered by Joe"
	3	2/6/04	3	"test data entered by Tim"
	4	2/6/04	1	"test data entered by Bob"
	5	2/7/04	2	"test data entered by Joe"

You create a form with 3 controls on it: txtStartDate, txtEndDate and cboEmployee

The rowsource for the combobox is simply:

SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
FROM tblEmployees;
UNION SELECT "*", "All"
FROM tblEmployees;



Your boss wants a query on weekly calls in February, by employee and an overall list.
He says Feb, but will asks for January and March five minutes later, because that's how he is ;)

You create the following query:

SELECT 
     C.CallID
     C.CallDate, 
     C.Notes, 
     E.EmployeeName AS PhoneTech
FROM tblCalls AS C 
     INNER JOIN tblEmployees AS E
          ON C.PhoneTechID = tblEmployee.EmployeeID
WHERE  (C.CallDate >= [Forms]![frmReport]![txtStartDate] And C.CallDate <=[Forms]![frmReport]![txtEndDate]) AND 
       (([Forms]![frmReport]![cboEmployee]="*") Or ([Forms]![frmReport]![cboEmployee]=[EmployeeID]))=True
ORDER BY C.CallDate;

The ticket here (if you are using the QBE grid), is to create an expression / criteria which is true (see the 2nd line in the WHERE clause).

So now you open up your form, enter dates of 2/5/04 and 2/09/04, drop the combobox down and choose Tim.
 
The criteria runs and the column criteria of EmployeeID = True is satisfied, so all of Tim's calls show.
Now you select "All", the criteria cboEmployee = "*" is satisfied, so all rows are returned.


I hope this helps.
David McAfee
dmcafee at pacbell.net

> ----- Original Message ----- 
> From: "Hollis,Virginia" <HollisVJ at pgdp.usec.com>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Wednesday, March 03, 2004 4:29 PM
> Subject: RE: [AccessD] Add All to List
>
>
> > I don't know what I am doing wrong.... The all shows at the top of the
> list,
> > but All the records do not show on the subform. The combobox is still
> > showing the first record when it opens and the subform is filtered for
> that
> > record.
> >
> > What makes the All part of "ALL" in the combo box work?
> >
> > Virginia
> >
> > -----Original Message-----
> > From: William Hindman [mailto:wdhindman at bellsouth.net]
> > Sent: Wednesday, March 03, 2004 8:09 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Add All to List
> >
> >
> > Virginia
> >
> > ...use a UNION SELECT in the combo record source to add an ALL.
> >
> > ...example:
> >
> > SELECT DISTINCTROW Customers.Company, Customers.[Customer Number] FROM
> > Customers UNION SELECT "<ALL>","" FROM Customers ORDER BY
> Customers.Company;
> >
> > William Hindman "My idea of an agreeable person is a person who agrees
> with
> > me." Disraeli
> >
> > ----- Original Message ----- 
> > From: "Hollis,Virginia" <HollisVJ at pgdp.usec.com>
> > To: <accessd at databaseadvisors.com>
> > Sent: Wednesday, March 03, 2004 8:45 AM
> > Subject: [AccessD] Add All to List
> >
> >
> > > How do you add All to List? This form contains 2 combo boxes on a main
> > form.
> > > Selecting data shows data on a subform for the related combo box
> choices.
> > >
> > > I need to either have an All in the combo boxes or when the form
opens,
> I
> > > would like them to be blank.
> > >
> > >  I tried this code in the On Open of the main form, but it still opens
> to
> > > the first record with entries in both combo boxes.
> > >
> > >     With Me
> > >         .cboNCSANo = ""
> > >         .cboProcedure = ""
> > >     End With