Link to home
Start Free TrialLog in
Avatar of mtrussell
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
Avatar of kmslogic
kmslogic
Flag of United States of America image

do this:

rst.Edit
Me.AccountStrip.Value = z
rst.Update
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of mtrussell
mtrussell

ASKER

Thanks