mtrussell
asked on
Update Table using Replace Function
I have a table called accounts. I want to strip out characters from an account table to leave just the numeric numbers without certain characters. I have a form and a button and what I would like to do on a double click is update all the account numbers and in another field called accountscript put the value of the account without the various characters.
I have the following but it is not looping through the records. How would I be able to udate the table with the new value using a simple do statement and replacing the characters?
Dim DB As Database
Dim Rst As DAO.Recordset
Dim q As String
Dim x As String
Dim y As String
Dim z As String
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("accounts ", dbOpenDynaset, dbSeeChanges)
Do
q = Me.[Security Account No].Value
x = Replace(q, "-", "")
y = Replace(x, "/", "")
z = Replace(y, ".", "")
Me.AccountStrip.Value = z
Rst.MoveNext
Loop Until Rst.EOF
I have the following but it is not looping through the records. How would I be able to udate the table with the new value using a simple do statement and replacing the characters?
Dim DB As Database
Dim Rst As DAO.Recordset
Dim q As String
Dim x As String
Dim y As String
Dim z As String
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("accounts
Do
q = Me.[Security Account No].Value
x = Replace(q, "-", "")
y = Replace(x, "/", "")
z = Replace(y, ".", "")
Me.AccountStrip.Value = z
Rst.MoveNext
Loop Until Rst.EOF
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why are you doing this:
Me.AccountStrip.Value = z
?
Do
q = Me.[Security Account No].Value
x = Replace(q, "-", "")
y = Replace(x, "/", "")
z = Replace(y, ".", "")
RST.Edit
RST![AccountStrip] = z ' not Me .....
RST.Update
Rst.MoveNext
Loop Until Rst.EOF
Me.AccountStrip.Value = z
?
Do
q = Me.[Security Account No].Value
x = Replace(q, "-", "")
y = Replace(x, "/", "")
z = Replace(y, ".", "")
RST.Edit
RST![AccountStrip] = z ' not Me .....
RST.Update
Rst.MoveNext
Loop Until Rst.EOF
ASKER
Thanks
rst.Edit
Me.AccountStrip.Value = z
rst.Update