Locking a field value after record creation.

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

Hi. Im not sure if this is the right place to post this. I
have Access 2003. Can anyone tell me if its possible, when
a record is created, for one field to automatically take
its value from another related records field and then to
lock that value, even if the source value changes?

Ill expand a little. Say I have TableA with a text field
called StartYear. Every record created in this table will
either have the StartYear field contents as the same year
value (eg 2002) or will be Null. Now assume TableB, linked
to TableA by SchoolID fields and which also has a
StartYear text field. When the user creates a new record
in TableB, I want Access to automatically insert the
TableA StartYear value in the TableB StartYear field and
then lock it somehow, so that when the TableA StartYear
value is changed, the TableB StartYear value will stay the
same as when the TableB record was created. Is this
possible?

Thanks, Noel
 
-----Original Message-----
Hi. Im not sure if this is the right place to post this. I
have Access 2003. Can anyone tell me if its possible, when
a record is created, for one field to automatically take
its value from another related records field and then to
lock that value, even if the source value changes?

Ill expand a little. Say I have TableA with a text field
called StartYear. Every record created in this table will
either have the StartYear field contents as the same year
value (eg 2002) or will be Null. Now assume TableB, linked
to TableA by SchoolID fields and which also has a
StartYear text field. When the user creates a new record
in TableB, I want Access to automatically insert the
TableA StartYear value in the TableB StartYear field and
then lock it somehow, so that when the TableA StartYear
value is changed, the TableB StartYear value will stay the
same as when the TableB record was created. Is this
possible?

Thanks, Noel

.
Click to expand...
Hi Noel, ensure that the code that is updating the value
in tableB first checks that the field IsNull(StartYear).
Only then make the change.

Luck
Jonathan
 
Hi Jonathan. Thanks for this reply but its left me a bit
confused - a state Im becoming quite used to. I think you
are suggesting an improvement to code that I dont have
yet. Im asking if what I want to do is possible and if so,
how to do it. Does your reply imply that it is possible?
If so, please tell me how. Thanks again, Noel
 
It is certainly possible to do what you want, assuming you are developing an
application and controlling the user's access so that they don't get
datasheet view of tables or queries and that they aren't allowed to design
and run new queries.

If those conditions are met, then on the form where the record is entered,
if its .NewRecord property is True, retrieve the value you want (in
whichever way is appropriate -- open a recordset, use a DLookup, or pick it
up from a global variable, a control on an open form, or ???). Make sure, if
you display that field for existing Records, that the Control in which it is
displayed is Locked = Yes and Enabled = No.

You can't protect it at the Table level. You can't protect it if you give
users full access to datasheet view and query creation capabilities.

Larry Linson
Microsoft Access MVP
 
Hi Larry, Thanks for the reply. Im still a bit confused
here. I wonder if Ive made myself clear. The point is that
when I create a new record, I want to cause the automatic
taking of a value from one field in one table and the
copying of it to a similar named field in the new record
(which is in a second table), then to automatically ensure
that that value is fixed in the second table. If I create
a new record in the second table using a subform and I
use, for example, a DLookup pointing at the first tables
field, surely all Im doing is viewing the value in that
original field. That’s not doing anything to copy that
value to the record in the second table. Im not sure if I
can explain this any better. Could you reread my initial
post again? If its still unclear, how can I proceed – I
could provide a simple (written) example of two tables and
their records I suppose. What do you think? Thanks again
for your time. Noel
 
Hi Larry. Since my last post earlier today Ive thought of
a solution – perhaps this is what you meant all along. Ive
put a text box in my subform, with the control source as
the field I want the value to go to. Then Ive put the
following code (which I cobbled together using the DLookup
Help notes) in the fields Default Value box:

=DLookUp("[PlacementStartYear]","Schools","[SchoolID] = "
& [Forms]![Schools2]![SchoolID])

This sets the default for new records as the value in the
PlacementStartYear field in the record showing on the main
form (Schools2). Then Ive done as you said (Enabled = No,
Locked = Yes)

This does exactly what I want. The PlacementStartYear
value is automatically copied in when a new record is
created and it does not change even when the origin field
value changes. Is this what you meant I should do? Anyway,
Im happy for now (unless I go into work and cannot get it
to work on the main db). Thanks for the help, Noel
 
Hi again Larry. One last question (hopefully). Ive just
realised that Im using the same Subform in a number of
different main forms, so my DLookup code needs to take
account of that. I thinks the code Im using, in my sample
db, specifically mentions the main forms name (Schools2).
Can you advise how to the code should be without
mentioning the main form? To expand, in the subform I
have a combo box which allows the user to select a School
which puts the SchoolID in the Placements table SchoolID
field. So basically I want the DLookup to say " based on
the SchoolID just selected, go to the Schools table, find
the appropriate School record and take a copy of the
contents of the PlacementsStartYear field".

The code I have at present is:

=DLookUp("[PlacementStartYear]","Schools","[SchoolID] = "
& [Forms]![Schools2]![SchoolID])

Thanks for the help, Noel

-----Original Message-----
Hi Larry. Since my last post earlier today Ive thought of
a solution - perhaps this is what you meant all along. Ive
put a text box in my subform, with the control source as
the field I want the value to go to. Then Ive put the
following code (which I cobbled together using the DLookup
Help notes) in the fields Default Value box:

=DLookUp("[PlacementStartYear]","Schools","[SchoolID] = "
& [Forms]![Schools2]![SchoolID])

This sets the default for new records as the value in the
PlacementStartYear field in the record showing on the main
form (Schools2). Then Ive done as you said (Enabled = No,
Locked = Yes)

This does exactly what I want. The PlacementStartYear
value is automatically copied in when a new record is
created and it does not change even when the origin field
value changes. Is this what you meant I should do? Anyway,
Im happy for now (unless I go into work and cannot get it
to work on the main db). Thanks for the help, Noel
-----Original Message-----
It is certainly possible to do what you want, assuming you are developing an
application and controlling the user's access so that they don't get
datasheet view of tables or queries and that they
Click to expand...
aren't
allowed to design
and run new queries.

If those conditions are met, then on the form where the record is entered,
if its .NewRecord property is True, retrieve the value you want (in
whichever way is appropriate -- open a recordset, use a DLookup, or pick it
up from a global variable, a control on an open form, or ???). Make sure, if
you display that field for existing Records, that the Control in which it is
displayed is Locked = Yes and Enabled = No.

You can't protect it at the Table level. You can't protect it if you give
users full access to datasheet view and query creation capabilities.

Larry Linson
Microsoft Access MVP
Click to expand...
stay
the


.
Click to expand...
.
Click to expand...
 
Hi Larry. My supposed solution isnt as flexible as I
thought so Im back where I started. Since this has now
become a lot more clear in my head, Ive decided to repost
this question - Im sure youll be glad Im not blasting you
with any more posts. Thanks for the help so far, Noel
-----Original Message-----
Hi Larry. Since my last post earlier today Ive thought of
a solution - perhaps this is what you meant all along. Ive
put a text box in my subform, with the control source as
the field I want the value to go to. Then Ive put the
following code (which I cobbled together using the DLookup
Help notes) in the fields Default Value box:

=DLookUp("[PlacementStartYear]","Schools","[SchoolID] = "
& [Forms]![Schools2]![SchoolID])

This sets the default for new records as the value in the
PlacementStartYear field in the record showing on the main
form (Schools2). Then Ive done as you said (Enabled = No,
Locked = Yes)

This does exactly what I want. The PlacementStartYear
value is automatically copied in when a new record is
created and it does not change even when the origin field
value changes. Is this what you meant I should do? Anyway,
Im happy for now (unless I go into work and cannot get it
to work on the main db). Thanks for the help, Noel
-----Original Message-----
It is certainly possible to do what you want, assuming you are developing an
application and controlling the user's access so that they don't get
datasheet view of tables or queries and that they
Click to expand...
aren't
allowed to design
and run new queries.

If those conditions are met, then on the form where the record is entered,
if its .NewRecord property is True, retrieve the value you want (in
whichever way is appropriate -- open a recordset, use a DLookup, or pick it
up from a global variable, a control on an open form, or ???). Make sure, if
you display that field for existing Records, that the Control in which it is
displayed is Locked = Yes and Enabled = No.

You can't protect it at the Table level. You can't protect it if you give
users full access to datasheet view and query creation capabilities.

Larry Linson
Microsoft Access MVP
Click to expand...
stay
the


.
Click to expand...
.
Click to expand...
 
Hey Noel, Larry won't even *SEE* your reply if you arbitrarily change the
subject line!!

Some of these newsgroups get hundreds of posts per day. People look for
answers under the subject line that they used when they posted.

HTH,
TC



Noel said:
Hi Larry. My supposed solution isnt as flexible as I
thought so Im back where I started. Since this has now
become a lot more clear in my head, Ive decided to repost
this question - Im sure youll be glad Im not blasting you
with any more posts. Thanks for the help so far, Noel
-----Original Message-----
Hi Larry. Since my last post earlier today Ive thought of
a solution - perhaps this is what you meant all along. Ive
put a text box in my subform, with the control source as
the field I want the value to go to. Then Ive put the
following code (which I cobbled together using the DLookup
Help notes) in the fields Default Value box:

=DLookUp("[PlacementStartYear]","Schools","[SchoolID] = "
& [Forms]![Schools2]![SchoolID])

This sets the default for new records as the value in the
PlacementStartYear field in the record showing on the main
form (Schools2). Then Ive done as you said (Enabled = No,
Locked = Yes)

This does exactly what I want. The PlacementStartYear
value is automatically copied in when a new record is
created and it does not change even when the origin field
value changes. Is this what you meant I should do? Anyway,
Im happy for now (unless I go into work and cannot get it
to work on the main db). Thanks for the help, Noel
-----Original Message-----
It is certainly possible to do what you want, assuming you are developing an
application and controlling the user's access so that they don't get
datasheet view of tables or queries and that they
Click to expand...
aren't
allowed to design
and run new queries.

If those conditions are met, then on the form where the record is entered,
if its .NewRecord property is True, retrieve the value you want (in
whichever way is appropriate -- open a recordset, use a DLookup, or pick it
up from a global variable, a control on an open form, or ???). Make sure, if
you display that field for existing Records, that the Control in which it is
displayed is Locked = Yes and Enabled = No.

You can't protect it at the Table level. You can't protect it if you give
users full access to datasheet view and query creation capabilities.

Larry Linson
Microsoft Access MVP

Hi. Im not sure if this is the right place to post this. I
have Access 2003. Can anyone tell me if its possible, when
a record is created, for one field to automatically take
its value from another related records field and then to
lock that value, even if the source value changes?

Ill expand a little. Say I have TableA with a text field
called StartYear. Every record created in this table will
either have the StartYear field contents as the same year
value (eg 2002) or will be Null. Now assume TableB, linked
to TableA by SchoolID fields and which also has a
StartYear text field. When the user creates a new record
in TableB, I want Access to automatically insert the
TableA StartYear value in the TableB StartYear field and
then lock it somehow, so that when the TableA StartYear
value is changed, the TableB StartYear value will
Click to expand...
stay
the
same as when the TableB record was created. Is this
possible?

Thanks, Noel



.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top