Wish to automatically update a field each time a new record is created.

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I wish to automatically update a field each time a new
record is created. More specifically, when a new record
is created, and a autonumber ID is applied, I wish to
update the cell of another field (PhotoName) in that same
record.

This is the expression I used to populate the
field 'Photoname' in the first place:

"D:\chris\CORES\photos\16.jpg" & [PhotoID] & ".jpg"
(Where 'PhotoID' is the autonumber field)

Can I modify this expression and use it to update the
record each time a new record is created?

Many thanks for the help, as always!
Chris
 
I see two problems. Correcting the major problem will also correct the
secondary problem:

1. I don't believe the autonumber will be committed until you save the
record so you would have to save the record first, then open it again and
insert your formatted ID into the record.

2. By using your primary key as a component of another data field you are
violating one of the rules of normalization. Not necessarily fatal but a
bad practice.

3. By using an Autonumber field as a component of another data field
you're inviting lots of problems you don't want. autonumber fields exist
only to guarantee uniqueness that might be useful in a Primary Key.
Autonumbers are not guaranteed to be sequential. Deleting a record will
create gaps in the sequence. Multiple simultaneous users can create
non-monotonic outcomes. If a user will see it in any form, DON'T USE
AUTONUMBER! Pardon the shouting.but emphasis is required.

So, what to do about it?

Create and maintain your own sequence generator. That gives you complete
control over it. That also takes the primary key out of the visible
picture. Check out "Autonumber" on http://mvps.org/access

HTH
 
Back
Top