Date changing to current

G

Guest

It’s probably very simple thing, but I can’t find it out:
I have following calculation as control source in the “Completed Date†field.
=IIf([Status]="Comp-Completed",Date(),"")
It’s updating the date when the status is changing.
The question is – how I make the date stay of the same date that it was
changed and that it will not update to today’s date.
For example: if the status changed today (04/12/07), that if I will enter to
this record tomorrow, the date will stay 04/12/07 and not 04/13/07.

Thanks for the help.
 
G

Guest

Thank you for the quick answer.
I know that I can’t store the calculation in the table, so how I can do it?


Douglas J. Steele said:
The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that it was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I will enter
to
this record tomorrow, the date will stay 04/12/07 and not 04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
 
A

Al Campagna

Anna,
That's because CompletedDate is a calculated field... not "bound" to a table field.

If you don't already have a CompletedDate field in your table, add one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Anna said:
Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I can do it?


Douglas J. Steele said:
The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that it was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I will enter
to
this record tomorrow, the date will stay 04/12/07 and not 04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I can’t place it on the Status AfterUpdate event, because I there have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it’s doesn’t work.
What am I doing wrong?


Al Campagna said:
Anna,
That's because CompletedDate is a calculated field... not "bound" to a table field.

If you don't already have a CompletedDate field in your table, add one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Anna said:
Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I can do it?


Douglas J. Steele said:
The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that it was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I will enter
to
this record tomorrow, the date will stay 04/12/07 and not 04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Include whatever your macro is doing into Al's suggested code. You can have
the Event Procedure VBA code run the macro, or put the equivalent code into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
I can't place it on the Status AfterUpdate event, because I there have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


Al Campagna said:
Anna,
That's because CompletedDate is a calculated field... not "bound" to a
table field.

If you don't already have a CompletedDate field in your table, add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Anna said:
Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I can do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I will
enter
to
this record tomorrow, the date will stay 04/12/07 and not 04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date situation ?

Thank you very much for the help and the patience.
Anna

Douglas J. Steele said:
Include whatever your macro is doing into Al's suggested code. You can have
the Event Procedure VBA code run the macro, or put the equivalent code into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
I can't place it on the Status AfterUpdate event, because I there have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


Al Campagna said:
Anna,
That's because CompletedDate is a calculated field... not "bound" to a
table field.

If you don't already have a CompletedDate field in your table, add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I can do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I will
enter
to
this record tomorrow, the date will stay 04/12/07 and not 04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
CompletedDate = Date()
DoCmd.RunMacro "mcrAppendPms"
Else
CompletedDate = Null
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date situation
?

Thank you very much for the help and the patience.
Anna

Douglas J. Steele said:
Include whatever your macro is doing into Al's suggested code. You can
have
the Event Procedure VBA code run the macro, or put the equivalent code
into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
I can't place it on the Status AfterUpdate event, because I there have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


:

Anna,
That's because CompletedDate is a calculated field... not "bound"
to a
table field.

If you don't already have a CompletedDate field in your table, add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I can
do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that
it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I
will
enter
to
this record tomorrow, the date will stay 04/12/07 and not
04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Douglas, thank you so much for your help. It’s finally working.
Now I have another small problem, maybe you can help me:
I have 2 macros: mcrAfterUpdateFrequency, mcrNextScheduledDate- both of them
are opening 2 queries that calculating Dates.

That’s what I have on the After Update of Status Field:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
Completion_Date = Date
DoCmd.RunMacro "mcrAfterUpdateFrequency"
DoCmd.RunMacro "mcrNextScheduledDate"
DoCmd.RunMacro "mcrAppendPms"
Else: Completion_Date = Null
End If
End Sub

The question is: when I am changing the Status, both of the queries opens,
but I don’t see the calculations on the Form (on the new PM that created as
the result of the Append Query). How I can make the queries to Run and not to
be Open, and that I will see the calculations on the Dates on the new PM that
was appended?
Is it making sense?

Thank you again for your help.


Douglas J. Steele said:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
CompletedDate = Date()
DoCmd.RunMacro "mcrAppendPms"
Else
CompletedDate = Null
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date situation
?

Thank you very much for the help and the patience.
Anna

Douglas J. Steele said:
Include whatever your macro is doing into Al's suggested code. You can
have
the Event Procedure VBA code run the macro, or put the equivalent code
into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I can't place it on the Status AfterUpdate event, because I there have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


:

Anna,
That's because CompletedDate is a calculated field... not "bound"
to a
table field.

If you don't already have a CompletedDate field in your table, add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I can
do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the "Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date that
it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I
will
enter
to
this record tomorrow, the date will stay 04/12/07 and not
04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Running a macro that opens a query doesn't actually do anything with the
results of the query. Realistically, the only reason you'd ever want to run
a query is because it's an Action query (eg a Make Table, Append, Update or
Delete query).

If you've got a query that's intended to return a specific value, just use
DLookup to refer to the value returned by the query.

And just a comment: you might consider moving away from calling macros, and
use VBA code instead. Access will convert the macros into VBA code for you
if you like. Select the macro in the database window and right-click. One of
the options will "Save As/Export...". Choose that, and let it save the macro
as VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
Douglas, thank you so much for your help. It's finally working.
Now I have another small problem, maybe you can help me:
I have 2 macros: mcrAfterUpdateFrequency, mcrNextScheduledDate- both of
them
are opening 2 queries that calculating Dates.

That's what I have on the After Update of Status Field:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
Completion_Date = Date
DoCmd.RunMacro "mcrAfterUpdateFrequency"
DoCmd.RunMacro "mcrNextScheduledDate"
DoCmd.RunMacro "mcrAppendPms"
Else: Completion_Date = Null
End If
End Sub

The question is: when I am changing the Status, both of the queries opens,
but I don't see the calculations on the Form (on the new PM that created
as
the result of the Append Query). How I can make the queries to Run and not
to
be Open, and that I will see the calculations on the Dates on the new PM
that
was appended?
Is it making sense?

Thank you again for your help.


Douglas J. Steele said:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
CompletedDate = Date()
DoCmd.RunMacro "mcrAppendPms"
Else
CompletedDate = Null
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date
situation
?

Thank you very much for the help and the patience.
Anna

:

Include whatever your macro is doing into Al's suggested code. You can
have
the Event Procedure VBA code run the macro, or put the equivalent code
into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I can't place it on the Status AfterUpdate event, because I there
have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


:

Anna,
That's because CompletedDate is a calculated field... not
"bound"
to a
table field.

If you don't already have a CompletedDate field in your table,
add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your
life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I
can
do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the
"Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date
that
it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I
will
enter
to
this record tomorrow, the date will stay 04/12/07 and not
04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you so much for your huge help and the patience! It is working as a
charm.
I understood where I was wrong. Have a good weekend.

Anna



Douglas J. Steele said:
Running a macro that opens a query doesn't actually do anything with the
results of the query. Realistically, the only reason you'd ever want to run
a query is because it's an Action query (eg a Make Table, Append, Update or
Delete query).

If you've got a query that's intended to return a specific value, just use
DLookup to refer to the value returned by the query.

And just a comment: you might consider moving away from calling macros, and
use VBA code instead. Access will convert the macros into VBA code for you
if you like. Select the macro in the database window and right-click. One of
the options will "Save As/Export...". Choose that, and let it save the macro
as VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
Douglas, thank you so much for your help. It's finally working.
Now I have another small problem, maybe you can help me:
I have 2 macros: mcrAfterUpdateFrequency, mcrNextScheduledDate- both of
them
are opening 2 queries that calculating Dates.

That's what I have on the After Update of Status Field:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
Completion_Date = Date
DoCmd.RunMacro "mcrAfterUpdateFrequency"
DoCmd.RunMacro "mcrNextScheduledDate"
DoCmd.RunMacro "mcrAppendPms"
Else: Completion_Date = Null
End If
End Sub

The question is: when I am changing the Status, both of the queries opens,
but I don't see the calculations on the Form (on the new PM that created
as
the result of the Append Query). How I can make the queries to Run and not
to
be Open, and that I will see the calculations on the Dates on the new PM
that
was appended?
Is it making sense?

Thank you again for your help.


Douglas J. Steele said:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
CompletedDate = Date()
DoCmd.RunMacro "mcrAppendPms"
Else
CompletedDate = Null
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date
situation
?

Thank you very much for the help and the patience.
Anna

:

Include whatever your macro is doing into Al's suggested code. You can
have
the Event Procedure VBA code run the macro, or put the equivalent code
into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I can't place it on the Status AfterUpdate event, because I there
have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


:

Anna,
That's because CompletedDate is a calculated field... not
"bound"
to a
table field.

If you don't already have a CompletedDate field in your table,
add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your
life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I
can
do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the
"Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date
that
it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I
will
enter
to
this record tomorrow, the date will stay 04/12/07 and not
04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
A

Al Campagna

Anna,
I'd like to ditto Douglas on the suggestion to make the leap to all code... rather than
mix macros and code.
It's really best to have all your code for a form/report all in one place. Bouncing
between code and macros will drive you nuts after a while, and... can make your code
really hard to bug-shoot, and read.
Go code... you'll never go back! :-D
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Anna said:
Thank you so much for your huge help and the patience! It is working as a
charm.
I understood where I was wrong. Have a good weekend.

Anna



Douglas J. Steele said:
Running a macro that opens a query doesn't actually do anything with the
results of the query. Realistically, the only reason you'd ever want to run
a query is because it's an Action query (eg a Make Table, Append, Update or
Delete query).

If you've got a query that's intended to return a specific value, just use
DLookup to refer to the value returned by the query.

And just a comment: you might consider moving away from calling macros, and
use VBA code instead. Access will convert the macros into VBA code for you
if you like. Select the macro in the database window and right-click. One of
the options will "Save As/Export...". Choose that, and let it save the macro
as VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Anna said:
Douglas, thank you so much for your help. It's finally working.
Now I have another small problem, maybe you can help me:
I have 2 macros: mcrAfterUpdateFrequency, mcrNextScheduledDate- both of
them
are opening 2 queries that calculating Dates.

That's what I have on the After Update of Status Field:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
Completion_Date = Date
DoCmd.RunMacro "mcrAfterUpdateFrequency"
DoCmd.RunMacro "mcrNextScheduledDate"
DoCmd.RunMacro "mcrAppendPms"
Else: Completion_Date = Null
End If
End Sub

The question is: when I am changing the Status, both of the queries opens,
but I don't see the calculations on the Form (on the new PM that created
as
the result of the Append Query). How I can make the queries to Run and not
to
be Open, and that I will see the calculations on the Dates on the new PM
that
was appended?
Is it making sense?

Thank you again for your help.


:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
CompletedDate = Date()
DoCmd.RunMacro "mcrAppendPms"
Else
CompletedDate = Null
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date
situation
?

Thank you very much for the help and the patience.
Anna

:

Include whatever your macro is doing into Al's suggested code. You can
have
the Event Procedure VBA code run the macro, or put the equivalent code
into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I can't place it on the Status AfterUpdate event, because I there
have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


:

Anna,
That's because CompletedDate is a calculated field... not
"bound"
to a
table field.

If you don't already have a CompletedDate field in your table,
add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your
life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I
can
do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the
"Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date
that
it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I
will
enter
to
this record tomorrow, the date will stay 04/12/07 and not
04/13/07.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for the suggestion.
I eventually got rid of the multiply macros and placed all the connections
to the Append Query, it’s seams to work.

Al Campagna said:
Anna,
I'd like to ditto Douglas on the suggestion to make the leap to all code... rather than
mix macros and code.
It's really best to have all your code for a form/report all in one place. Bouncing
between code and macros will drive you nuts after a while, and... can make your code
really hard to bug-shoot, and read.
Go code... you'll never go back! :-D
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Anna said:
Thank you so much for your huge help and the patience! It is working as a
charm.
I understood where I was wrong. Have a good weekend.

Anna



Douglas J. Steele said:
Running a macro that opens a query doesn't actually do anything with the
results of the query. Realistically, the only reason you'd ever want to run
a query is because it's an Action query (eg a Make Table, Append, Update or
Delete query).

If you've got a query that's intended to return a specific value, just use
DLookup to refer to the value returned by the query.

And just a comment: you might consider moving away from calling macros, and
use VBA code instead. Access will convert the macros into VBA code for you
if you like. Select the macro in the database window and right-click. One of
the options will "Save As/Export...". Choose that, and let it save the macro
as VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas, thank you so much for your help. It's finally working.
Now I have another small problem, maybe you can help me:
I have 2 macros: mcrAfterUpdateFrequency, mcrNextScheduledDate- both of
them
are opening 2 queries that calculating Dates.

That's what I have on the After Update of Status Field:
Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
Completion_Date = Date
DoCmd.RunMacro "mcrAfterUpdateFrequency"
DoCmd.RunMacro "mcrNextScheduledDate"
DoCmd.RunMacro "mcrAppendPms"
Else: Completion_Date = Null
End If
End Sub

The question is: when I am changing the Status, both of the queries opens,
but I don't see the calculations on the Form (on the new PM that created
as
the result of the Append Query). How I can make the queries to Run and not
to
be Open, and that I will see the calculations on the Dates on the new PM
that
was appended?
Is it making sense?

Thank you again for your help.


:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
CompletedDate = Date()
DoCmd.RunMacro "mcrAppendPms"
Else
CompletedDate = Null
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This is what I have on the After Event Procedure on the Status Field:

Private Sub Status_AfterUpdate()
If Me.Status = "Comp-Completed" Then
DoCmd.RunMacro "mcrAppendPms"
End If
End Sub

How is it possible, if at all, to add the Completed Date = Date
situation
?

Thank you very much for the help and the patience.
Anna

:

Include whatever your macro is doing into Al's suggested code. You can
have
the Event Procedure VBA code run the macro, or put the equivalent code
into
the event's VBA.

Another alternative would be to put logic in the form's BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I can't place it on the Status AfterUpdate event, because I there
have
already Run Macro Comd.
But I tried to place that in the CompeledDate AfterUpdate event:
Private Sub Completion_Date_AfterUpdate()
If Status = "Comp-Completed" Then
Completion_Date = Date
Else: Completed_Date = Null
End If
End Sub

But it's doesn't work.
What am I doing wrong?


:

Anna,
That's because CompletedDate is a calculated field... not
"bound"
to a
table field.

If you don't already have a CompletedDate field in your table,
add
one, and make it's
ControlSource CompletedDate on the form.

Use the AfterUpdate event of Status...

If [Status] = "Comp-Completed" Then
CompletedDate = Date()
Else
CompletedDate = Null
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your
life."

Thank you for the quick answer.
I know that I can't store the calculation in the table, so how I
can
do
it?


:

The only way is to store the date in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


It's probably very simple thing, but I can't find it out:
I have following calculation as control source in the
"Completed
Date"
field.
=IIf([Status]="Comp-Completed",Date(),"")
It's updating the date when the status is changing.
The question is - how I make the date stay of the same date
that
it
was
changed and that it will not update to today's date.
For example: if the status changed today (04/12/07), that if I
will
enter
to
this record tomorrow, the date will stay 04/12/07 and not
04/13/07.

Thanks for the help.
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

Allen Browne’s date range vba for more reports 2
Form to organize records by todays date 4
Forms to Prompt Users for Action; Guide User Through Overall Proce 1
Assign today's date to field based on combo box choice 1
Calculating the difference between two dates if two criteria are m 3
Nested IIF problem 2
graphical indicator 1
Extra space between paragraphs when sending 4

Top