How to select all Records from a query using combox

[Safeerhs]

Hi,

I have made a form to enter parameters of a query to select record. This form contain a comobox containing the ID's of all employees. When I select any ID it give the required result. I have problem that I wants to add a text in the comobox "All Employees" and when I select this it display records of all employees. This comobox is linked with a employee table and display all records from that table.

Thanks to all for your anticipated kind cooperation to resolve my this problem.

Regards
Safeer

 

[KenReay]

Hi

You do not give any details of table names etc, but in principle waht you do is as follows:

With a two column ComboBox (cboEE), with existing source of:

"SELECT EEId, EEName FROM tblEmployees ORDER BY EEName;"

Make this SQL into a union Query so:

SELECT EEId, EEName FROM tblEmployees
UNION
SELECT &quot;*&quot; AS EEId, &quot;<All>&quot; As EENAme FROM tblEmployees
ORDER BY EEName;

Now make the criteria in the query (on EEid)

Like CboEE


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Safeerhs]

Thanks for your quick kind advise.

Please find below the sql code of my query. For the parametrs of this query I use a form which contain two text boxes and one comobox. text box are for the starting & Ending date and comobox contain the names of employees which are extracted from the emloyees table. I can select one employee and can get his data but I want that if a user don't select any employee than it give data of all the employees.

I also wants that in the comobox a new text &quot;All Employees&quot; appeared by default so user can understand that now it give the data of all employees.



SELECT tblCustomers.CompanyName, tblCustomers.Address1, tblProductBrand.ProductBrandName, tblProduct.ProductModel, tblEmployees.EmployeeName, tblComplaintRecieved.[Date&Time], tblComplaintRecieved.Problem, tblComplaintRecieved.CallingPerson, tblComplaintRecieved.[AssignedDate&Time], tblComplaintRecieved.[JSDate&Time], tblComplaintRecieved.[JEDate&Time], tblComplaintRecieved.JCNote, tblComplaintRecieved.Note, tblComplaintRecieved.IsSaved, tblComplaintRecieved.ComplaintID, tblComplaintRecieved.IsAssigned, tblComplaintRecieved.IsCompleted, tblEmployees.EmployeeID
FROM tblCustomers INNER JOIN (tblProductBrand INNER JOIN (tblProduct INNER JOIN (tblEmployees INNER JOIN (tblCustomerDetail INNER JOIN tblComplaintRecieved ON tblCustomerDetail.CvsID = tblComplaintRecieved.ProductID) ON tblEmployees.EmployeeID = tblComplaintRecieved.AssignedEmployeeID) ON tblProduct.ProductID = tblCustomerDetail.ProductID) ON tblProductBrand.ProductBrandID = tblProduct.ProductBrandID) ON tblCustomers.CustomerID = tblCustomerDetail.CustomerID
WHERE (((tblComplaintRecieved.[Date&Time]) Between [Forms]![frmOneEmployeeComplaintsDetail]![txtDate] And [Forms]![frmOneEmployeeComplaintsDetail]![txtTo]) AND ((tblEmployees.EmployeeID)=[Forms]![frmOneEmployeeComplaintsDetail]![cmbEmployee]));

 

[KenReay]

Hi

Is that the SQl from your Combo box Rowsource?

If yes why, surely you are not displaying all of those columns in teh combo box?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Safeerhs]

No This is the code of my query from which i get information.

Below is the code of the comobox.


SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Designation
FROM tblEmployees
WHERE (((tblEmployees.DepartmentID)=2) AND ((tblEmployees.Saved)=Yes))
ORDER BY tblEmployees.EmployeeName;

 

[KenReay]

Hi

OK so if you make that SQL:

SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Designation
FROM tblEmployees
WHERE (((tblEmployees.DepartmentID)=2) AND ((tblEmployees.Saved)=Yes))
UNION
SELECT &quot;*&quot; As EMployeeId, &quot;<All Employees&quot; As EmployeeName, &quot; &quot; As Designation FROM tblEmployees
ORDER BY tblEmployees.EmployeeName;

and do the otehr things I said in my firts post, you will get <All Employees> option in the Combo box


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK