Solved

Error 3021 message

Posted on 1998-01-07
9
548 Views
Last Modified: 2010-08-05
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

0
Comment
Question by:kpierce
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:chapie
ID: 1964733
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
 

Author Comment

by:kpierce
ID: 1964734
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
 
LVL 1

Accepted Solution

by:
BBC earned 100 total points
ID: 1964735
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
 
LVL 5

Expert Comment

by:cekman
ID: 1964736
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:kpierce
ID: 1964737
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
 
LVL 3

Expert Comment

by:chapie
ID: 1964738
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
 
LVL 1

Expert Comment

by:BBC
ID: 1964739
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
 
LVL 3

Expert Comment

by:chapie
ID: 1964740
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
 

Author Comment

by:kpierce
ID: 1964741
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now