Link to home
Start Free TrialLog in
Avatar of kpierce
kpierce

asked on

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

Avatar of chapie
chapie

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
Avatar of kpierce

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of BBC
BBC

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


Avatar of kpierce

ASKER

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)
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 !!!!

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
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.
Avatar of kpierce

ASKER

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.