A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Field Before Update property



 
 
Thread Tools Display Modes
  #1  
Old June 11th, 2005, 12:18 AM
Robert_L_Ross
external usenet poster
 
Posts: n/a
Default Field Before Update property

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?
  #2  
Old June 11th, 2005, 01:28 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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

--

Ken Snell
MS ACCESS MVP

"Robert_L_Ross" wrote in message
...
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?



  #3  
Old June 11th, 2005, 03:49 AM
Robert_L_Ross
external usenet poster
 
Posts: n/a
Default

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?

"Ken Snell [MVP]" wrote:

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

--

Ken Snell
MS ACCESS MVP

"Robert_L_Ross" wrote in message
...
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?




  #4  
Old June 11th, 2005, 05:32 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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


"Robert_L_Ross" wrote in message
news
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?

"Ken Snell [MVP]" wrote:

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

--

Ken Snell
MS ACCESS MVP

"Robert_L_Ross" wrote in message
...
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?






  #5  
Old June 11th, 2005, 10:41 AM
Robert_L_Ross
external usenet poster
 
Posts: n/a
Default

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!

"Ken Snell [MVP]" wrote:

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


"Robert_L_Ross" wrote in message
news
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?

"Ken Snell [MVP]" wrote:

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

--

Ken Snell
MS ACCESS MVP

"Robert_L_Ross" wrote in message
...
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?






  #6  
Old June 11th, 2005, 05:39 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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



"Robert_L_Ross" wrote in message
...
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!

"Ken Snell [MVP]" wrote:

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


"Robert_L_Ross" wrote in message
news
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?

"Ken Snell [MVP]" wrote:

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

--

Ken Snell
MS ACCESS MVP

"Robert_L_Ross" wrote in
message
...
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?








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Query "Only Update if Field is blank" Amy Adams via AccessMonster.com Running & Setting Up Queries 5 March 25th, 2005 08:03 PM
How do I update a timestamp field when another field is updated Swhite951 Using Forms 1 March 15th, 2005 11:40 PM
Update field value automatically based on subform Sammie Using Forms 0 February 11th, 2005 10:49 PM
NUMBERING the pages Bob New Users 7 June 14th, 2004 12:20 AM
field manipulation Steve Running & Setting Up Queries 2 May 28th, 2004 03:12 PM


All times are GMT +1. The time now is 04:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.