Field Before Update property

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, here's the deal:

I have a field on my form that defaults to today's date (called RecdDate).
The user has a chance to change that field when the form is first opened, as
it is the default first field the form tabs to.

I want the user to be able to change the default date to a new date, but
after the record is saved the first time, I don't want the user to change
that field. See, the field generates a Julian Date. I combine the Julian
Date along with a Batch Number to create a BatchID - a key that can never
change.

If I allow the user to change the RecdDate, it would change the Julian Date,
which would change the BatchID and create orphan records.

How can I use the Before Update property of the field to allow me to store
or change the first time the record is opened, but never allow it if the
record has been saved?
 
Perhaps use the GotFocus event (or the form's Current event) to test the
value that is in the textbox and see if it matches the default value, and
lock the data if it doesn't.

Private Sub ControlName_GotFocus()
Me.ControlName.Locked = Not (Me.ControlName.Value = _
Me.ControlName.DefaultValue)
End Sub
 
If I do that, it won't allow me change it the first time.

Here's how it should work:
New record: RecdDate should default to today's date.
User should be able to change the date and move to the next field (since
it's a new record) or keep the date as-is and move to the next field.

Existing record: RecdDate should be pre-populated
If the user attempts to change the date, they are prevented from doing so.

I'm thinking there should be a macro or code I could refer to using Before
Update that could check to see if the field is null...if so, allow the update
to go forward. If the field is not null, prevent the update (stop the
update, show a msgbox, something like that).

Can't that be programmed into the Before Update event?
 
How are you putting the default date in the RecdDate textbox? Are you using
the ControlSource to do that? Or the DefaultValue to do that?

You should be using the DefaultValue to do this. The code I suggested
assumes that that is how it's being done.

Post more details about what your setup is.

I'm not inclined to look at the BeforeUpdate event for what you seek to do,
because that allows the user to go to the trouble of changing the data and
then you "try" to stop the change...this won't be "helpful" for your users.
That is why I'm suggesting a different event and to lock the data so that
the user is prevented from even trying to change the data.

--

Ken Snell
<MS ACCESS MVP>
 
The default date is done by using the default value (Default value = now() or
date(), something like that - it's at work right now so I can't reference it
exactly).

I want the user to be able to change that date if needed when they first
create the new record. ...see, the database is to track 'batches' of work.
Sometimes the work is already completed (work generated by a phone call).
So, when the work is received via paper (we call it batch work), I want the
user to be given the default date of today and everything is fine. When the
user receives work that was generated from a call (we call it post-batch
work), the work is already done, and sometimes it's done the day before or
even more. I want the user to be able to set the date back as needed. The
cincher is that this date can only be allowed to change when the record is
first being created. If for some reason they select the wrong date, the
batch will be marked for deletion (it's actually marked for supression as the
data is housed on a SQL server).

The date that is entered will generate a Julian date that will be combined
with a batch number to create what we call a BatchID, a unique key that is
then used to tie in the records of work done on the batch. It's a
one-to-many relation between the header info (Received date, etc.) and the
work table (who worked how many of the batch on what day).

You can see, if we allow the user to change a date accidentally on an
existing batch, we could cause the Julian date to change, breaking the link
between the header info (stored on the BatchControl table) and the work info
(stored on the BatchWork table and tied to BatchControl using BatchID -
created with the Julian date that was made with the Received date).

I want to make sure the code you listed would allow the user to change the
date the very first time they see it - when the record is being created - but
not any other time AFTER that.

Sorry this was so long, but I'm thinking maybe I didn't give enough detail
to accurately describe what I need.

THX for taking the time to read and reply, I REALLY appreciate it!
 
Thanks for the explanation. That is helpful.

If you're using Now() as the DefaultValue, then my code won't work for your
needs. That is because Now includes seconds, and it won't match the value
that was written in as the "default" when the record is created after just
one second. If you're using Date(), then the code will work to allow changes
anytime on the date that the record is created, and not thereafter. Neither
of these situations is what you seek to accomplish.

Thus, we can use the Current event of the form to test whether the record is
a "new" record -- that is, it's being created. If it is, it's possible then
to allow changes to the RecdDate value. If it's not (meaning that the record
has already been saved), then it can prevent such changes.

Private Sub Form_Current()
Me.RecdDate.Locked = Not Me.NewRecord
End Sub

The above code will lock the data in the RecdDate control if the record has
already been saved. If it's not been saved yet, then the data are unlocked
and available for editing.

We also can use the form's AfterUpdate event to lock the data as soon as the
record is saved.

Private Sub Form_AfterUpdate()
Me.RecdDate.Locked = True
End Sub

Using both of these codes in your form should allow you to do what you seek.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top