queries in subforms

[lcalaway]

Good morning
I have created a main form with two embedded subforms. Each of these has a
separate query. The main form contains the employee name, the first subform
contains accumulated hours available, and the second subform contains the
hours used. The forms relate to yearly data by employee.

If I put the parameter [Enter CY] in the subform queries, every time the
form moves to a new employee, I must re-enter the year...twice. Therefore,
for each year I have created separate forms and hardcoded the year in the
associated query. This cannot be correct.

What is the best organization of this type of scheme to enable the use of
one set of forms and one set of queries?
Thank you.
LCalaway

 

[tina]

to filter each employee's subform records by a specific year, you could add
an unbound textbox control to the main form. in the control's AfterUpdate
event, add code to requery each subform.

instead of using a parameter in the subform queries, use a reference to the
textbox on the main form, as

[Forms]![MainFormName]![UnboundTextboxName]

hth

 

[John Vinson]

Good morning
I have created a main form with two embedded subforms. Each of these has a
separate query. The main form contains the employee name, the first subform
contains accumulated hours available, and the second subform contains the
hours used. The forms relate to yearly data by employee.

If I put the parameter [Enter CY] in the subform queries, every time the
form moves to a new employee, I must re-enter the year...twice. Therefore,
for each year I have created separate forms and hardcoded the year in the
associated query. This cannot be correct.

What is the best organization of this type of scheme to enable the use of
one set of forms and one set of queries?
Thank you.
LCalaway

In addition to Tina's suggestion, you might want to use the subforms'
Master/Child Link Fields. These properties can contain multiple
fields. If you have an unbound year textbox txtYear on the mainform
(defaulting to Year(Date()) for example). You can then use a Master
Link Field of

EmployeeID;txtYear

and a Child Link Field of

EmployeeID;CY


John W. Vinson[MVP]