Replace, recordsets and Null values (1 Viewer)

[jubb]

I am trying to create a DB that fixes some common data problems in a DB created by an app purchased by my employer.

I need to check one field of a table and make multiple alterations. I have tried the following code but am getting an improper use of null error when the code is running.

Private Sub cmdReplacer_Click()
    Dim rs1 As Recordset
    
    Set rs1 = CurrentDb.OpenRecordset("Grades")
    
    rs1.MoveFirst
    While Not rs1.EOF
        rs1.Edit
        rs1!Comments = Replace(rs1!Comments, "co-oper", "cooper")
        rs1.Update
        rs1.MoveNext
    Wend
    MsgBox "All instances of co-oper changed to cooper", vbOKOnly, "Check 1 complete"
    rs1.Close
End Sub

This is the first of many different replaces I have to do on the records in the comments field. (for checking school report comments)

I know once I get this working I will be able to complete all the checks I would normally do manually (just opening the TABLE in access and runnig find/replace a number of times) but I am going on leave and others are going to have to check this information so I am trying to make it easier as most of the other staff at my work wouldn't have a clue when it comes to access.

Any help would be greatly appreciated.

Thanks
Jubb

PS. I have also tried using an If isnull(rs1!comments) then... in the loop but that causes another problem. Also I am using link tables to access the main DB.

 

[workmad3]

I think the recordset might not be updateable, or perhaps access won't let you change data values like that

In any case, I think an update query would be better suited in this instance.

"UPDATE Grades SET Comment = 'cooper' WHERE Comment = 'co-oper'"

 

[jubb]

Hi,

Thanks for the quick reply, I had thought about using an update query but there are so many different changes that have to be made to the data it would require alot of queries. (The data is entered by 100 different staff members and all the changes have to be made to suit the current boss' tastes)

I have worked out the problem though, the data in the main DB had become corrupted. Just replaced the DB with a backup copy and the code worked fine.

Thanks for the suggestion though.

Jubb

 

[workmad3]

Even so, I would suggest update queries, but perhaps run them through VBA code, e.g.

CurrentDB.Execute "UPDATE Grades SET Comment = 'cooper' WHERE Comment = 'co-oper'"

will do what those ten lines of code of yours did

 

[jubb]

Thanks for the suggestion but as it is only part of the field that is being changed I would have to use like "*co-oper*" etc...
or I think i would anyway.

As I said there are approximately 200 different changes our picky boss wants made to the comments accross the board and it doesn't matter how many times I tell the staff what these changes are they continue to make the same mistakes. So as you can see the one line of code would turn into 200 lines of code or a very complex update query.

the code now looks like this:

Private Sub cmdReplacer_Click()
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    
    Set rs1 = CurrentDb.OpenRecordset("Grades")
    Set rs2 = CurrentDb.OpenRecordset("tblChanges")
    
    rs1.MoveFirst
    
    While Not rs1.EOF
        rs2.MoveFirst
        rs1.edit
        Do While Not rs2.EOF
            rs1!comments = Replace(rs1!comments, rs2!changefrom, rs2!changeto)

            rs2.MoveNext
        Loop
        rs1.Update
        rs1.MoveNext
    Wend
    MsgBox "Listed changes completed", vbOKOnly, "Changes Complete"
    rs1.Close
    rs2.Close
End Sub

Once again thank you for your suggestions to my problem.

I think I am doing this the most efficient way now due to the number of changes. Given this is only one stage of the checking process. I also spell check all the comments (creating a custom dictionary based on the students names) then check all the gender references made in the comments to make sure the teachers are referring to boys as boys and girls as girls.

I have contact the company that developed the original package and they want nearly $500 AUD to supply a package that does all this to the data. Pretty expensive since my solution works perfectly now and only took 3 hours to develop.

Thanks

Jubb