Front and back end in same file produces 'No Current Record' error?

[Alc]

Until last week, the front and back end of the application I use were split. They are now in one file (for 'political' reasons ).

Although it worked perfectly beforehand, since the merger, the following code produces an error "Runtime Error 3021: No current record"

        str_Account1 = UCase([Forms]![Referral to BMO]![Account #])
 
        If Me.Dirty Then
            Me.Undo
        End If
 
        Call Prevent_Referral_Entry
 
        'Main Form
        '---------
        Set Rst = [Forms]![Referral to BMO].RecordsetClone
        Rst.FindFirst "[Account #]= '" & [Forms]![Referral to BMO]![Account #] & "'"
        '*****ERROR POINT IN DEBUG *****
        [Forms]![Referral to BMO].Bookmark = Rst.Bookmark
        '****************************
        'Subform
        '-------
        str_Data = "SELECT * FROM NBReferral " & _
                   "WHERE [Account #]= '" & [Forms]![Referral to BMO]![Account #] & "'"
        [Forms]![Referral to BMO]![Referral to BMO Search Subform].Form.RecordSource = str_Data
        [Forms]![Referral to BMO]![Referral to BMO Search Subform].Form.Requery
 
        Exit Sub

The only difference is that now the tables are stored in the database itself, as opposed to being linked. I don't see how that would cause a problem, but I'm open to suggestions.

 

[Galaxiom]

Generally track problems like this by using a variable to hold the FindFirst criteria and make sure it is what you are expecting in the Locals window at a break point or the Immediate window with Debug.Print.

However it appears to me this would always happen on a new record because of the Undo. (Unless the Prevent_Referal_Entry sub does someting about it.)

Also note the code should always check the NoMatch property after a FindFirst before equating the form's bookmark to something that might not be there.

 

[Alc]

Thanks for the answer. They had me working on somethign else yesterday, hence the delay in responding.

I changed the code as follows

        str_Account1 = UCase([Forms]![Referral to BMO]![Account #])
        If Me.Dirty Then
            Me.Undo
        End If
        Call Prevent_Referral_Entry
        'Main Form
        '---------
        MsgBox "1 " & [Forms]![Referral to BMO].Recordset.NAME
        Set Rst = [Forms]![Referral to BMO].RecordsetClone
        MsgBox "2 " & Rst.NAME
        If Not Rst.EOF Then
            Rst.FindFirst "[Account #]= '" & [Forms]![Referral to BMO]![Account #] & "'"
            MsgBox [Account #]
            If Not Rst.NoMatch Then
                [Forms]![Referral to BMO].Bookmark = Rst.Bookmark
            Else
                MsgBox "None"
                Exit Sub
            End If
 
            'Subform
            '-------
            str_Data = "SELECT * FROM NBReferral " & _
                       "WHERE [Account #]= '" & [Forms]![Referral to BMO]![Account #] & "'"
            [Forms]![Referral to BMO]![Referral to BMO Search Subform].Form.RecordSource = str_Data
            [Forms]![Referral to BMO]![Referral to BMO Search Subform].Form.Requery
        Else
            txt_Air_Miles_Accrued = Rst.RecordCount
            MsgBox "5 " & Rst.NAME
            MsgBox "No recordset"
        End If
        Exit Sub

Message boxes 1 and 2 produce exactly the same result (i.e. all data from one of my tables, 27,000+ rows), yet the code still goes to the section where Rst.EOF is true.

 

[Alc]

Not sure what it means, but if I add

Msgbox Rst.RecordCount

on the line after

Msgbox Rst.Name

I get a runtime error: 3219 - Invalid Operation.

I get the same error if I try to use this instead

Msgbox [Forms]![Referral to BMO].Form.Recordset.RecordCount

and I've used that elsewhere with no errors.

I've used Dcount to check and the table the data comes from definitely has 27,000+ rows. The actual recordsource for the form is

SELECT * FROM NBreferral ORDER BY [Record Number] ASC;

Running this as a query produces exactly what I'd expect to see.

In an act that might be classed as clutching at straws, I ran a compact and repair - just in case there was an issue with the data itself - but that didn't help.

 

[Galaxiom]

When a DAO recordset is opened on a linked table the default is a dynaset (dbOpenDynaset). The default for local tables is a TableType (dbOpenTable).

Add the argument to the openrecordset command to force it to the dynaset built on the local table.

An alternative is to use the Seek Method of the TableType recordset instead of the FindFirst.

Also note that the count of a DAO recordset must be preceded by a MoveLast so all the records are loaded otherwise it won't provide a count. Then move back to the first record before a Find.