Using combo box to select records from a table

[lee12]

I have 2 table in my db. One contains the name of all employees and is called Employees and consists of 2 fields, ID (Autonumber,PK) and EmployeeName(Text).
The other contains details of holidays booked by each employee. This is called RestDays and has 4 fields, EmpName(Text), RestDate (Short Date), Cover1 (Text) and Cover2 (Text).
What I want to be able to do is choose an employee using a combo box and then have all that particular employees holidays listed for me, firstly as a table or subform if I press a particular button on the switchboard and as a report if I press another.
I have managed to do the above with a query where the name is entered into a dialog box(parameter box?) and in a subform where the employee is selected using record selectors and a text box in the main form but I cannot do it with a combo. If possible could this be done without using any VB code, just the access controls etc.
Thanks

 

[Mile-O]

Five things:

i) Change the name of ID in your table Employees to EmployeeID;

ii) In your Employees table, break the EmployeeName field into two or more parts (i.e. EmployeeForename, Employee Surname) as you can recalculate it as a full name in any query;

iii) In your table RestDays, change the field EmpName to EmployeeID, set it to a number field.

iv) Relate the EmployeeID in RestDays to the EmployeeID in Employees in a one-to-many relationship

v) On your combobox, it's RowSource should be something like this:

SELECT tblEmployees.EmployeeID, [EmployeeForename] & " " & [EmployeeSurname] AS Employee
FROM tblEmployees;

Ensure the ColumnCount proprty of the combobox is set to 2, and that the BoundColumn is set to 1. Also, for ColumnWidths, set it to be 0cm;2.5cm so the first column isn't actually visible and the user will only see the concatenated name.

Now, on the AfterUpdate() event of the combobox you can easily filter on all records in RestDays where EmployeeID in said table match the EmployeeID selected in the combobox.

 

[Mile-O]

Also, here's an example of what I mean:

 

[kedarj]

Hi Mc,
I am having the same problem. Can you help me?
regards
K