Access 2010 Error 3021 while porting code from Access 2007 :: Help Request

basstard80

Registered User.
Local time
Today, 06:29
Joined
Sep 4, 2010
Messages
12
Hello to everyone,
I've googled my problem but it seems anyone had it before.

I have a form containing a sub form. The main form is only a mask to contain some controls, while the subform shows a recordset given by a dinamically built query in datagrid mode. When I click on some row of the subform and then I click on a command button I run some code that should perform actions on the selected record. So to retrieve the id of the record to work on, I use this piece of code:

Code:
Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
In Access 2007 this code worked fine all the time, but not in Access 2010. In Access 2010 this works only if I select the first row of the subform, while selecting any other record it rises error 3021, apparently without reasons. In fact, using watch windows for the recordset object, and then navigating into the tree structure up to the field "LEDGER1.TRANSACTION", I can clearly see the correct value populated, by the way even into the watch window the full piece of code "Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]" shows the label "No current record.", and it rises runtime error 3021 when the code is executed.

Honestly I don't see any error... maybe is this an Access bug?

Thanks to everyone who'll give advice and help.

Max
 
Something things check...

1. References
2. Uncheck Track AutName Correct
3. Uncheck Compact on Close
4. Make sure you have...

Option Compare Database
Option Explicit

...at the top of *every* module (including the ones behind forms that have code). Then do Debug > Compile and fix any errors or undeclared variables that appear, if any.
 
You are depending on the selection remaining which is not wise. Just use this instead:

Code:
Screen.PreviousControl.SetFocus
And then gather the value.
 
Hello, thanks for your posts.
However
1. I always declare every variable (I tend to keep my code clean and DRY)
2. I don't want to uncheck "compact on close" because I do want to compact the DB on closure
3. I didn't found where uncheck Track AutName Correct (I've never used this option, could you please tell me where and what's going to change in behaviour without this option?)
4. I've added Screen.PreviousControl.SetFocus before the statement

... but my runtime error still rises and nothing is changed... Is therre a way to access to a record in a subform different from such I used?

Thanks again.
 
In reply to...

2. Compact on Close has been the *beginning* of many a corrupted database, especially one that is shared by multiple Users. And there was a period of time where it actuallu *deleted* the database completely.

3. Go to Options > Current Database to find that option. That has been the cause of many obscur errors that unchecking has eliminated.

And 1. You didn't mention if you checked Tools > References to see if there are any MISSING. Also, could you list them here so we can have a look?
 
Gina,
as you suggested I disabled "compact on close" and "auto name" option but I'm still stuck.
The references I see listed are in the piture attached, I cannot recognize any anomaly... pls take a look and tell me. I'm feeling like I found another of such kind of misteryous problem impossible to explain.

Thanks.
 

Attachments

  • references.png
    references.png
    26 KB · Views: 326
One more quick question before I make one more observation.

Is the subform control (control on the main form that HOUSES the subform) really named subAccountLedgerList? I'm guessing it is but I just need to know to eliminate one other possible scenario.
 
I found it!

UNCHECK Microsoft Visual Basic for Applications Extensiblity...
You probably want to also remove the Common Controls one. If you are using any then you need to replace with Access 2007/Access 2010 built in one. The way to check is to UNCHECK them and the Debug > Compile and see if any errors present themselves.

That is left over from Access 97 and creates issues. See...

http://www.regina-whipp.com/index_files/References.htm

...scroll all the way to the bottom.
 
@ Bob: yes, that's the name of the subForm which host the data form and has default view as datasheet

@ Gina: I left minimum references which still compiles the project (VB for Application, MS Access 14.0 Object Library, MS Office 14.0 Access database engine Object Library) but... still "no record found"

Maybe I should rewrite my project from scratch? I'm starting feeling lost!
In fact if I quickly build a new database, I create a subform as datasheet container, then a form, and then I try to access to subform's recordset with the same piece of code:
Code:
Private Sub Command15_Click()
Dim a As Variant

    a = Me.subFrmTable1.Form.Recordset![Field1]
    MsgBox a
    
End Sub
it works.

If I add a new command button to existing project and I write a similar subroutine, it doesn't. So what's wrong? :confused:
I really do need to copy/paste the whole project view an control starting from only the underlying database model?
 
Last edited:
Before we go to a complete rewrite...

1. Create a new database
2. Uncheck Track Autoname... and Compact on Close
3. Import all your objects into the new database

Does it exhibit the same behaviour?
 
I've just done 1, 2 and 3... and same error... I don't understand... I'm little bit disconsolate.
 
So am I... can you please post the entire section of code. Even though it's erroring on that line maybe there is something else that is causing the problem.
 
Here you are the whole module:

Code:
Option Compare Database
Option Explicit

Private formHnd As Form_frmAccountList
Private AccountId As String

Private Sub cboDateFilter_Change()
    Call updateLedgerRecordset
End Sub

Private Sub cboDateFilter_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    cboDateFilter.Dropdown
End Sub

Private Sub cmdReconcile_Click()
    Dim frm As Form
    
    ' Search if the form is already open
    For Each frm In Application.Forms
        If CurrentProject.AllForms(frm.NAME).IsLoaded = True Then
            If frm.NAME = "frmReconcile" Then
                ' Error Message
                MsgBox mmgrGetDialog("frmAccountList.Reconcile.AlreadyOpen"), vbExclamation
                Exit Sub
            End If
        End If
    Next frm

    ' Open reconciliation form
    DoCmd.OpenForm "frmReconcile", acNormal, , , , acNormal, AccountId
End Sub

Private Sub cmdViewTransaction_Click()
    Dim sArgument As String
    Dim trId As Long
    
    Screen.PreviousControl.SetFocus
    
    If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
        Exit Sub
    End If
    
    ' Get the transaction ID to show
    trId = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]

    ' Generate argument string to pass to transaction form
    sArgument = Join(Array(enumFormNewTransactionOpenMode.OpenView, trId))
    
    ' Open transaction form
    DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub

Private Sub cmdDeleteTransaction_Click()
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim msg As String
    
    If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
        Exit Sub
    End If
    
    msg = mmgrGetDialog("frmAccountList.DeleteTrasanction.AskYN")
    msg = mmgrSetParam(msg, 1, Me.subAccountLedgerList.Form.Recordset![DATE])
    If MsgBox(msg, vbYesNo + vbExclamation) = vbYes Then
        Set dbs = CurrentDb()
        Set qdf = dbs.QueryDefs("qryTransaction_Delete")
        qdf.Parameters("lTransaction").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
        qdf.Execute
        qdf.Close
        dbs.Close
        
        Me.subAccountLedgerList.Form.Requery
    End If
End Sub

Private Sub cmdEditTransaction_Click()
    Dim sArgument As String
    Dim trIdNew As Long
    Dim trIdOld As Long
    Dim dbs As Database
    Dim qdf As QueryDef
    
    If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
        Exit Sub
    End If
    
    ' Add a new transaction with old transaction date
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("qryTransaction_CopyTransaction")
    qdf.Parameters("lTransactionToCopy").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
    qdf.Execute
    qdf.Close
    dbs.Close
    
    ' Get ID from just inserted transaction
    trIdNew = getLastInsertedId()
    
    'Copy ledgers to new transaction
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("qryTransaction_CopyLedgersWithStatus")
    qdf.Parameters("lTransaction").Value = trIdNew
    qdf.Parameters("lTransactionToCopy").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
    qdf.Execute
    qdf.Close
    dbs.Close
    
    ' Generate argument string to pass to transaction form
    trIdOld = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
    sArgument = Join(Array(enumFormNewTransactionOpenMode.openEdit, trIdNew, trIdOld))
    
    ' Open transaction form
    DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub

Private Sub cmdCopyTransaction_Click()
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim sArgument As String
    Dim trId As Long
    
    If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
        Exit Sub
    End If
    
    ' Add a new transaction with actual date
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("qryTransaction_AddNew")
    qdf.Execute
    qdf.Close
    dbs.Close
    
    ' Get ID from just inserted transaction
    trId = getLastInsertedId()
    
    'Copy ledgers to new transaction
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("qryTransaction_CopyLedgers")
    qdf.Parameters("lTransaction").Value = trId
    qdf.Parameters("lTransactionToCopy").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
    qdf.Execute
    qdf.Close
    dbs.Close

    ' Generate argument string to pass to transaction form
    sArgument = Join(Array(enumFormNewTransactionOpenMode.openCopy, trId))
    
    ' Open transaction form
    DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub

Private Sub cmdNewTransaction_Click()
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim sArgument As String
    Dim trId As Long
    
    ' Add a new transaction with actual date
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("qryTransaction_AddNew")
    qdf.Execute
    qdf.Close
    dbs.Close
    
    ' Get ID from just inserted transaction
    trId = getLastInsertedId()

    ' Generate argument string to pass to transaction form
    sArgument = Join(Array(enumFormNewTransactionOpenMode.openNew, trId))
    
    ' Open transaction form
    DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub

Private Sub updateLedgerRecordset()
    Dim dbs As Database
    Dim qdf As QueryDef
    
    Set dbs = CurrentDb()
    
    Select Case cboDateFilter.Value
    
    Case 0 'Last month
        Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_Limited")
        qdf.Parameters("lDayView").Value = 30
    
    Case 1 ' Last 3 months
        Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_Limited")
        qdf.Parameters("lDayView").Value = 90
    
    Case 2 ' Last year
        Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_Limited")
        qdf.Parameters("lDayView").Value = 365
    
    Case 3 ' All
        Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_All")
        
    End Select
    
    qdf.Parameters("AccountId").Value = AccountId
    
    ' Set the new recordset
    Set Me.subAccountLedgerList.Form.Recordset = qdf.OpenRecordset()
End Sub

Public Sub InitializeFormInstance(acctId As String)
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim sInactive As String
    
    ' Store the selected account id in module scope variable
    AccountId = acctId
    
    ' Set the form handler
    Set formHnd = Me
    
    ' Set the subform recordset
    Call updateLedgerRecordset
    
    ' Disable transaction function for inactive accounts
    If Not getAccountActive(AccountId) Then
        Me.cmdNewTransaction.Enabled = False
        Me.cmdDeleteTransaction.Enabled = False
        Me.cmdCopyTransaction.Enabled = False
        Me.cmdEditTransaction.Enabled = False
        Me.cmdReconcile.Enabled = False
        
        sInactive = " [Inactive]"
    End If
    
    ' Disable Reconcile button for expenses and incomes accounts
    If getAccountType(AccountId) = enumAccountType.typeExpenses Or _
        getAccountType(AccountId) = enumAccountType.typeIncomes Then
        
        Me.cmdReconcile.Enabled = False
    End If
    
    
    ' Set form caption
    Me.Caption = getAccountName(acctId) & sInactive
    
    'Activate the form
    Me.Visible = True

End Sub
 
The only difference between this and the test I did is that the subForm recordset is assigned dinamically whilst the test I did was filled by a table content...
May be this the problem?
 
Okay, well it appears to me that should work so they only other thought I have is did you apply the Office SP1 patch? It appears that patch is *breaking* code.
 
At the end I gave up and I decided to rewrite from scratch. Nevertheless, I'm starting having a lot of "senseless" behaviours such as declaration like this
Code:
Dim dbs as Database
giving me strange compile errors like "Expected user-define type, not project", or "File not found" while opening the DB.

Solutions: create new DBs, import code step by step and stop when code stops working... re-create module, copy/paste...

Really, I coded C, Java, php, C++, Js... but VBA is always a lot of fun when you waste time to debug "correct code" or you try to find work around to make your code working...
 
At the end I gave up and I decided to rewrite from scratch. Nevertheless, I'm starting having a lot of "senseless" behaviours such as declaration like this
Code:
Dim dbs as Database
giving me strange compile errors like "Expected user-define type, not project", or "File not found" while opening the DB.
It should be
Code:
Dim dbs As DAO.Database

Be explicit in your coding. Don't use ambiguous declarations. While they would work in many instances, you run the risk of problems if you don't.
 
Service Pack 1 fixed all the problems. It looks like the first issue was not very stable...
Thanks to you all for the support, in particular to Gina.
 

Users who are viewing this thread

Back
Top Bottom