Find and Replace

[steve walker]

Hi
Access 2000 Win XP. I need to find and replace fields in a table I have. The
fields are Year (example 7,8,9 etc), Class (example 07A/In1, 07A/In2 etc).
Form (7A,7B etc). As students progress to the next year they move from 07 to
08 etc. I could use the find and replace function (that's what I've done for
this year) but need to link a macro (can't find a function there) or VBA to
do it automatically at the end of the year.

Any help would be appraciated.

Steve Walker

 

[Jeff Boyce]

Steve

Why would you want to replace last year's data? Won't that erase the
historical record?

What if you kept last years and started this years?

Can you provide just a bit more description of the data as you have it
structured?

 

[steve walker]

Thanks for your response Jeff

Each Year a student moves for Class 07A/In1 to Class 08A/In1, they also
move Form Group from 07A to 08A and of course the Year from 7 to 8. I have
one table with this information in for around 900 records. This table is
related to Unit of Work Table, Detentions, etc.

As the oldest group leave school I use an append query to put their records
into a table and then delete them using a delete query. Because there are
records for this last group e.g. the form has not been deleted I can cascade
the records from Year 10 to Year 11, Year 9 to Year 10 etc and that seems to
work using find and replace in Edit with the criteria Find 07 (any part of
the field) Replace with 08.

What I'm trying to do is find a function or VBA to carry out the same task
using a button to carry out this procedure (properly passworded of course).

Sorry if I haven't explained it very well but the basics are child moves
from one year to the next. All records for that child must remain with them
until they leave.

Steve

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Jeff Boyce]

Steve

I'm not there, nor thee, so I don't know about your subject area.

I do know that you have alternatives to physically moving records (append to
another table, delete from first).

I don't have any idea what information about the students you feel it is
necessary to move around, rather than referring to.

Can you provide a table design description (tables, fields,
description/example of what's in those fields)?

 

[steve walker]

I'm in a school and need to provide data on students.

tblPUPIL (PupilID, Surname, FirstName, Form, Class, Year_Group)

Data in this table is (0001, Smith, Greg, 07A, 07A/In1, 7)

For example a pupil would start school in Year 7 therefore 07A is the Form, 07A/In1 is the Class, 7 is the Year Group. As they move into the next Year the data needs to be changed to 08A as the Form, 08A/In1 as the Class and 08 as the year group.

Pupils move up a year until they leave school. At the end of the final year (Year 11) I archive the records from tblPUPIL. This allows me to use the find and replace function in Acess to move Year 10 to Year 11 without losing and data relationships.

I can use an append/delete query to archive the table but want to find a method of using a macro to carry out the function of Find and Replace e.g. Find all Year 7 and replace with Year 8.

Cheers

Steve


--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Jeff Boyce]

Steve

It sounds like you're pretty well determined to do it the way you've
decided.

If you want to consider another data structure, one that would obviate the
need to do most of what you are trying to do, continue this thread.

I'll bow out here. If you don't get responses, re-post your request, and
I'll leave it alone.

Good luck!

Jeff Boyce
<Access MVP>

 

[steve walker]

Cheers Jeff - Thanks for looking

Steve

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com