Recordset loop breaks

[davesmith202]

I have a code loop where I run through some data in one recordset and push the data into another recordset. Part of the code is below.

I'm getting a No Current Record error when it hits the rs3.Edit code. Is that because I have done AddNew, Update but the current record still doesn't exist?

            If InStr(rs2!Data, "[") > 0 Then

                rs3.AddNew
                rs3.Update
                '
                '
                Do While (Len(rs2!Data > 0)) And (InStr(rs2!Data, "[") > 0) And (Not rs2.EOF)
                    If InStr(rs2!Data, "[Date ") > 0 Then
                        rs3.Edit
                        rs3!Data = rs2!Data
                        rs3.Update
                    ElseIf InStr(rs2!Data, "[Event ") > 0 Then
                        rs3.Edit
                        rs3!Data = rs2!Data
                        rs3.Update
                    End If
                    rs2.MoveNext
                Loop
                
                End If

 

[vbaInet]

First thing you do is: Do While Not rs2.EOF

Then... You check IF Len() and Len()

Also, you're not moving to the next record for rs3.

 

[davesmith202]

But it has to meet all three conditions to loop and not just the "Not rs2.EOF" condition.

My error comes after that Do While line of code.

 

[vbaInet]

Just try what has been suggested. Allow the loop to flow as usual and use an IF check.

 

[davesmith202]

Do you mean like this?

            If InStr(rs2!Data, "[") > 0 Then

                rs3.AddNew
                rs3.Update
                '
                '
                Do While  (Not rs2.EOF)
                 if (Len(rs2!Data > 0)) And (InStr(rs2!Data, "[") > 0) then
                    If InStr(rs2!Data, "[Date ") > 0 Then
                        rs3.Edit
                        rs3!Data = rs2!Data
                        rs3.Update
                    ElseIf InStr(rs2!Data, "[Event ") > 0 Then
                        rs3.Edit
                        rs3!Data = rs2!Data
                        rs3.Update
                    End If
                    rs2.MoveNext
                    end if                
                 Loop
                
                End If

 

[vbaInet]

That's it, but put the rs.MoveNext outside the IF block.

 

[davesmith202]

There is no change - still the same error. I didn't actually follow your logic on why that code mod would make any difference. To me, it seems that if you have to have 3 conditions met to do the loop, it won't make any difference putting one of them on its own line as suggested, unless I am missing something here.

I am wondering if it is because I have done an AddNew and an Update but without any data being added. So when it comes to do an Edit, it says, "Hey, no record here since your Update was ignored due to no data."

Your thoughts?

 

[vbaInet]

In other programming languages, if you had "Not rs.EOF" before the other conditions, if it finds that the first condition is not met it will not evaluate the others but in vba it would still evaluate all three. I think the term is eager evaluation.

What is this bit in your code at the top?
If InStr(rs2!Data, "[") > 0 Then

Is there any other place where rs2.Movenext or Movelast or MoveFirst or move anything is happening?

 

[davesmith202]

I've noticed a typo in the statement if (Len(rs2!Data > 0)). That should be if (Len(rs2!Data) > 0)!!!

So, lets see what that brings!

 

[davesmith202]

That solved it. Tiny typos are a right pain! Thanks for your help in any case.

 

[vbaInet]

Great! Glad you got that sorted.

By the way, why not move that evaluation to the query in the recordset instead?

 

[davesmith202]

Not sure what you mean? Do you mean create the whole query that has if statements in it?

 

[vbaInet]

You can use Len() and Instr() in the WHERE condition of rs2's recordset query. It will be faster limiting the records there.

 

[davesmith202]

Well, initially when I was doing everything by importing my data through a text file, going line by line, it took 4 minutes to import a smallish file. Then I streamlined it and cut it down to 20 seconds by using a mod with openevents.

That was still too slow due to the file sizes I am working with. So I am trying transfertext and I think it is plenty fast enough now. Just gotta see the end result to see if its good enough.