How to Find and Replace a value in a field using 2 Textboxes

[neelsfer]

I have two text boxes in a form:
Old_Value
New_Value

I want to use the recordset method or whatever is the best, to replace multiple rows (in tblClientmeds) based on the "Old_Value" textbox (FIND) , with a value in the "New_Value" Textbox (REPLACE) using VBA.
My table - tblClientmeds
My field - IdNo

Any suggestions?

[Seth Schrock]

If Old_Value contains the exact value you are searching for and New_Value contains the entire text you want stored, then an update query would be easiest. Based on the field name of IdNo, I'm going to guess that it is a number field. So the query would be close to this:

UPDATE tblClientmeds SET IdNo = Forms!your_form!New_Value
WHERE IdNo = Forms!your_form!Old_Value

[neelsfer]

thx Seth. I have patients at hospitals that are admitted on emergency Identity numbers based on birth dates. When the patient's own Identity booklet with a unique IdNo arrives hours later, then the IdNo has to be changed in 2 tables based on the new IdNo - tblCient and tblClientMeds. I will try your solution and let you know

[neelsfer]

Its working fine Seth. Will use it. Thx for trouble