IF statement...I think

S

Secret Squirrel

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
 
D

Duane Hookom

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.
 
S

Secret Squirrel

Hi Duane,

Not sure I understand. How will that help trigger the email when the last
manager signs off on the record?

SS
 
D

Duane Hookom

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.
 
S

Secret Squirrel

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?
 
D

Duane Hookom

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.
 
S

Secret Squirrel

I'm following you now. But how would I set up the query to look at this table
to see if all the dates have been filled in for that specific record? I
assume I would fire this query in the after update event of the form. And
then i would use the results of the query to fire the email code?
 
S

Secret Squirrel

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?
 
D

Duane Hookom

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
 
S

Secret Squirrel

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?

Duane Hookom said:
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


Secret Squirrel said:
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?
Click to expand...
Click to expand...
 
D

Duane Hookom

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


Secret Squirrel said:
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?

Duane Hookom said:
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


Secret Squirrel said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

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"?

Duane Hookom said:
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


Secret Squirrel said:
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?

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

Oh and the append query you mentioned....I assume I would run this append
query to create the records in the "tblReviews" every time I create a new
"CA" record in my "tblCorrectiveActions", right?

Duane Hookom said:
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


Secret Squirrel said:
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?

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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


Secret Squirrel said:
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"?

Duane Hookom said:
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


Secret Squirrel said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

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?



Duane Hookom said:
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


Secret Squirrel said:
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"?

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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


Secret Squirrel said:
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?



Duane Hookom said:
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


Secret Squirrel said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

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

Duane Hookom said:
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


Secret Squirrel said:
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?



Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Good luck...

--
Duane Hookom
Microsoft Access MVP


Secret Squirrel said:
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

Duane Hookom said:
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


Secret Squirrel said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

Hi Duane,

I was able to set everything up the way we discussed yesterday. It works
like a charm. Thank you again for all your help!

SS

Duane Hookom said:
Good luck...

--
Duane Hookom
Microsoft Access MVP


Secret Squirrel said:
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

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Glad to be of assistance.

--
Duane Hookom
Microsoft Access MVP


Secret Squirrel said:
Hi Duane,

I was able to set everything up the way we discussed yesterday. It works
like a charm. Thank you again for all your help!

SS

Duane Hookom said:
Good luck...

--
Duane Hookom
Microsoft Access MVP


Secret Squirrel said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Naming of Form Button 3
Count if Text is Equal in two Fields 2
Query to calculate percent, but not greater that 100% 6
Optionbutton Not working 3
show records when two fields not equal 4
Copy previous record into form field 0
Save New Record from Form 2
How to use/create code in MS Word to calculate age? 0

Top