replace old record to new record

guys help me with this!
see this screenies...

will this help you.... :)

Code:

---

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= " & App.Path & "\db1.mdb;Persist Security Info=False;Jet OLEDB:Database Password=pwd;"
    cn.Open

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Table1 ", cn, adOpenForwardOnly, adLockReadOnly

While Not rs.EOF
            if left(rs.fields("from_bus_id"),4)="S001" then  ' ~~~> please try to use table/field names without spaces or you can use underscore
                rs.fields("phasing")="CN"    ' ~~~> new phasing
                rs.update
            end if
    rs.MoveNext
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

---

You have to make some changes to the code.. like the database name , database password , etc... in the above code

Good luck...

thanks for the urgent reply sir! i'll try this!

Ok... :)

it really works sir!

1 more sir, in this code..."If Left(rs.fields("From_Bus_ID"), 4) = "S003" Then"
what if im using 5 or 6 characters example: s0010 or s00100... ca n you please help sir/

anyway, is there possible to use input in textbox instead of coding in program in runtime? like in my previous post...

thanks in advance sir!

it really works sir!

1 more sir, in this code..."If Left(rs.fields("From_Bus_ID"), 4) = "S003" Then"
what if im using 5 or 6 characters example: s0010 or s00100... ca n you please help sir/

anyway, is there possible to use input in textbox instead of coding in program in runtime? like in my previous SS post...

thanks in advance sir!

S003-TPD0005
We can extract the data before - using the below code....
Also, i used txtPhase, which is a text box that contains the old phase...

Code:

---

if left(rs.fields("from_bus_id"),instr(1,rs.fields("from_bus_id"),"-")-1)=txtPhase.Text then

---

i can't understand sir...
can u please explain in one by one because im still noob in VB..hehe

Code:

---

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= " & App.Path & "\db1.mdb;Persist Security Info=False;Jet OLEDB:Database Password=pwd;"
    cn.Open

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Table1 ", cn, adOpenForwardOnly, adLockReadOnly

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

While Not rs.EOF      '~~~ loops through the recordset
          if left(rs.fields("from_bus_id"),instr(1,rs.fields("from_bus_id"),"-")-1)=txtSearch.Text then
               
rs.fields("phasing")=txtNewPhase.Text    ' ~~~> new phasing
                rs.update
            end if
    rs.MoveNext
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

---

txtSearch is the text box which can be used to enter data like S003
txtNewPhase is the text box which can be used to enter new phase like CN

eg: Consider S003-TPD0005

The below code will give S003

Code:

---

left(rs.fields("from_bus_id"),instr(1,rs.fields("from_bus_id"),"-")-1)

---

I hope you understand.... :)

problem solved sir! it works perfectly...
thanks sir!
good day and GOD bless....

Quote:

---

problem solved sir! it works perfectly...
thanks sir!
good day and GOD bless....

---

Happy to hear that... :)

You can mark this thread as RESOLVED

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)