Replace field content

[DavidW]

I am trying to use a form to check and replace an existing number in a field
from a table.
This doesnt happen often but what I am doing is that if a fields number
equals whats been put in text1(say "10"), replace the number with whatever
is placed in text2 and continue to the next record until the end of all
records.

how would you phrase the "for each" statement to go through each record in a
table and check to see if it matches and if it does, replace that value.

 

[Ronald W. Roberts]

I am trying to use a form to check and replace an existing number in a field
from a table.
This doesnt happen often but what I am doing is that if a fields number
equals whats been put in text1(say "10"), replace the number with whatever
is placed in text2 and continue to the next record until the end of all
records.

how would you phrase the "for each" statement to go through each record in a
table and check to see if it matches and if it does, replace that value.

If this is a bound form that only you use then you could do something
like this. This could be a command
button. With this code you will get an error when the program tries to
move past the EOF.

If this form is going to be used by others, then you have more work to do.

StartRtn:
if Me!Text1="10" then
Me!Test1 =Me!Test2
endif
DoCmd.GoToRecord , , acNext
go to StartRtn

Exit sub

This can be done in a unbound form with 1 text box and 1 command button.
dim dbs as database
dim rs as recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("MyTable")
Do Until rs.EOF
if rs!Field1="10" then
rs.edit
rs!Field1=Me!Text2
rs.Update
endif
Loop

exit sub

It sounds like you are going to run thru the table and change a field
from 10 to something
else. If this is the case, you can also use and update query.

This is all air code.

Ron

 

[Kelvin]

Create an upddate query to do the conversion and link this to a command
button on the form.

If not isnull(txtText1) then DoCmd.RunQuery qryUpdateQuery

or run everything directly from the button.

If not isnull(txtText1) then
strSQL = "UPDATE tblNAme SET tblName.[Field1] = '" & Me.txtText2 & "'
WHERE (((tblName.Field1)= '" & txtText1 &"';"
DoCmd.RunSQL strSQL
end If

Kelvin