Change data

[bone head]

Hi

quite a simple one here i think, but thought i would seek advice before i cocked anything up.

I have a database with a feild which carries a specific No to donate which location a site ie 37b could mean that the location is London.

Like all Good organisations, just as you sort something out they decide to change everything. they now decide that London will be 3076 for example

What i need to do is to change all 35b's for example into 3076 at each location, and also change any future instances where an operator may inadvertanly use the old code No.

I would guess i need some visual basic code to do this,

Any suggestions or sample code most welcome


TIA

Bone Head

 

[Fornatian]

In the first instance use an update query to update all your records with 37b to the new reference. My instincts would say to then remove 37b from the reference table where the code is selected. If you can't do this you should run the update query now and again to update the reference to the new code.

If this occurs for many locations, you could add an extra field to your location lookup table to hold the new codes then use that as the update value. Be careful that you only select records for the update query where a new code exists to prevent records with no 'new code' being updated to nothing and thus losing the old codes.

You don't need VBA to do this, just sit back and think of the frequency of how often you need to do this and how to prevent/manage it in future.

 

[bone head]

Many thanks.

As i thought, i was going off in the wrong direction, this clarifies quite a bit, and a lot more simple than i thought.

Cheers