ok I will piece this all together and see how it works. I will post back once
I have it completed to let you know how it went. Thank you for your time,
instructions, and input. I greatly appreciate it. Wish me luck!
SS
:
You either append 6 and then delete the unneeded ones or use some method to
append only the required reviewers. If you have standard groups to append,
the create tables to manage this. If you look up some list box code, you
could place a multi-select list box on a form that has a row source of the
reviewers. Use code to loop through the selected items in the list box and
append them.
For a single user, you could add a yes/no field to the Reviewers table and
check it for the reviewers you want to append to the related table.
--
Duane Hookom
Microsoft Access MVP
:
But how do I only create just 4 records for those specific managers that need
to review the CA? How do I select them and append them to this related table?
:
You would not use the old check boxes. You would have only the required
reviewer records in your related table. If you needed 4 reviewers, there
would be 4 related records. When each of these records for a given CA value
have a value in the reviewdate field then it suggests every review has been
completed. That's what the DCount() does. If any of the related records have
a Null review date then not all reviews have been completed.
--
Duane Hookom
Microsoft Access MVP
:
I'm getting there.
I would still use the check boxes to trigger the email to be sent to the
mgrs, right? That would still reside in my current table as is.
Then in the afterupdate event of my subform (tblReviews) I would put the
DCount If statement like this:
IF DCount("*","tblReviews","[ReviewDate] Is Null AND [CA]=" &
Me.[CA]) = 0 Then
'send email code
End If
Exactly how does the DCount work if the result of "0" sends the email?
Wouldn't it have to be if the result equals all records for that particular
"CA"?
:
You are catching on. I expect you would have a table of reviewers like:
tblReviewers:
RvwID RvwPosition
1 CEO
2 Sales
3 Ops
4 SC
5 QA
6 Eng
You could use an append query to create up to 6 records to your related table
CARvwID primary key autonumber
RvwID relates to RvwID in tblReviewers
CA relates to your original table
RvwDate Review Date
RvwComments ...
--
Duane Hookom
Microsoft Access MVP
:
There are quite a few fields in my table so I will only show the ones relevant.
CA - PK
CEOCheck - Yes/No
SalesCheck - Yes/No
OpsCheck - Yes/No
SCCheck - Yes/No
QACheck - Yes/No
EngCheck - Yes/No
CEODate - Date/Time
SalesDate - Date/Time
OpsDate - Date/Time
SCDate - Date/Time
QADate - Date/Time
EngDate - Date/Time
Those are the fields that I use for this process. The checks are used to
send the email to only those selected. Then the date field is used to show
the mgr signed off on it.
I would create this new table and set it up as a subform on my main form
with the master/child link using the CA. I assume I would use the after
update event of the subform to trigger the DCount you wrote, right?
:
Yes, you would need a foreign key relating back to your other table. It would
be easier to suggest a structure if you would provide the structure of your
existing table. Your after update of your form would be something like:
If DCount("*","tblRelated","[ReviewDate] Is Null AND [FKField]=" &
Me.[FKField]) = 0 Then
'code to send your email
End If
--
Duane Hookom
Microsoft Access MVP
:
wouldn't I need a FK in this table to relate the records in this table with
the record in my table where the records are kept that they are signing off
on?
:
I would expect your related table would have a primary key field, reviewer
ID, and review date.
Access doesn't have triggers on tables and all user interaction should be in
forms. You would use the after update event of the form to trigger the code.
--
Duane Hookom
Microsoft Access MVP
:
Yes I think I understand the structure issue. So I would create a table with
just a PK, a date field, and a FK to my other table, correct?
Where I'm lost now is how I use the after update event of the table to query
the table. Can you explain that one better please?
:
You would probably use the after update event of the table to query the table
to see if all of the reviews have been done. If so, you can trigger the email.
Do you at least understand the issue with your structure? What happens if
you want to add another reviewer? In your system, you add fields, controls,
queries, and work. If you have a normalized table structure, you might add a
record to a table of reviewers. Then you work is done.
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane,
Not sure I understand. How will that help trigger the email when the last
manager signs off on the record?
SS
:
I would change the table structure to normalize it. Each "review" should
create its own record in a related table. If only 3 managers have to review,
there should be only 3 records in the related table.
--
Duane Hookom
Microsoft Access MVP
:
I have a form (single view) that has 6 checkboxes that are bound to my table.
These check boxes are used as email triggers. Each check box is for each mgr
that needs to get an email notice to review that particular record. The QA
mgr checks off which managers need to review that record and then fires off
the email. The email is only sent to the mgrs who have a check in their
specified checkbox. This portion works fine. I also have 6 textboxes that are
also bound. These 6 textboxes are used for the managers to enter a date when
they reviewed that particular record. Once all the managers enter a date in
their textbox then the record needs to be reviewed one last time by the QA
mgr and then he closes that particular record.
The problem I'm having is with the email notification once all the managers
review that record. Say only 3 managers need to review it (meaning only 3
checkboxes will be true for that record), once the last manager enters his
date in the textbox I want an automatic email generated to the QA mgr to
notify him that all management has reviewed it and it's ready to be closed. I
would assume the code would need to be in the afterupdate event of each of
the textboxes. The email code I have so that's not the problem. The problem
is how do I write code (IF statement?) that looks at the checks that are
"true" and then looks at the corresponding textboxes to see if there is a
date in all 3 of them and if there is then fires the email code. How can I
set this up in vba code? Since there can be any combination of these 6
checkboxes with the corresponding textboxes I really don't know how to
proceed with this. Any help would be greatly appreciated.
SS
