Trying to correct a misselling made.... 2

[Askeladden]

I'm trying to correct a misselling made in an access recordset by use of vb code.
Here is my code:

 Dim antallrecords As Integer
  Dim t As Integer
 
  frmRegistrering.datAccess.Refresh
  frmRegistrering.datAccess.Recordset.MoveLast
  antallrecords = frmRegistrering.datAccess.Recordset.RecordCount
  frmRegistrering.datAccess.Recordset.MoveFirst
    
  If antallrecords > 0 Then
    For t = 1 To antallrecords
    
      If frmRegistrering.datAccess.Recordset.Fields("Ansvarlig") = gstrNavnR Then
        frmRegistrering.datAccess.Recordset.Fields("Ansvarlig") = gstrNavn
      End If
      
      Next t
  End If

This is not working as I predicted.
Can someone tell me what I have done wrong, and how I might be able to fix it.
Thanks.

 

[fredericofonseca]

Without having the error you are getting, or the reason why you say it isn't working it is hard to say.

But if you are just changing the value of a field to another then it is faster and easier to just use a SQL "update" directly on the fields you need.

e.g.

update my_tbl set my_fld = "my_new_value" where my_fld = "my_old_value"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[strongm]

Perhaps if you could tell us how you predicted it might work, and how it is failing to work that way, maybe we could help out

 

[vladk]

Hey, you do not use the "t" index in your loop!

 

[johnwm]

Are you missing a MoveNext inside the loop?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[Askeladden]

I am exchanging the value of a field with another value, or that is what I hope to do. The original field (ansvarlig) is a variable with the name gstrNavnR . This value I need to change into what's in the variable gstrNavn .
This is not working as I had planned.
It should move though every record in an access database file, and if its field is gstrNavnR , it should replace it with gstrNavn . This is not happening, and I can't figure out why.
Again my code is:

Dim antallrecords As Integer
  Dim t As Integer
 
  frmRegistrering.datAccess.Refresh
  frmRegistrering.datAccess.Recordset.MoveLast
  antallrecords = frmRegistrering.datAccess.Recordset.RecordCount
  frmRegistrering.datAccess.Recordset.MoveFirst
    
  If antallrecords > 0 Then
    For t = 1 To antallrecords
    
      If frmRegistrering.datAccess.Recordset.Fields("Ansvarlig") = gstrNavnR Then
        frmRegistrering.datAccess.Recordset.Fields("Ansvarlig") = gstrNavn
      End If
      
      Next t
  End If

 

[fredericofonseca]

Have you tried ANY of our suggestions?

If you did it does not appear so, as you still haven't changed your code neither have you given us any errors.

And once again using the direct SQL UPDATE code will be a lot faster.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Askeladden]

Thanks frericofonseca.
Your SQL UPDATE suggestion looked like something that would do the trick, but how would I implement it into my Vb code.
I'm not very experienced with SQL.
My Access file is named Vedlikeholds_Rutiner.mdb, the table's name is Vedlikeholds Rutiner, and the field's name is ansvarlig.

 

[fredericofonseca]

something along the lines

dim conn as adodb.connection
set conn = new adodb.connection

conn.open ....

conn.execute "update my_table set my_field = 'abc'"

Search these forums from ado and execute and you will find plenty of full examples.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Askeladden]

Also, I get no error messages when I run the program. Even when I "step through" the program as it is running all my variables is what they're supposed to be. t get incemented, but rmRegistrering.datAccess.Recordset.Fields("Ansvarlig") in the if statement stays on the first post. Exchanging the
value of the variable in a listbox works. But the Fields in the access file is not updated.

 

[Askeladden]

thanks fredericofonseca.
I will search the forums.

 

[johnwm]

I guess you missed my original post - you still aren't stepping through your recordset

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[Askeladden]

thanks johnwn, any ID on where I should put the . movenext statment, inside the loop, to step through my posts?

 

[Askeladden]

Thank you johnwm. Disreguard the last question, I put the ".movenext" right before the

Next t

and added a ".edit" and a ".update" in the if statement.
Thank again for putting me on the right track.

 

[Askeladden]

It work now Thank to each and every one of you that helped me through this problem. But a star goes to johnwm. Thanks.

 

[johnwm]

You're welcome, and thanks

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts