Solved

Error 3021 message

Posted on 1998-01-07
9
546 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kpierce
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

762 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

12 Experts available now in Live!

Get 1:1 Help Now