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.
