Recordset cycle problem

[free_style]

Hello friends,

I've created a database with lots of code behind it.
There was not any problem in testing mode, but I have problem now.
The problem is concerned to one cycle which doesn't reaches the end of function and stop the whole function.

The cycle is:
Public Function CommentsToReport_Assessment_Company()
DoCmd.RunSQL "DELETE * FROM Create_Report_Assessment_Company_Comments_Temp;"
DoCmd.RunSQL "INSERT INTO Create_Report_Assessment_Company_Comments_Temp ( RiskNr, Department, Comment ) SELECT Create_Report_Assessment_Company_Comments.RiskNr, Create_Report_Assessment_Company_Comments.Departme nt, Create_Report_Assessment_Company_Comments.Comment FROM Create_Report_Assessment_Company_Comments;"

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb()


Set rs = Db.OpenRecordset("Create_Report_Assessment_Company _Comments_Temp")

Dim RiskNr As String
Dim Dept As String
RiskNr = ""
Dept = ""

Do While Not rs.EOF

If rs![RiskNr] = RiskNr Then
DoCmd.RunSQL "UPDATE Create_Report_Assessment_Company_CommentsReady_Tem p SET Comment = '" & Dept & "<br />" & "<u>" & rs![Department] & "</u>:" & rs![Comment] & "' WHERE RiskNr = '" & rs![RiskNr] & "';"
RiskNr = rs![RiskNr]
Dept = Dept + "<u>" & rs![Department] & "</u>:" & rs![Comment] & "<br />"
Else
Dept = ""
DoCmd.RunSQL "INSERT INTO Create_Report_Assessment_Company_CommentsReady_Tem p (RiskNr,Comment) VALUES ( '" & rs![RiskNr] & "' , ' <u>" & rs![Department] & "</u>:" & rs![Comment] & "');"
RiskNr = rs![RiskNr]
Dept = Dept + "<u>" & rs![Department] & "</u>:" & rs![Comment] & "<br /> <br />"
End If

rs.MoveNext

Loop

MsgBox "Done!"

End FunctionThe above is the code. This is a function which I call from another function.

I put 407 records in the recordset. The cycle starts and after 37 times it stops. I saw that if I comment a code, I mean to remove the SQL code there is not problem, so the problem is with it.

Do you have any ideas what can cause the problem?

Thank you in advance,
Best wishes

[orange]

It seems you have a space in the middle of Temp --> Tem p

DoCmd.RunSQL "INSERT INTO Create_Report_Assessment_Company_CommentsReady_Tem p (RiskNr,Comment)

You can insert some Debug.print statements to see exactly what some of the SQL statements are before you run the code.

[rpeare]

if orange's suggestion does not work can you post a sample database with garbage data that can duplicate the error. Without seeing the dataset and looking at where it's breaking down and why it's going to be very difficult to tell you what's wrong.

[free_style]

Hello guys,

I solved the problem!

It wasn't the problem with Tem p. That was because of copying from the VBA to the forum directly. In the original version of the code is Temp.

However, the problem was that I import data (Comments) from one Excel file to Access and then work with them. Anyway, there was happened an SQL injection, caused by the comments. In several of theme there was ' , because something like this: "I haven't been in Paris!". This apostrophe (quote) it happens SQL injection. It was really hard to define this such as problem.

It is not possible to validate the date while entering, so I get the values from rs![Comment] for every record in the recordset and put it into variable. After that I use REPLACE function to replace all the forbidden characters, such as ' (you may change it with double quote - '' ) or SELECT, UPDATE, etc. You know this expressions....

Thank you for your fast answers and for you activity!
Best wishes