FIND and replace via a form

[tina hayes]

Hi there

i am looking for a bit of help. I have a database that has a form/VBA front end that has linked tables from a back end DB. it is a restricted Database given out as an MDE file to users. On the main form, there is a combo box called responsible officer this is connected to a column in the back end table called res_off. When a record is entered into the DB you have to choose, from the drop down, a responsible officer for this record. if this certain responsible officer leaves They may have 1000 records against their name which will need to be changed by the User. What I need it to create a form with two combo boxes on it so that in one I can say find all records in the DB that match the name in combo1 and replace them all with the name in combo 2. The user will not have access to the backend table.
A recreation of the Find and Replace function in access I think would work but I don’t really know where to start.
any help will be much appreicated.

many thanks

 

[ghudson]

You have a design flaw with your data. You should assign a hidden primary key [autonumber] to the officers name in one field and the actual text for the officers name in another field. Assign the numeric value [for each officer] to the related records. Then you [the user] will only have to update the officers name once and the update will show for each related record since the autonumber will remain the same, only the officers name in the text field will have to be changed once if and when needed.

 

[Mile-O]

the actual text for the officers name in another field.

Or, to be more precise, the officer's name split into one field for forename and another for surname.

 

[tina hayes]

Hi thankyou for this.

but there will be times when only one instance will need changing and not the whole lot. there could be a reasn that they no longer look after one record. so this unfortunatly wouldn't work.

any other ideas please

 

[Mile-O]

but there will be times when only one instance will need changing and not the whole lot.

The quantity is irrelevant. An UPDATE query will handle as many as is needed.

 

[tina hayes]

iam confused with this last statement sj mcabney

Ghudson was saying that if i attach a numberical vaule to the officers name then all i will need to do is do a change once and all records attached to that officer will change automatically becyuase of the atuonumber.

but i do not want to change them all at once because i need the option to only change one record if necessary for that officer say offcier james has 30 records against his name record number 25 he loses so i will haev to change jis nmae on the one. if i am understanding what ghudson is saying if i change record 25 all records change

 

[ghudson]

I have a database that has a form/VBA front end that has linked tables from a back end DB. it is a restricted Database given out as an MDE file to users. On the main form, there is a combo box called responsible officer this is connected to a column in the back end table called res_off. When a record is entered into the DB you have to choose, from the drop down, a responsible officer for this record. if this certain responsible officer leaves They may have 1000 records against their name which will need to be changed by the User.

You have a design flaw if the user can not change the officer responsible for any selected record using your combo box.

My previous suggestion was to avoid the problem of an officer leaving and you had to re-assign his records to another officer. All you had to do was change the officers name in the First and Last name fields since the officer responsble was tied to an autonumber field. BUT you have to have a seperate table with just the officers data.

If that does not make sense then you will have to post your db so that we can see how you have structured your db. Just strip out the parts we do not need to see and key a few dummy records.