VBA error (3201 no Current Record) (1 Viewer)

[LeagueOneDale]

Hi folks,

New poster here, thou I have found answers in the forum before.

I have an Access 2003 database where I need to take the output from a number of records, format that data into a string and add it to a table for use in a report.

I have attempted the code to do this and researched the web on the error I am getting, which is a '3201 No Current record'. I thought the 'Do While Not rst.EOF' would trap this for me, but it doesn't seem to work the way I thought it did.

I only get the error when the routine reaches the end of the source table.

Here is the code:

Public Function RefLCNBuilder()
Dim db As Database
Set db = CurrentDb
Dim rst, rst1 As Recordset
Dim strSQL, strSQL1 As String
Dim strLCNSTART1, strLCN1, strREF, strLCNPart As String
'SOURCE DATA SET
strSQL = "SELECT QRY_reference_tasks_MPOL_unsched.REFLCNCA as REFLCN, QRY_reference_tasks_MPOL_unsched.LSACONXB AS LCN FROM QRY_reference_tasks_MPOL_unsched ORDER BY QRY_reference_tasks_MPOL_unsched.REFLCNCA, QRY_reference_tasks_MPOL_unsched.LSACONXB"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'COMPILED DATA SET
strSQL1 = "SELECT [REF LCNS].LCN, [REF LCNS].[REF LCNS] AS REFLCN, [REF LCNS].[REF LCNS1] AS REFLCN1 FROM [REF LCNS]ORDER BY [REF LCNS].LCN"
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
If ((Not rst.BOF) And (Not rst.EOF)) Then rst.MoveFirst
'ADD LCNs TOGETHER'
    Do While Not rst.EOF
       strLCNSTART1 = rst!REFLCN
       strLCN1 = rst!LCN
       strREF = rst!LCN
 
       rst.MoveNext
 
       Do While rst!REFLCN = strLCNSTART1 Or Not rst.EOF
          strLCN1 = rst!LCN
          strREF = strREF & ", " & strLCN1
 
          rst.MoveNext
       Loop
 
       rst1.AddNew
       rst1!LCN = strLCNSTART1
       rst1!REFLCN1 = strREF
       rst1.Update
    Loop
 
rst.Close
rst1.Close
Set db = Nothing
 
End Function

Can anyone take a look and advise what I am doing wrong?

 

[spikepl]

You inner loop can step beyond eof with your While condition.

Edit: Actually that's exactly what happens. Fix your logic for the inner loop

 

[jdraw]

I haven't looked at you error message but these do not work the way you think.

Dim rst, rst1 As Recordset
Dim strSQL, strSQL1 As String
Dim strLCNSTART1, strLCN1, strREF, strLCNPart As String

For example this

Dim rst, rst1 As Recordset

creates the following

Dim rst as Variant
Dim rst1 as Recordset

Variant is the default.

Also, I think you should be explicit here

Dim rst as DAO.Recordset
Dim rst1 as DAO.Recordset

I would add some breakpoints, and possibly a couple of counters

and check the values of the counters at various points. Something is getting past EOF -- it appears.

 

[LeagueOneDale]

Thanks for the help folks, a quick tweak to the logic sorted it out. Also thanks for the help on the Dim statements.

 

[MarkK]

A rule of thumb I sometimes use is don't open two recordsets in the same subroutine. Perform some logic in Sub1 and, if required, pass parameters to some Sub2 to work with another recordset. This keeps your code simpler, cleaner, and practically self-documenting.
Cheers,

 

[AussGrid]

LeagueOneDale,

Having the same problem with almost identical coding, what did you change to get the loop to run without error? I have been using something along the lines of:

Do While Not rs.eof

x = rs!x

Do While rs!= x and Not rs.eof

rs.MoveNext
Loop

rs.MoveNext
Loop

Cheers,

 

[spikepl]

your inner loop can put the pointer at EOF and then, without further tests, you have a following .movenext in your outer

 

[AussGrid]

Spikepl,

Still not loving the loops unfortunately;
Currently I have:

Do

x=rs!x

Do while rs!x=x and not rs.eof
[Statements]

rs.movenext
Loop

rs.movenext
Loop

but still returning the 3021.
Have tried several variations on this but no success. Any assistance you can offer would be very helpful.

Many thanks,

 

[smig]

your both .MoveNext refer to rst.
One of them should be refering to rst1.

as lagbolt said you better avoid using two recordsets inside each other.
I prefer saving data into an array and put it into the other rs.

 

[boblarson]

I do use nested recordsets and don't have problems with using them. But the problem comes back in that using loops you have to be very mindful of when you use

Do While

or

Do Until



But AussGrid - post the real code and what it is you are intending because your example makes no sense whatsoever and some of it isn't even correct at all (for example Do While rs!= x is an invalid piece of code. You can't compare rs! to anything. It needs a field rs!Something = x.

 

[smig]

boblarson: I didn't say it's not possible using nester recordsets. but as you also said: you must be more carefull

in any case I think this is the problem

your both .MoveNext refer to rst.
One of them should be refering to rst1.

again, an example of the risk of using nesed recordsets

 

[AussGrid]

Well I'm cheating a bit as really this is really being designed for Excel but I've found no solutionson nested queries like this anywhere.

The procedure I'm trying to carry out is pretty simple in theory, looping through the recordset and grouping result so that when the value in a field changes, the macro skips a couple of rows and moves on. I thought this requires a nested loop, but how do you compare one field in a recordset with the previous? Declare a variable outside of the inner loop which holds the value until it changes. This causes the inner loop to break and the variable is reassigned a new value and the inner loop starts over etc. But it gave me the 3021 error

However I’ve found a solution using ‘if’ functions which works very efficiently. Thank you for the help; I guess making mistakes is part of the learning process.