Apply Filter Macro in Form

[Hadi]

Hello Experts,

I have a form that is based on a query. The form is a timsheet entry form
for the employees that are working on my project. The first thing the user
is supposed to do is pick the company name of the employee from a drop down
box. Once he/she selects the company name. I have small macro (using macro
builder) that goes and finds the first employee record from that company.
The user then goes to another field call Company Code and hit the filter by
selection button to the filter employees in that company. I want to save the
user that step and have form automatically filters the by the company name.
I can set a macro after update or after change but not sure how to tell the
form to do that. do i need to write a samll SQL statement?

Help is always appreciated

 

[Wayne-I-M]

Hi Hadi

I may be missunderstanding the problem but from what you have put I don't
think you need either the macro or any code.

I "think" (?) you have a form with company details and employee details.
You want to select the company somehow (at the moment from a combo)
You then want to filter the form to show only the employees from the company.

If that's correct

You should have 2 tables
Company
Employee

The company primary field (ID) should be in the employee table somewhere and
used as the "link" for the relationship.

You can base the form on the company table - don't forget this if you want
this form may have only 2 controls like a combo to select the company with
another control bound to the ID field - you can have a sub form with all the
employee details (make sure it has the linking ID field in this form as
well). The 2 forms are linked Child and Master by the ID.

When you select a company from the combo - which you can set so the user
must do before they can do anything else - the corresponding employee details
are shown in the sub form.

I'm not sure what you mean by the "1st" employee to be shown on the form.
But eitherway you can base the subform on a query and sort this in anyway you
want to so the the 1st employee in shown - if the subform is a single form
you will only show one emplyee, in a contious or datasheet you'll ge them all

Hope this helps

 

[Wayne-I-M]

OK then another method, I left this till after you - hopefully - read the
other post as this 1st method is simpler and less likely to go wrong.

In your company select combo have a number of columns
CompanyID
CompanyName
Set the column column wdths 0cm;3cm
Bound column = 1

On the AfterUpdate event of the combo

Me.cboEmployee.RowSource = "SELECT EmployeeID, EmployeeName FROM" & "
tblEmployee WHERE CompanyID = cboCompany"
Me.cboEmployee = Me.cboEmployee.ItemData(0)

Notes - I don't know your table/field names
Change the above code
cboEmployee = a combo that show employees
EmployeeID, EmployeeName are fields in the employees table
cboCompany = is the combo holding company data
cboEmployee = is the combo holding employee data


Of course you don't to to just filter a combo using the after update - you
can filter the whole form / subform etc. This is just one example of the use
of the after update method of a combo.

 

[Hadi]

thanks Wayne. I am going with your second post but need help with sth. Do I
need to define the Macro ahead of time because after I adjusted the code you
gave me and put in the afterupdate of the company combo. I got a message
saying the database can't find the macro Me.

sorry but I am relativley new to access and dont know how to identify the
macros

thanks again