Access 2000 to SQL2000 Access FE problems

Posted on 2006-04-27
Last Modified: 2008-02-01
I moved a application that was in Access backend and a Access FrontEnd to SQL 2000. I have been running into all kind of problems on the frontend. The current one I am stuck in is. Run-time error '3021' No Current Record. and it highlights :

'Calculates Owner Numbers for Active Filing & Non-Filing Owners
    Dim db As Database
    Dim rsf As Recordset
    Dim rsn As Recordset
    Dim rsc As Recordset
    Dim rso As Recordset 'rsf = filing owners, rsn = non-filing owners, rsc = Canadian Letters
    Dim F, N As Integer  'F= # filing owners, N = # non-filing owners
    Dim qdf As QueryDef
    Dim qdn As QueryDef
    Dim qdc As QueryDef
    Dim qdo  As QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryHomesteadActiveFilingOwners")
    Set qdn = db.QueryDefs("qryHomesteadActiveNonFilingOwners")
    Set qdc = db.QueryDefs("qryOwnersNeedingCanadianLetters")
    Set qdo = db.QueryDefs("qryHomesteadActiveFilingOwnersOwnerData")
    qdf(0) = Me!HomesteadID
    qdn(0) = Me!HomesteadID
    qdc(0) = Me!HomesteadID
    qdo(0) = Me!HomesteadID
    Set rsf = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set rsn = qdn.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set rsc = qdc.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set rso = qdo.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    'Sets initial value of owners & turns on hourglass
    F = 0
    N = 0
    DoCmd.Hourglass True
    'Runs process for Filing Owners
    If Not (rsf.EOF And rsf.BOF) Then
    Do Until rsf.EOF
        F = F + 1
        rsf!OwnerNum = F
    Me!NumFilingOwners = F
    DoCmd.RunCommand acCmdSaveRecord
    'Runs process for Non-filing Owners
        rsn.MoveFirst<---STOPS RIGHT HERE.
        Do Until rsn.EOF
            N = N + 1
            rsn!OwnerNum = N
         Me!NumNonFilingOwners = N
    DoCmd.RunCommand acCmdSaveRecord
Question by:ircpamanager
    LVL 5

    Author Comment

    LVL 119

    Expert Comment

    by:Rey Obrero
    try checking first if rsn recordset is not empty

        'Runs process for Non-filing Owners

        if  rsn.eof =false then        ' add this line
            rsn.MoveFirst<---STOPS RIGHT HERE.
            Do Until rsn.EOF
                N = N + 1
                rsn!OwnerNum = N

       end if                   ' add this line

    LVL 44

    Expert Comment

    can you open your Access MDB, go to the Queries tab, and Execute the query


    does it return records?

    LVL 44

    Expert Comment

    do you know for a fact that there are HomesteadActiveNonFilingOwners (that the number of records to be returned is in fact >0)?

    LVL 5

    Author Comment

    No there are no records in the query, on the original it does not give me a error at this point on same record.
    LVL 44

    Expert Comment

    if there are no records to be counted, then capricorn1 has the anser - and the equivalent test should be applied to each of the blocks - skip the block (and set the value to 0) if there are no records to be counted.

    LVL 54

    Expert Comment

    The already coded test:
    If Not (rsf.EOF And rsf.BOF) Then
    is the way Microsoft describes as the safe way to test for no records.
    Just use this statement for all other .movefirst statements as indicated by Arthur_Wood.

    LVL 5

    Author Comment

    now it will not move past.

     'Variables used for each status flag
        'The number af the "S" in the variable matches the status type
        Dim S1 As Integer  'Completed
        Dim S2 As Integer  'Missing Information
        Dim S3 As Integer 'Denial
        Dim S4 As Integer  'Approved
        Dim S5 As Integer  'Research
        'Starts each status variable at zero
        S1 = 0
        S2 = 0
        S3 = 0
        S4 = 0
        S5 = 0
    'Calculates if each Exemption Type is used on the Application
        'by updating invisible form sfrmHomesteadExemptions
        'and thus saving data to tblHomesteadExemptions
        'First resets form so all exemptions are "NO"
        Me!sfrmHomesteadExemptions!Homestead = 0
        Me!sfrmHomesteadExemptions!Widow = 0
        Me!sfrmHomesteadExemptions!Widower = 0
        Me!sfrmHomesteadExemptions!Disability = 0
        Me!sfrmHomesteadExemptions!Veterans = 0
        Me!sfrmHomesteadExemptions!Blind = 0
        Me!sfrmHomesteadExemptions!Quad = 0
        Me!sfrmHomesteadExemptions!ServiceCon = 0
        Me!sfrmHomesteadExemptions!ServiceConTP = 0
        Me!sfrmHomesteadExemptions!Wheelchair = 0
        Me!sfrmHomesteadExemptions!TotalPerm = 0
        Me!sfrmHomesteadExemptions!Senior = 0
        Me!sfrmHomesteadExemptions!Partial = 0
        'Updates fields based on Active Filing Owners
        Do Until rsf.EOF
            If rsf!HomeExempt = -1 Then
                Me!sfrmHomesteadExemptions!Homestead = -1
            End If
            If rsf!WidowExempt = -1 Then
                Me!sfrmHomesteadExemptions!Widow = -1
            End If
            If rsf!WidowerExempt = -1 Then
                Me!sfrmHomesteadExemptions!Widower = -1
            End If
            If rsf!DisableExempt = -1 Then
                Me!sfrmHomesteadExemptions!Disability = -1
            End If
            If rsf!VeteranExempt = -1 Then
                Me!sfrmHomesteadExemptions!Veterans = -1
            End If
            If rsf!BlindExempt = -1 Then
                Me!sfrmHomesteadExemptions!Blind = -1
            End If
            If rsf!QuadExempt = -1 Then
                Me!sfrmHomesteadExemptions!Quad = -1
            End If
    '        If rsf.SvcConExempt = -1 Then
    '            Me!sfrmHomesteadExemptions!ServiceCon = -1
    '        End If
            If rsf!TotSvcExempt = -1 Then
                Me!sfrmHomesteadExemptions!ServiceConTP = -1
            End If
            If rsf!WheelChairExempt = -1 Then
                Me!sfrmHomesteadExemptions!Wheelchair = -1
            End If
            If rsf!TotPermExempt = -1 Then
                Me!sfrmHomesteadExemptions!TotalPerm = -1
            End If
            If rsf!SeniorExempt = -1 Then
                Me!sfrmHomesteadExemptions!Senior = -1
            End If
            If rsf!PartialExempt = -1 Then
                Me!sfrmHomesteadExemptions!Partial = -1
            End If
        'Resets all Print Flags before processing
        'Individual Canadian Flags used to verify Owner
        'Canadian Letter Reports based from Overall Providence Flags
        Forms!frmHomesteadMain!sfrmAppConfig!O1Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Saskatchewan = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Saskatchewan = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Saskatchewan = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Saskatchewan = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Saskatchewan = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Saskatchewan = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1OtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2OtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3OtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4OtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5OtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6OtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1DecDomicile = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2DecDomicile = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3DecDomicile = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4DecDomicile = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5DecDomicile = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6DecDomicile = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Affidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Affidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Affidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Affidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Affidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Affidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1ResAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2ResAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3ResAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4ResAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5ResAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6ResAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1MissingInfo = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2MissingInfo = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3MissingInfo = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4MissingInfo = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5MissingInfo = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6MissingInfo = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O1Research = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O2Research = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O3Research = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O4Research = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O5Research = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!O6Research = "N"
        'Overall Canadian Letter Print Flags
        Forms!frmHomesteadMain!sfrmAppConfig!Alberta = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!BritishColumbia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!Manitoba = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!NewBrunswick = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!Newfoundland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!NovaScotia = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!Ontario = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!PrinceEdwardIsland = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!Quebec = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!Saskatchewan = "N"
        'Overall other reports flags
        Forms!frmHomesteadMain!sfrmAppConfig!PrintOtherCounty = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!PrintDeclarations = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!PrintAffidavit = "N"
        Forms!frmHomesteadMain!sfrmAppConfig!PrintResAffidavit = "N"
        'Sets Print Flags for Canadian Letters for each owner and overall letters
        If Not (rsc.EOF And rsc.BOF) Then<-----------------------------------------------------will not move past here
      #### #I put a msgbox before "If Not (rsc.EOF and rsc.BOF) Then" the msgbox displays. If i put it after it the Msgbox does not display######
        Do Until rsc.EOF
        Dim P As String
        Dim O As Integer
        P = rsc!PropProvidence  'Providence Name
        O = rsc!OwnerNum  'Owner Number
    LVL 54

    Accepted Solution

    That indicates that qryOwnersNeedingCanadianLetters doesn't give rows for the specified HomesteadID.
    Did you check or HomesteadID is filled and that the query will return data ?

    From your code I get the impression that your table(s) aren't properly "normalized". Are you still developing your application ?

    LVL 119

    Expert Comment

    by:Rey Obrero

    try it this way

        If rsc.BOF = True Or rsc.EOF = True Then
            MsgBox ("No records to process")
            Do Until rsc.EOF
             Dim P As String
             Dim O As Integer
              P = rsc!PropProvidence  'Providence Name
             O = rsc!OwnerNum  'Owner Number

       end if


    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

    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…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    755 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

    25 Experts available now in Live!

    Get 1:1 Help Now