How do I copy a query for each record in a table

[Guest]

Hello, I am running a query to determine what clients my employees have. In
the employees form I would like to have a command button that says "clients"
so I can preview what clients that employee has. But how do I make a command
so when I go to another employee it automatically runs thier client list
info? Do I have to create the same query for each employee?

 

[Allen Browne]

Presumably you have 3 tables:
1. Employee table
2. Client table
3. An Assign or Job table (depending on whether you assign employees
generally to care for clients, or assign employees to work on specific jobs
for a client.)

The 3rd table will have the EmployeeID field (which employee is assigned)
and ClientID field (which client they are assigned to.)

With that structure, you can add a subform to your employee form. The
subform will be bound to the 3rd table, and will show the clients they are
assiged to.

If you assign for specific jobs, the Job table will have a yes/no field to
mark the job Completed. If you want the subform to show only the active jobs
the employee is assigned to, you would create a query into table 3, with
False as the Criteria under this field. Then use this query as the source
for the subform.

 

[strive4peace]

Hi Erik,

I am assuming you have the following tables:

*Clients*
ClientID, autonumber
Client, text
etc

*Employees*
EmployeeID
Firstname, text
Lastname, text
etc

*EmployeeClients*
EmployeeID, long integer
ClientID, long integer


rather than running a query for each employee...

make a form (frmEmployeeClients) to show clients for
employees and put the EmployeeID on the form (it can be
hidden).

RecordSource --> EmployeeClients

on the form for Employees, make a subform
sourceObject --> frmEmployeeClients
LinkMasterFields --> EmployeeID
LinkchildFields --> EmployeeID

Optionally, you can make a listbox to show this information,
but you will need to requery it on the form OnCurrent event.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day