Find & replace a name string in multi memo fields across a record -HELP!

[graham]

I am just building a simple school reports writing dbase - When the
teacher creates a new duplicate record - I want the them to be able to
replace all instances of the pupil's forename with a new pupil's
forename found within multiple memo fields in that duplicate record
only.

Find and replace wants to change all instances of the forename across
the whole table...

any ideas as to how to do this?????

 

[missinglinq via AccessMonster.com]

This is kind of clunky but works, where the memo field is named PMemo.
Essentially you're popping up two InputBoxes to enter the name to replace and
the replacement name, then using the function Replace to do so. This limits
the rplacement to the PMemo control in the current record


Private Sub CustomSearchAndReplace_Click()

Dim Message, Title, Default, MyValue

PMemo.SetFocus

Message = "Enter name to replace."
Title = "Name to Replace" ' Set title.
' Display message, title, and default value.
SearchNameValue = InputBox(Message, Title)

Message = "Enter Replacement Name."
Title = "Name to Replace" ' Set title.
' Display message, title, and default value.
ReplacementNameValue = InputBox(Message, Title)

PMemo = Replace(PMemo, SearchNameValue, ReplacementNameValue)

End Sub

 

[John W. Vinson]

I am just building a simple school reports writing dbase - When the
teacher creates a new duplicate record - I want the them to be able to
replace all instances of the pupil's forename with a new pupil's
forename found within multiple memo fields in that duplicate record
only.

Find and replace wants to change all instances of the forename across
the whole table...

any ideas as to how to do this?????

You can use an Update query, updating just the one single record. Update the
field Memofield to

Replace("[memofield]", "Billy", "William")

or whatever change you need to make.

The fact that you are storing the pupil's name in multiple Memo fields -
rather than, or instead of? - a single text field is worrisome. What are these
memos?

John W. Vinson [MVP]

 

[graham]

I am just building a simple school reports writing dbase - When the
teacher creates a new duplicate record - I want the them to be able to
replace all instances of the pupil's forename with a new pupil's
forename found within multiple memo fields in that duplicate record
only.

Find and replace wants to change all instances of the forename across
the whole table...

any ideas as to how to do this?????

Thanks for the ideas - but they only work on one memofield at a time -
where there are up to 12 memofields in the record - any of which may
contain the child's forename.

The childs fore & surname are recorded in a text fields separately.

But say - in maths - a record memo field may say "John has worked well
in maths this year." - in the english - the memofield may say " I have
been pleased with John's progress in English" and so on for all
subjects..When the teachers creates a new record for a new pupil from
a duplicate - they need to be able to replace the instance of
"john(s)" across all the new record's memo fields with another child's
name.

Thinking caps on - I just can't think of a work around!

Many many thanks!

 

[John W. Vinson]

Thanks for the ideas - but they only work on one memofield at a time -
where there are up to 12 memofields in the record - any of which may
contain the child's forename.

The childs fore & surname are recorded in a text fields separately.

But say - in maths - a record memo field may say "John has worked well
in maths this year." - in the english - the memofield may say " I have
been pleased with John's progress in English" and so on for all
subjects..When the teachers creates a new record for a new pupil from
a duplicate - they need to be able to replace the instance of
"john(s)" across all the new record's memo fields with another child's
name.

Thinking caps on - I just can't think of a work around!

If you have one memofield for English, and a second memofield for Maths, and a
third memofield for History...

Your database needs normalization.

Consider a one to many relationship from a table of Students to a table of
Comments, with fields StudentID, CommentDate, Subject, CommentorID, and
Comment (the memo field). You can then add as many *records* of comments as
needed for any student - and update them in one go.

With your current design you can still do it in one go; update English to

Replace([English], "John", "Sally")

and update Maths to

Replace([Maths], "John", "Sally")

Better, you can link to a template document in Word and just fill the name
into a boilerplate text.

John W. Vinson [MVP]