Auto Updating Data

[Guest]

I have a field where a date of completion for a project is entered. I want
the system to automatically switch the field where open/completion is
indicated to read complete when the date is entered. In addition I have a
field where our person tracking the data indicates if a monthly update is
required. Once the issue is resolved (that is what we are tracking) then
monthly updates are not needed and I was hoping that by entering in the issue
resolved date that two things could happen. First the field indicating open
or complete would switch to complete automatically and second the field
indicating a monthly update needed would be de-selected (that field is a
simple checkbox).

Is it possible to have these things occur or when the person enters the
completed date will they have to go back and manually perform these changes?

Any help would be appreciated.

Judy

 

[Rick Brandt]

I have a field where a date of completion for a project is entered.
I want the system to automatically switch the field where
open/completion is indicated to read complete when the date is
entered. In addition I have a field where our person tracking the
data indicates if a monthly update is required. Once the issue is
resolved (that is what we are tracking) then monthly updates are not
needed and I was hoping that by entering in the issue resolved date
that two things could happen. First the field indicating open or
complete would switch to complete automatically and second the field
indicating a monthly update needed would be de-selected (that field
is a simple checkbox).

Is it possible to have these things occur or when the person enters
the completed date will they have to go back and manually perform
these changes?

Any help would be appreciated.

Judy

You can do this in a form used to edit the data. You cannot do it at the table
level unless you use a databse engine that supports triggers (Jet does not).

 

[Duane Hookom]

You can write code in the After Update event of the completion date. You
will need to change control names to yours:

If Not IsNull(Me.txtCompletionDate) Then
Me.txtOpenComplete = "Complete"
Me.chkMonthlyUpdates = False
End If

 

[Guest]

In reference to changing control names....txtCompletionDate changes to read
how to match my field? txt[name of actual date field] or txtname of actual
date field?
Same question on the other two where the name of the field from my database
needs to be entered.

Thank you for your help. I am very close with this, but I cannot seem to
debug the problem on my own.

 

[Duane Hookom]

This is all accomplished using controls and code on a form. If you don't
have a form, you can't do this.

If you have a form, you should have text boxes, combo boxes, check boxes, or
whatever bound to your fields from your table. The names of these controls
need to be used in the code that I suggested.


--
Duane Hookom
MS Access MVP

In reference to changing control names....txtCompletionDate changes to
read
how to match my field? txt[name of actual date field] or txtname of
actual
date field?
Same question on the other two where the name of the field from my
database
needs to be entered.

Thank you for your help. I am very close with this, but I cannot seem to
debug the problem on my own.

You can write code in the After Update event of the completion date. You
will need to change control names to yours:

If Not IsNull(Me.txtCompletionDate) Then
Me.txtOpenComplete = "Complete"
Me.chkMonthlyUpdates = False
End If

 

[Jamie Collins]

You can do this in a form used to edit the data. You cannot do it at the table
level unless you use a databse engine that supports triggers (Jet does not).

You *can* do the same at the Jet database engine level using a Jet
procedure e.g.

CREATE TABLE Projects (
project_ID INTEGER NOT NULL UNIQUE,
status VARCHAR(20) NOT NULL,
completed_date DATETIME,
CHECK (NOT (completed_date IS NULL AND status = 'Completed'))
)
;
CREATE PROCEDURE SetProjectCompleted
(arg_project_ID INTEGER,
arg_date DATETIME = NOW())
AS
UPDATE Projects
SET completed_date = arg_date,
status = IIF(arg_date IS NULL,
IIF(status = 'Completed', 'Ongoing', status)
, 'Completed')
WHERE project_ID = arg_project_ID
;

Revoke UPDATE permissions on the table and grant them to the proc. This
way, you can ensure the completed_date column is only ever changed via
the proc.

Jamie.

--

 

[Rick Brandt]

You *can* do the same at the Jet database engine level using a Jet
procedure e.g.

The question was "Can I make this happen automatically when I enter data
directly into a table?"

My response (essentially) was "No, but it's possible if you use a form to do
the edit".

Your response was (essentially) "No, but it's possible if you use an SP
instead of the table to do the edit."

I fail to see how this translates to "You *can* do the same at the Jet
database engine level..." since Jet is used to do the edit in all cases.

If you are talking about the fact that your solution can be "enforced" by
using User Level Security to restrict editing directly in the table then I
would point out that a form could be bound to a SQL Statement that uses
Run-With-Owner's-Permissions and User Level Security could then also be used
to restrict editing directly in the table.

Both solutions work, but yours is no more "engine level" than mine if you
add on the (extra) requirement of applying ULS.

 

[Guest]

I have a form and I got the formula to run almost...problem is listing the
combo box result that I want. In your formula "Complete" is part of a combo
box. I referenced the field, but not the table the field has been linked to
as part of the combo box. Please be patient with my programming
ignorance...my new question is after "Me." how do I input the table that the
"Complete" would be pulled from? In addition do I reference the ID Number
for "Complete" or the word itself in the formula.

Thank you again for your help.

This is all accomplished using controls and code on a form. If you don't
have a form, you can't do this.

If you have a form, you should have text boxes, combo boxes, check boxes, or
whatever bound to your fields from your table. The names of these controls
need to be used in the code that I suggested.


--
Duane Hookom
MS Access MVP

In reference to changing control names....txtCompletionDate changes to
read
how to match my field? txt[name of actual date field] or txtname of
actual
date field?
Same question on the other two where the name of the field from my
database
needs to be entered.

Thank you for your help. I am very close with this, but I cannot seem to
debug the problem on my own.

You can write code in the After Update event of the completion date. You
will need to change control names to yours:

If Not IsNull(Me.txtCompletionDate) Then
Me.txtOpenComplete = "Complete"
Me.chkMonthlyUpdates = False
End If


--
Duane Hookom
MS Access MVP

I have a field where a date of completion for a project is entered. I
want
the system to automatically switch the field where open/completion is
indicated to read complete when the date is entered. In addition I
have a
field where our person tracking the data indicates if a monthly update
is
required. Once the issue is resolved (that is what we are tracking)
then
monthly updates are not needed and I was hoping that by entering in the
issue
resolved date that two things could happen. First the field indicating
open
or complete would switch to complete automatically and second the field
indicating a monthly update needed would be de-selected (that field is
a
simple checkbox).

Is it possible to have these things occur or when the person enters the
completed date will they have to go back and manually perform these
changes?

Any help would be appreciated.

Judy

 

[Jamie Collins]

My response (essentially) was "No, but it's possible if you use a form to do
the edit".

Your response was (essentially) "No, but it's possible if you use an SP
instead of the table to do the edit."

Both solutions work, but yours is no more "engine level" than mine if you
add on the (extra) requirement of applying ULS.

Yep, both are comparable but mine is 'closer' to the database engine
because, unlike a Jet proc, an Access form is not available to all
connections to the Jet database.

Your approach IMO is too restrictive: you would decree that one and
only one front end application can modify the database. Mine
facilitates any application updating the database in a controlled way.

Hey, your form could use my proc and everyone would be happy <g>!

Jamie.

--

 

[Rick Brandt]

Yep, both are comparable but mine is 'closer' to the database engine
because, unlike a Jet proc, an Access form is not available to all
connections to the Jet database.

Fair enough. I actually can't get either of your DDL statements to run in a
test file. Is there something special one has to do?

Your approach IMO is too restrictive: you would decree that one and
only one front end application can modify the database. Mine
facilitates any application updating the database in a controlled way.

Hey, your form could use my proc and everyone would be happy <g>!

No, there could actually be any number of forms in any number of front ends
as long as they all did the appropriate update. Also any number of update
queries could be used providing they also followed the desired rules. The
constraint could be used which would make the required update rule obvious
to any developer that might come along.

If ULS eliminates the possibility of editing the table directly then all
developers would have to be pre-aware that the SP even exists to be able to
utilize it. There is nothoing about the table definition that would lead
them to use the SP. A true server-based engine with a trigger would at
least be self-documenting and even if another developer writing to the table
didn't know about it, the trigger would still do its job.

 

[Duane Hookom]

Can you provide your table structures(s) and which tables/fields are
available in your form's record source?

--
Duane Hookom
MS Access MVP

I have a form and I got the formula to run almost...problem is listing the
combo box result that I want. In your formula "Complete" is part of a
combo
box. I referenced the field, but not the table the field has been linked
to
as part of the combo box. Please be patient with my programming
ignorance...my new question is after "Me." how do I input the table that
the
"Complete" would be pulled from? In addition do I reference the ID Number
for "Complete" or the word itself in the formula.

Thank you again for your help.

This is all accomplished using controls and code on a form. If you don't
have a form, you can't do this.

If you have a form, you should have text boxes, combo boxes, check boxes,
or
whatever bound to your fields from your table. The names of these
controls
need to be used in the code that I suggested.


--
Duane Hookom
MS Access MVP

In reference to changing control names....txtCompletionDate changes to
read
how to match my field? txt[name of actual date field] or txtname of
actual
date field?
Same question on the other two where the name of the field from my
database
needs to be entered.

Thank you for your help. I am very close with this, but I cannot seem
to
debug the problem on my own.

:

You can write code in the After Update event of the completion date.
You
will need to change control names to yours:

If Not IsNull(Me.txtCompletionDate) Then
Me.txtOpenComplete = "Complete"
Me.chkMonthlyUpdates = False
End If


--
Duane Hookom
MS Access MVP

I have a field where a date of completion for a project is entered.
I
want
the system to automatically switch the field where open/completion
is
indicated to read complete when the date is entered. In addition I
have a
field where our person tracking the data indicates if a monthly
update
is
required. Once the issue is resolved (that is what we are tracking)
then
monthly updates are not needed and I was hoping that by entering in
the
issue
resolved date that two things could happen. First the field
indicating
open
or complete would switch to complete automatically and second the
field
indicating a monthly update needed would be de-selected (that field
is
a
simple checkbox).

Is it possible to have these things occur or when the person enters
the
completed date will they have to go back and manually perform these
changes?

Any help would be appreciated.

Judy

 

[Jamie Collins]

there could actually be any number of forms in any number of front ends
as long as they all did the appropriate update.

For the second time this morning I use the phrase "maintenance
disaster". How is that better than any number of applications all
invoking the same routine?

any number of update
queries could be used providing they also followed the desired rules.

That is constraint fallacy number three:

"If anyone does write new application code against this database, he or
she will always get the constraints correct and maintain the data
integrity... Each of our perfect programmers will always and forever
enforce all of the constraints in their application code. And when the
specs change, the perfect programmer will be able to find the altered
constraints in tens of thousands of lines of code and fix them -
perfectly."

http://www.dbazine.com/ofinterest/oi-articles/celko25/

The
constraint could be used which would make the required update rule obvious
to any developer that might come along.

If ULS eliminates the possibility of editing the table directly then all
developers would have to be pre-aware that the SP even exists to be able to
utilize it.

A true server-based engine with a trigger would at
least be self-documenting

I find it hard to credit the notion that a query/proc is the least
'visible' of all those options.

When I consider users across an enterprise accessing a database I think
of the tool of choice being Excel. I think such a user will find a
query/proc long before they discover a validation rule/CHECK constraint
or trigger e.g. a phone call to the DBA/owner would glean the answer,
"Simply invoke SetProjectCompleted."

Jamie.

--

 

[Guest]

I have a few lookup tables tied to the main data table. The tables I am
trying to reference in this formula are the Main Data Table which has the
field of "CAPA Plan Status" that is listed as a Number Field. It is tied to
a lookup table called "Plan Status Table" that has two columns, an ID column
and the descriptive column (where my Complete would be listed). I linked the
descriptive column to the field in the Main table. It is in this area that
is on my form that I am wishing to do the modification. The form itself is
based directly on the Main Data Table. I read the help section in Access,
but am still a touch fuzzy on how to clearly reference the "Plan Status
Table" when indicating the change in the CAPA Plan Status field on the form.

Thank you again for all of your help.

Judy

Can you provide your table structures(s) and which tables/fields are
available in your form's record source?

--
Duane Hookom
MS Access MVP

I have a form and I got the formula to run almost...problem is listing the
combo box result that I want. In your formula "Complete" is part of a
combo
box. I referenced the field, but not the table the field has been linked
to
as part of the combo box. Please be patient with my programming
ignorance...my new question is after "Me." how do I input the table that
the
"Complete" would be pulled from? In addition do I reference the ID Number
for "Complete" or the word itself in the formula.

Thank you again for your help.

This is all accomplished using controls and code on a form. If you don't
have a form, you can't do this.

If you have a form, you should have text boxes, combo boxes, check boxes,
or
whatever bound to your fields from your table. The names of these
controls
need to be used in the code that I suggested.


--
Duane Hookom
MS Access MVP

In reference to changing control names....txtCompletionDate changes to
read
how to match my field? txt[name of actual date field] or txtname of
actual
date field?
Same question on the other two where the name of the field from my
database
needs to be entered.

Thank you for your help. I am very close with this, but I cannot seem
to
debug the problem on my own.

:

You can write code in the After Update event of the completion date.
You
will need to change control names to yours:

If Not IsNull(Me.txtCompletionDate) Then
Me.txtOpenComplete = "Complete"
Me.chkMonthlyUpdates = False
End If


--
Duane Hookom
MS Access MVP

I have a field where a date of completion for a project is entered.
I
want
the system to automatically switch the field where open/completion
is
indicated to read complete when the date is entered. In addition I
have a
field where our person tracking the data indicates if a monthly
update
is
required. Once the issue is resolved (that is what we are tracking)
then
monthly updates are not needed and I was hoping that by entering in
the
issue
resolved date that two things could happen. First the field
indicating
open
or complete would switch to complete automatically and second the
field
indicating a monthly update needed would be de-selected (that field
is
a
simple checkbox).

Is it possible to have these things occur or when the person enters
the
completed date will they have to go back and manually perform these
changes?

Any help would be appreciated.

Judy

 

[Duane Hookom]

Lookup tables are good. Lookup fields are bad
http://www.mvps.org/access/lookupfields.htm.

I don't know:
Name of your completion date control
Name of your combo box
Name of the Descriptive Column in Plan Status Table
Name of your check box

Try place your names into the following:

If Not IsNull(Me.txtCompletionDate) Then
Dim lngComplete as Long
lngComplete = DLookup("ID", "[Plan Status Table]",
"[DescriptionColumn]='Complete'")
Me.cboCAPAPlanStatus = lngComplete
Me.chkMonthlyUpdates = False
End If

--
Duane Hookom
MS Access MVP

I have a few lookup tables tied to the main data table. The tables I am
trying to reference in this formula are the Main Data Table which has the
field of "CAPA Plan Status" that is listed as a Number Field. It is tied
to
a lookup table called "Plan Status Table" that has two columns, an ID
column
and the descriptive column (where my Complete would be listed). I linked
the
descriptive column to the field in the Main table. It is in this area
that
is on my form that I am wishing to do the modification. The form itself
is
based directly on the Main Data Table. I read the help section in Access,
but am still a touch fuzzy on how to clearly reference the "Plan Status
Table" when indicating the change in the CAPA Plan Status field on the
form.

Thank you again for all of your help.

Judy

Can you provide your table structures(s) and which tables/fields are
available in your form's record source?

--
Duane Hookom
MS Access MVP

I have a form and I got the formula to run almost...problem is listing
the
combo box result that I want. In your formula "Complete" is part of a
combo
box. I referenced the field, but not the table the field has been
linked
to
as part of the combo box. Please be patient with my programming
ignorance...my new question is after "Me." how do I input the table
that
the
"Complete" would be pulled from? In addition do I reference the ID
Number
for "Complete" or the word itself in the formula.

Thank you again for your help.

:

This is all accomplished using controls and code on a form. If you
don't
have a form, you can't do this.

If you have a form, you should have text boxes, combo boxes, check
boxes,
or
whatever bound to your fields from your table. The names of these
controls
need to be used in the code that I suggested.


--
Duane Hookom
MS Access MVP

In reference to changing control names....txtCompletionDate changes
to
read
how to match my field? txt[name of actual date field] or txtname of
actual
date field?
Same question on the other two where the name of the field from my
database
needs to be entered.

Thank you for your help. I am very close with this, but I cannot
seem
to
debug the problem on my own.

:

You can write code in the After Update event of the completion
date.
You
will need to change control names to yours:

If Not IsNull(Me.txtCompletionDate) Then
Me.txtOpenComplete = "Complete"
Me.chkMonthlyUpdates = False
End If


--
Duane Hookom
MS Access MVP

I have a field where a date of completion for a project is
entered.
I
want
the system to automatically switch the field where
open/completion
is
indicated to read complete when the date is entered. In addition
I
have a
field where our person tracking the data indicates if a monthly
update
is
required. Once the issue is resolved (that is what we are
tracking)
then
monthly updates are not needed and I was hoping that by entering
in
the
issue
resolved date that two things could happen. First the field
indicating
open
or complete would switch to complete automatically and second the
field
indicating a monthly update needed would be de-selected (that
field
is
a
simple checkbox).

Is it possible to have these things occur or when the person
enters
the
completed date will they have to go back and manually perform
these
changes?

Any help would be appreciated.

Judy