replace old record to new record

[jhaps007]

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

[akhileshbc]

will this help you....

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

[jhaps007]

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

[akhileshbc]

Ok...

[jhaps007]

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!

[jhaps007]

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!

[akhileshbc]

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

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

[jhaps007]

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

[akhileshbc]

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

[akhileshbc]

eg: Consider S003-TPD0005

The below code will give S003

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

I hope you understand....

[jhaps007]

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

[akhileshbc]

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

Happy to hear that...

You can mark this thread as RESOLVED

[si_the_geek]

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