Date Function

[Mahesh]

Hi

I have a query which has the following fields.

Registration Number, Meeting Number, Client ID, Registration Date,
Attended, Cancelled, No Show, Date Modified. Modified By

I would like the date modified to be automatic i.e when the user puts
the attendance information ( either attended/ cancelled/ no show ) the
date modified should show the system date.

Is this possible and if yes, how ???

Thanks in advance.

Mahesh

 

[Guest]

Use your query as record source for a form. Have the On Update property of
the text boxes you want to change the date with to call a macro that has Set
Value as action. Set the DateModified text box to Date() or Now() you also
want time recordsed.

 

[John Spencer]

The classic way to do this would be to use a form for input that is attached
to the query. Then you can use VBA in the form's Controls' events to
populate the Date Modified field.

By the way, I would change Attended, Cancelled, and No Show into one field
that would show one of four states - blank, "Attended", "Cancelled", or "No
Show". That has the advantage of allowing you to add additional attendance
information categories easily and automatically allowing only one of the
mutually exclusive answers.

The VBA code on the form (with one field - AttendanceStatus) could be
something like the following untested code.

Private Sub AttendanceStatus_AfterUpdate()

IF IsNull(Me.AttendanceStatus) = False Then
Me.[Date Modified] = Date()
Else
Me.[Date Modified] = Null
End IF

End Sub

 

[Mahesh]

Hi John

I did change the attendance status to one field and copied the
following code and paste it in After Update event for Attendance status
field. But there is no change in the date.

Thanks

Mahesh

The classic way to do this would be to use a form for input that is attached
to the query. Then you can use VBA in the form's Controls' events to
populate the Date Modified field.

By the way, I would change Attended, Cancelled, and No Show into one field
that would show one of four states - blank, "Attended", "Cancelled", or "No
Show". That has the advantage of allowing you to add additional attendance
information categories easily and automatically allowing only one of the
mutually exclusive answers.

The VBA code on the form (with one field - AttendanceStatus) could be
something like the following untested code.

Private Sub AttendanceStatus_AfterUpdate()

IF IsNull(Me.AttendanceStatus) = False Then
Me.[Date Modified] = Date()
Else
Me.[Date Modified] = Null
End IF

End Sub

Hi

I have a query which has the following fields.

Registration Number, Meeting Number, Client ID, Registration Date,
Attended, Cancelled, No Show, Date Modified. Modified By

I would like the date modified to be automatic i.e when the user puts
the attendance information ( either attended/ cancelled/ no show ) the
date modified should show the system date.

Is this possible and if yes, how ???

Thanks in advance.

Mahesh

 

[John Spencer]

Did you click on the after update event of the control and select "[Event
Procedure]" and then click the three dot button next to the property. And
then enter the code.

Sorry if that seems basic, but I have had people paste the code in the wrong
place before.

Hi John

I did change the attendance status to one field and copied the
following code and paste it in After Update event for Attendance status
field. But there is no change in the date.

Thanks

Mahesh

The classic way to do this would be to use a form for input that is
attached
to the query. Then you can use VBA in the form's Controls' events to
populate the Date Modified field.

By the way, I would change Attended, Cancelled, and No Show into one
field
that would show one of four states - blank, "Attended", "Cancelled", or
"No
Show". That has the advantage of allowing you to add additional
attendance
information categories easily and automatically allowing only one of the
mutually exclusive answers.

The VBA code on the form (with one field - AttendanceStatus) could be
something like the following untested code.

Private Sub AttendanceStatus_AfterUpdate()

IF IsNull(Me.AttendanceStatus) = False Then
Me.[Date Modified] = Date()
Else
Me.[Date Modified] = Null
End IF

End Sub

Hi

I have a query which has the following fields.

Registration Number, Meeting Number, Client ID, Registration Date,
Attended, Cancelled, No Show, Date Modified. Modified By

I would like the date modified to be automatic i.e when the user puts
the attendance information ( either attended/ cancelled/ no show ) the
date modified should show the system date.

Is this possible and if yes, how ???

Thanks in advance.

Mahesh

 

[Mahesh]

Hi John

I did paste the code in the VB editor.

Thanks
Mahesh

Did you click on the after update event of the control and select "[Event
Procedure]" and then click the three dot button next to the property. And
then enter the code.

Sorry if that seems basic, but I have had people paste the code in the wrong
place before.

Hi John

I did change the attendance status to one field and copied the
following code and paste it in After Update event for Attendance status
field. But there is no change in the date.

Thanks

Mahesh

The classic way to do this would be to use a form for input that is
attached
to the query. Then you can use VBA in the form's Controls' events to
populate the Date Modified field.

By the way, I would change Attended, Cancelled, and No Show into one
field
that would show one of four states - blank, "Attended", "Cancelled", or
"No
Show". That has the advantage of allowing you to add additional
attendance
information categories easily and automatically allowing only one of the
mutually exclusive answers.

The VBA code on the form (with one field - AttendanceStatus) could be
something like the following untested code.

Private Sub AttendanceStatus_AfterUpdate()

IF IsNull(Me.AttendanceStatus) = False Then
Me.[Date Modified] = Date()
Else
Me.[Date Modified] = Null
End IF

End Sub


Hi

I have a query which has the following fields.

Registration Number, Meeting Number, Client ID, Registration Date,
Attended, Cancelled, No Show, Date Modified. Modified By

I would like the date modified to be automatic i.e when the user puts
the attendance information ( either attended/ cancelled/ no show ) the
date modified should show the system date.

Is this possible and if yes, how ???

Thanks in advance.

Mahesh

 

[John Spencer]

Any error messages?
Have you tried to see if the code gets called? Put a break on the code to
see if it gets called or add something like the following as a temporary
test.
MsgBox "Yes! This code has been called",,"Stop bothering me!"

Do you have a control bound to the date field on the form (control's visible
property can be false)?

Is the date field in the recordset?

Hi John

I did paste the code in the VB editor.

Thanks
Mahesh

Did you click on the after update event of the control and select "[Event
Procedure]" and then click the three dot button next to the property.
And
then enter the code.

Sorry if that seems basic, but I have had people paste the code in the
wrong
place before.

Hi John

I did change the attendance status to one field and copied the
following code and paste it in After Update event for Attendance status
field. But there is no change in the date.

Thanks

Mahesh


John Spencer wrote:
The classic way to do this would be to use a form for input that is
attached
to the query. Then you can use VBA in the form's Controls' events to
populate the Date Modified field.

By the way, I would change Attended, Cancelled, and No Show into one
field
that would show one of four states - blank, "Attended", "Cancelled",
or
"No
Show". That has the advantage of allowing you to add additional
attendance
information categories easily and automatically allowing only one of
the
mutually exclusive answers.

The VBA code on the form (with one field - AttendanceStatus) could be
something like the following untested code.

Private Sub AttendanceStatus_AfterUpdate()

IF IsNull(Me.AttendanceStatus) = False Then
Me.[Date Modified] = Date()
Else
Me.[Date Modified] = Null
End IF

End Sub


Hi

I have a query which has the following fields.

Registration Number, Meeting Number, Client ID, Registration Date,
Attended, Cancelled, No Show, Date Modified. Modified By

I would like the date modified to be automatic i.e when the user
puts
the attendance information ( either attended/ cancelled/ no show )
the
date modified should show the system date.

Is this possible and if yes, how ???

Thanks in advance.

Mahesh