Datasheet Form problem

[Tony Williams]

I have a subform that is in datasheet format. There are 3 controls
ActionDate (Source Date()), ActionTime (Source Time()), and ActionLog (Text
field). All 3 fields are locked and have this code in onCurrent event
Private Sub Form_Current()
If Me.NewRecord Then
Me.Action_Date.Locked = False
Else
Me.Action_Date.Locked = True
End If
If Me.NewRecord Then
Me.Action_Time.Locked = False
Else
Me.Action_Time.Locked = True
End If
If Me.NewRecord Then
Me.Action_Log.Locked = False
Else
Me.Action_Log.Locked = True
End If
End Sub
This works fine but the problem is as soon as I start to fill in the latest
log a new record is created in the datasheet with today's date and time.
This means that the next time I open that subform the date and tme that this
empty record was created are already there not the date and time the subform
is opened. I think I need it to stop creating a new record automatically but
default the date and time when the sub form is opened for input.
Anyone help?
Thanks
Tony

 

[Mark A. Sam]

Tony,

When I first began reading the routine, I thought that you should set
defaults for those controls, otherwise you would be establishing a new
recored each time you to to a new record. Set the default values on the
controls, then when you enter data, the defaults will fill in automatically.

God Bless,

Mark A. Sam

 

[Tony Williams]

Thanks for that. However as I said the control sources for the date and time
are set to default to the system date and time using Date() and Time(). I
would prefer for them to be set when the user starts to enter data not when
the form is opened though?
Any ideas?
Thanks
Tony

 

[Mark A. Sam]

Yes, try te BeforeInsert of BeforeUpdate, or OnDirty event. I didnt test
this but one or all should work.

 

[Tony Williams]

Thanks Mark I'll try that
Tony

Yes, try te BeforeInsert of BeforeUpdate, or OnDirty event. I didnt test
this but one or all should work.

 

[BruceM]

Just thought I'd mention that you don't need to test for a new record for
each control. You can do:

If Me.NewRecord Then
Me.Action_Date.Locked = False
Me.Action_Time.Locked = False
Me.Action_Log.Locked = False
Else
Me.Action_Date.Locked = True
Me.Action_Time.Locked = True
Me.Action_Log.Locked = True
End If

You may do better not using the Default Value. Instead, use a single field
(I will call it TimeDate) for both the time and the date. In the After
Update event for txtAction_Log (the text box bound to Action_Date), you
could have:

If Me.NewRecord Then
Me.TimeDate = Now
End If

Then, in two unbound text boxes on the form, set the control source to:
=Format([TimeDate],"dd-mmm-yyyy")
and
=Format([TimeDate],"hh:nn AM/PM")

Use whatever format you prefer, of course.

Now stores both Date and Time, so one field is all you need for both values.
You can extract the date and time portions as needed.

 

[Tony Williams]

Thanks Bruce that's really useful.
Tony

Just thought I'd mention that you don't need to test for a new record for
each control. You can do:

If Me.NewRecord Then
Me.Action_Date.Locked = False
Me.Action_Time.Locked = False
Me.Action_Log.Locked = False
Else
Me.Action_Date.Locked = True
Me.Action_Time.Locked = True
Me.Action_Log.Locked = True
End If

You may do better not using the Default Value. Instead, use a single
field (I will call it TimeDate) for both the time and the date. In the
After Update event for txtAction_Log (the text box bound to Action_Date),
you could have:

If Me.NewRecord Then
Me.TimeDate = Now
End If

Then, in two unbound text boxes on the form, set the control source to:
=Format([TimeDate],"dd-mmm-yyyy")
and
=Format([TimeDate],"hh:nn AM/PM")

Use whatever format you prefer, of course.

Now stores both Date and Time, so one field is all you need for both
values. You can extract the date and time portions as needed.

Thanks Mark I'll try that
Tony