Filtering form from another and make visible/hide

[M.Garza]

Hello,

I've tried these groups before and been helped tremendously, thanks!
I have a database that I am working on right now that has me really
confused. I am being asked to develop a database that tracks projects
for our agency. This is what they want:

Form with drop down menu with each department (done)
From the form above, have another form "appear" that shows the
employees related to that department where he/she can select her name
via a click button (done with coding found on internet)
Then have another form "appear" that only inludes the tasks related to
that employee (somewhat done)
Then have another form to show any related secondary tasks that
employee is involved with.

This is what I have:

Tbls:
ProjTbl, lists projects
DeptTbl, lists departments
Dept_EmplTbl, lists employees and department
Emp_onProjTbl, lists project, employees on project and tasks assigned

Tables are related by project, department, employeeID


1. Department form with unbound txt box searches query for department
and edit button (yes/no)
2. Subform, Employees, two fields, "Employeetxt" and "Update
Task" (yes/no) linked to Department form by Department that opens when
edit button is clicked
3. Two functions:

Private Sub Form_Current()
If Me.Edit = True Then
Me.Employees.Visible = True
Else
Me.Employees.Visible = False
End If
End Sub


Private Sub Edit_AfterUpdate()
If Me.Edit = True Then
Me.Employees.Visible = True
Else
Me.Employees.Visible = False
End If
End Sub

4. Tasks form that holds all tasks assiged to each employee.
Currently, I have this form "pop-up" when the "Update Task" button in
the Employee form is clicked, then uncheck when the Update Task button
on exit of Task form.

Private Sub Update_Task_AfterUpdate()
If Me.Update_Task.Value = True Then DoCmd.OpenForm
"Tasks", , , , , acDialog
Me.Update_Task = False

End Sub

I need to have the Task form "appear" instead of "pop-up" and I also
need it to open up only with those records that are related to the
employee that clicked the "Update Task" button.

I tried doing the same for the "Employees/Task" forms that I did with
the first two forms (DepartmentForm/EmployeesSubform/Hide/Visible) but
I can't have the main form be continous forms if I am adding a
subform, so that didn't work (they want only the names of the
employees within the department they checked to show up).

I know for sure that part of my problem is, if the employee only
selects the Update Task button without referring to their name, how
would ACCESS know to "call" the Task form with only their tasks? I
don't know the answer to this. Tried doing setfocus on that field
once the Update Task fired to see if I could use that to filter but it
only went to that field after I closed the Task form.

Any help I could get will be greatly appreciated. This is a sort of a
challenge for me because there are several people that don't think I
can do it and I want to prove them totally wrong, assuming it is
possible. I've built several databases (very basic) with the help
I've been able to find on the internet, books, and personal
experience.

HELP and THANK YOU!

 

[M.Garza]

Ay caramba!  That's a lot of form shuffling.  Question: Do they just need to
see info, or are they using these forms for data entry?  And if they are
doing data entry, is it batch entry, or just one or two records?

My first instinct is to encourage you to try to approach it a little
differently by simplifying it with listboxes.  Here's what I'd suggest:

Your form has the dropdown for dept at the top.  On the afterupdate event it
fills a list box just under it, on the same form, with the employees' names.

Then when the employee clicks their name, it fills the primary task list box,
then when you click on the primary task, it sets the secondary listbox.

Is that an option?  Because that would dramatically simlify this game of
musical forms.

The only data entry that would be done is on the task menu. There,
they will give an update on where they are in their individual
task(s). So, maybe one to three updates on their task.

What you suggest sounds absolutley great! Will I be able to just see
the employees' that are related to that particular department, when I
click on the department? How do I set up the primary task list box.
It would have to show several fields of information related to the
individual. For instance, task start/end date, etc. Is this related
to a form?

Your help is totally appreciated! Thank you, thankyou, thank you!

 

[Mike Painter]

If they insist on separate forms you are struck but a far better way would
be with combo or list boxes on the same form that allows gettting to the
employee. There is no need for a button as the onclick event in the combo or
list handles this. The department combobox would fill the employee combo.
Unless there are a lot of employees with the same name I would just use one
combo sorted by name showing their department.
At this point a subform and one related to that would give you all you
need.
I think all this has examples in Northwind, but if not BUY a copy of Access
Developer for your version.

 

[M.Garza]

If they insist on separate forms you are struck but a far better way would
be with combo or list boxes on the same form that allows gettting to the
employee. There is no need for a button as the onclick event in the comboor
list handles this. The department combobox would fill the employee combo.
Unless there are a lot of employees with the same name I would just use one
combo sorted by name showing their department.
 At this point a subform and one related to that would give you all you
need.
I think all this has examples in Northwind, but if not BUY  a copy of Access
Developer for your version.





















- Show quoted text -

The only thing they want is not have to see all of the forms/subforms
until they are needed. I am going to give the list boxes a shot. If I
run into issues I'll update the post. I really appreciate your
direction on this as I REALLY want to prove that I can handle this job
and that a database is a far better option to keep track of things
than an EXCEL spreadsheet. I can also be reached via my e-mail at
(e-mail address removed)


tkelley, you can probably zip the file and send it over e-mail, no?

I am using ACCESS 2003

THANK YOU!!!

 

[M.Garza]

The only thing they want is not have to see all of the forms/subforms
until they are needed. I am going to give the list boxes a shot.  If I
run into issues I'll update the post.  I really appreciate your
direction on this as I REALLY want to prove that I can handle this job
and that a database is a far better option to keep track of things
than an EXCEL spreadsheet.  I can also be reached via my e-mail at
(e-mail address removed)

tkelley, you can probably zip the file and send it over e-mail, no?

I am using ACCESS 2003

THANK YOU!!!- Hide quoted text -

- Show quoted text -

I think I got it! I created two listboxes on an unbound form and
called different queries to each one based on the selection of the
other (Dept/Employee), on the after update of the Dept box, I
requeried the Employee box and it worked! Finally, I created a
another form that housed all the tasks, on the afterupdate of the
Employee bosx, I called the form and guess what, it worked!!

Thank you both for setting me on the right path! Guess I showed those
that didn't think I couldn't do it, with the help of my super smart
ACCESS gurus! Thanks!!!!!

 

[M.Garza]

Excellent.  Plus you've opened another avenue to explore and to continue to
learn ... all while staying disconnected.

Another thing you can do is to add code to the DoubleClick event of a listbox
to pull up a form where you can show more detail about the double-clicked
record and perform updates to it, etc.

If they insist on separate forms you are struck but a far better waywould
be with combo or list boxes on the same form that allows gettting tothe

[quoted text clipped - 111 lines]

- Show quoted text -

I think I got it! I created two listboxes on an unbound form and
called different queries to each one based on the selection of the
other (Dept/Employee), on the after update of the Dept box, I
requeried the Employee box and it worked!  Finally, I created a
another form that housed all the tasks, on the afterupdate of the
Employee bosx, I called the form and guess what, it worked!!

Thank you both for setting me on the right path!  Guess I showed those
that didn't think I couldn't do it, with the help of my super smart
ACCESS gurus!  Thanks!!!!!

Thank you!! I really do appreciate your help!