Error 3021 message

This is sort of a followup question to one that I posted about a month ago.  I've written a function that populates an empty field in a table that is on the "many" side of a "one to many" relationship.  It looks at the ID field in that particular table (which can and does have duplicate values), and numbers them from 1 to the last # of records for the value in that ID field, moving to the next record along the way.  The resulting table looks something like this:

ID     Sort #
1054    1
1054    2
1054    3
1063    1
1063    2
1066    1

My code works fine, and the field is populated correctly, except that I seem to get an error 3021 message when it gets to the end of the table.  I suspect that it has something to do with a .MoveNext statement that pushes it beyond where there is a current record.  I've tried including an "if err = 3021 then exit do statement" to check for this (within both loops), but continue to get the message.

I'd really appreciate any help that I could get with this.  The code for the function follows:

Function HostHistIndex()
         
    Dim dbs As Database
    Dim rst As Recordset
    Dim idval As String
    Dim ct As Integer
    Dim retval
   
   
   
   
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("hosthist")
    ct = 1
   
   
    With rst
        'Populate the recordset
        .MoveLast
        .MoveFirst
       
    'initialize a variable to hold the hostid
    idval = Val(!hostid)
       
    Do While Not .EOF
        ct = 1
       
        Do While idval = !hostid And Not .EOF
       
            .Edit
            !indexno = ct
            .Update
            ct = ct + 1
            .MoveNext
            Loop
           
            idval = !hostid
        Loop
    .Close
    End With

dbs.Close

End Function

kpierceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapieCommented:
first thing I would ask is why are you looping with the same requirement 2 times

do while not .eof
and
Do While idval = !hostid And Not .EOF
the second .eof seems redundant
as does the
.movelast
.movefirst
are they both necessary??

have you thought of putting in a simple error trap for this 3021 message
0
kpierceAuthor Commented:
I added the 2nd .eof as one of the last things that I tried to escape the loop.  It won't be in the final code (I forgot to remove it before I posted the question).

Yes, I've tried adding a 3021 trap.  I did this as follows:

Do While Not .EOF
 ct = 1
 
 Do While idval = !hostid And Not .EOF
 
 .Edit
 !indexno = ct
 .Update
 ct = ct + 1
'3021 error trap, which I deleted
'----------------------
   if err <> 3021 then
     .MoveNext
   else
     exit do
   end if
'----------------------
 Loop
 
 idval = !hostid
 Loop
 .Close
 End With

dbs.Close


The above however, didn't work.. I continued to get the message.  I'm wondering if the trap is placed incorrectly.  Any help would be greatly appreciated.

Thanks.
0
BBCCommented:
Perhaps it works with a bookmark like this:
-------------------------------------------

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDB()
Set rst = dbs.OpenRecordset("sohalt")
                         
rst.MoveLast
                         
theEnd = rst.bookmark

rst.MoveFirst

Do While Not rst.bookmark = theEnd
  Debug.Print rst.lala
  rst.MoveNext
Loop

rst.Close
dbs.Close


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

cekmanCommented:
The bookmark idea is a great idea, and would work.  I now keep getting a data type mismatch message, however, when I try to compare the current position with the position of the last record (this is declared as a variant data type).  Any ideas?

My code looks like this:

dim theEnd as Variant

with rst
.move last
theEnd = .bookmark

...
'debugging shows the following line as the problem:
do while Not theEnd = .bookmark  
   Blah, blah, blah.
Loop


0
kpierceAuthor Commented:
I think the comment about changine the Do While Not to Do Until will fix the problem becuase the first instance while check the cursor position when it reaches the .EOF, where the second instance some how stops before the error instance (I believe)
0
chapieCommented:
Funny, in Access 2 it works. But not in Access 97. But I solved it ;-)


Make a new function:

Function sameBM(in1, in2)
 sameBM = (in1(0) = in2(0)) And (in1(1) = in2(1)) And (in1(2) = in2(2)) And (in1(3) = in2(3))
End Function


And for the loop use :

Do Until sameBM(rst.Bookmark, theEnd)
  Debug.Print rst.lala
  rst.MoveNext
Loop

---------------------------------------------------------

Could someone tell me, why M$ changed this ? AGAIN they didn't test enough ... ARGH !!!!

0
BBCCommented:
BBC .... just out of personal curiousity, whats the 'theEnd' portion in this code??

Do Until sameBM(rst.Bookmark, theEnd)


Kpierce ...your error trap is missing something

'3021 error trap, which I deleted
'----------------------
if err <> 3021 then
 .MoveNext
else
 exit do
end if
'----------------------

in order to capture the error (then analyze it), you must have a line of code (typicaly added at the beginning of the function)

On Error Goto Err_Section

then at the bottom of the sub or function (must be placed between an Exit Sub, and End Sub, like the following)


Function CauseError()
On Error goto Err_CauseError

msgbox Prompt:="Something Blah Blah"

exit function
Err_CauseError:
select case err.number
case 3021 'can't goto record
  exit function
case else
  msgbox Prompt:="The following error occurred" & vbcr & vbcr & _
     err.number & " - " & err.description
end select
end function
0
chapieCommented:
Thanks SO much for the help.  I ended up using the function that BBC wrote.  It took me to the last record, but the loop seemed to end in the previous record, and didn't enter a value in the field that I needed.  For this, I ended up hardwiring a .moveprev, writing the value of the first field to a variable, doing a .movenext, and comparing the same field in the last record to enter a number in the field that I needed.

I'm curious, BBC.. what does the sameBM function do?  (I'm a relative newbie, so forgive the sophomoric question).

Again, many thanks for your help on this.  It's working.
0
kpierceAuthor Commented:
In the object-browser Access shows me, that the bookmarks are 4 bytes. I couldn't compare two variables containing bookamrks. Don't ask me why, should have worked. So I looked for a way to compare every single of the 4 bytes with the other bookmark.
So it checks byte 1 : in1(0) = in2(0)
and then the second : in1(1) = in2(1) ...
And only returns TRUE if all the bytes are the same :
(.0. AND .1. AND .2. AND .3.)

I didn't realyy understand what you did in your last step and why it ended one before the end. Are you sure your .movenext is the last command in the loop ?

chapie:
theEnd should be the bookmark of the last record. Look in code above.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.