Find/Replace

[bravofoxtrotuk]

Access 2003 - I'm struggling with VBA to find/replace entries across an
entire table. I have a form which is used to change a surname in
tblPersonnel, where say, a typing error is found after some time. I then
want to propogate that same change into another table - tblMatchDay, which
holds the data for which players played in what position in each match (one
row per match). This means that there will be up to 18 'player name' fields
on each row to check looking for the selected player's name to update. I can
do it from the menus by selecting the entire tblMatchDay and doing a simple
find/replace, but I don't want users to have to do that. I've tried using
recordsets and can assemble the SQL using variables for the field name such
as 'F_PlayerName_1' but found I needed to repeat the code 18 times to do it
in the do loop, as I couldn't figure how to loop through the field names
(PlayerName_1, PlayerName_2 etc ) using variables in the 'Debug.Print
rstZ!F_PlayerName_1' line below (which is just for testing) to select the
right fields.


examples of code where sql is explicit, not using variables:
'strOriginalPersonnelName is the name being searched for

Dim rstZ As DAO.Recordset
qqqq = """"

strSQL = "SELECT tblMatchDay.F_PlayerName_1 " & _
"FROM tblMatchDay " & _
"WHERE (((tblMatchDay.F_PlayerName_1)=" & qqqq & strOriginalPersonnelName &
qqqq & "))"

Set rstZ = DBEngine(0)(0).OpenRecordset(strSQL)
'
Do Until rstZ.EOF
Debug.Print rstZ!F_PlayerName_1 <<<< can't use variable ??
' new name goes here if match found using if ... then
Loop


Also tried using Docmd.Findrecord, but can't see how to put the new value
into the field, then do find next. Hope this makes sense! Can anyone help?

Bob
UK

 

[John Spencer]

Dim i as Long

For i = 1 to 18
strFieldName = "F_PLayerName_" & i

strSQL = "SELECT " & strFieldName & _
" FROM tblMatchDay " & _
" WHERE " & strFieldName & =""" & strOriginalPersonnelName & """"

....

Next i

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ryguy7272]

You can user replace like this:
http://www.techonthenet.com/access/functions/string/replace.php
http://www.rickworld.com/download.html

Or, just use an Update Query.
http://office.microsoft.com/en-us/access/ha100765271033.aspx

HTH,
Ryan--

 

[Pendragon]

If you have tblPersonnel using a primary key (as autonumber, like
PersonnelID), then you can set your other tables as relationships to
tblPersonnel and simply link the PersonnelID. Your forms control what the
user sees (name info only) but the form edits and displays data based on the
Personnel ID and not how the name is spelled.