Find Employee

[tjnichols]

Good afternoon. I would like to create a dropdown to select an employee. When I do this (I have a main form and a subform), it won't change the data in either the main form or the subform.

Granted, I am most definitely doing this wrong. (Obviously! Or I wouldn't be here right?) The subform and the main form are linked by the EMPID field.

Any help would be most appreciated!

Thanks!

 

[sparks80]

Hi,

It would help if you could give some more information about your underlying data.

What is the record source for your main form and the subform. What are the fields that you want to display a) on your main form, and b) on the subform?

 

[tjnichols]

The main form is called Employees Extended and the subform is called Hours.

The main form has the following fields:
EmpID
Employee Name (This is a combination between the First and Last Name fields in the employee table) this is what I would like to have a dropdown on to select the employee.
ReportsTo
HireDate
TerminationDate (note - if there is a value here, the record will not show in the Employees Extended form).

Sub form is called Hours. The following is a list of those fields:

EmpID
DateWorked
WorkCodeID
Hours
WorkDescription
WeekEnding (Calculated field)
PeriodEnding (Caluclated field).

I want to display all of the fields above. Any more questions?

An idea? Can we have a form that allows a user to enter information, from there the user clicks on a button that takes them to the form to enter data with the filters applied?

 

[sparks80]

Hi,

There are lots of ways of displaying the data, but here are 2 examples:

The first (and simplest) one is to scrap the combobox, and have the main form and subform linked by your employee ID (EmpID). This way the subform should be automatically populated without the need for any programming.

The second is to have an unbound main form.
Set the Row Source for the combobox (drop-down box) to a query with fields EmpID and EmployeeName. Set the combobox name to "cboEmployeeName".

Next set the record source for the subform as follows:

SELECT * 
FROM tblEmployees
WHERE EmpID = " & cboEmployeeName

Now on the AfterUpdate event of the combobox on the main form enter this code:

Private Sub AfterUpdate()
    Me!SubFormName.Requery
End Sub

You could create two subforms, one to display the employee details, and one to display the hours.

 

[tjnichols]

I don't think I have been clear. I want to apply a filter based on the employee I select (hopefully a drop down). I will also need to filter based on the PeriodEnding so that only the current period end date shows for the hours that are being entered. Will the code sparks80 do this?

Thank you!!!

 

[sparks80]

Hi,

The example I gave will allow you to select an employee, and then return the results on the Hours subform that relate to that employee. It would be easy to further filter the results on the subform using a date.

If you have a bound main form with results for one employee, and then display the results for another employee on the subform it will get very confusing. This is why I have suggested using an unbound main form.

The simplest thing to do would be to post a copy of the database containing the relevant tables and some sample data (obviously having removed anything confidential).

 

[tjnichols]

Alright - here you go. Thanks again!!!

 

[sparks80]

Hi,

Sorry, I forgot to say it needs to be in 2007 format - sadly I don't have Access 2010 yet

 

[tjnichols]

I have been working with it in 2007. I downloaded the template from the Microsoft website. Can't you open it? I don't have 2010 either!

 

[sparks80]

Hi,

My download corrupted - I have got it working.

I've added a new form called frmSelectEmployee. Have a look and let me know if anything doesn't make sense!

 

[tjnichols]

This is great! Thank you! So can we set the match on the name instead of the EmpID? I think it will be harder with my idea. Yours is better.

Thanks again!!!!

 

[sparks80]

Hi,

You can match using the Employee Name, but there is no need. By using text rather than a numerical index you will make the matching process slower.

You're welcome for the help