B
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
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