Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2013
    Thanked 0 Times in 0 Posts

    MS Access 2010 Runtime Error 3020 - Combo box & master/subform

    I am new at VBA/MS Access programming. I have a problem that is intermittent. Background: I have a main form that will display the data based on another forms selections of combo boxes. Please note: all combo boxes are unbound. -- This code was written by someone else.

    I select a client number, client name comes up in a text box, then I need to select an Agreement. Once the Agreement is selected, I have to select the Occurrences for this Agreement (but if I make a wrong decision and change it to another Agreement before or without selecting an Occurrence, I do *not* get an error). Then I click a cmd button to update the sub form. It works beautifully – no problems…

    Because there may be many Occurrences (one for each month dating back to the year 2004~ which can get kind of long to find the Occurrence you need) , I added another unbound combo box for the year() of the Occurrences to the form. This populates via code from the afterUpdate on the Agreement combo box. I select it. Then I select the Occurrences, then the cmd button to update sub form. Everything works.

    All is well. But….

    Now if I do the same process again (starting from the beginning)… however; this time I select a year, and do NOT select an Occurrence, BUT change the year again (for example: say I selected 2012, but realized that I needed 2011, so select the year 2011 from the combobox). I then get a 3020 runtime error on the AfterUpdate on the combo box stored proc.

    All the code is the same as the previous code, except I moved it down for the new combo box.

    Basically this is the Orig code:
    1a) Select Client Number, which queries the clientID, and Selects the Client Name
    2a) Select Agreement to load Occurrences
    3a) Select Occurrences to display in form.

    Basically this is the New code:
    1b) Select Client Number, which queries the clientID, and Selects the Client Name (SAME as orig code)
    2b) Select Agreement to load OccurrenceYear (added new code to select years) - basic select query code and setting the rowsource of the OccurrenceYear combo box
    3b) Select OccurrenceYear to load Occurrences (same code that was in 2a~from above, except I added another line of code to the select query to narrow search: & "AND (Year((tblOccurrences.datePeriodEnd))=" & cboSelectOccurrenceYear & ") " _)
    4b) Select Occurrences to display in form. – (SAME as orig code)

    I know exactly where the code is breaking, but it doesn’t make sense to me because the only difference to the original code and mine is that one line of code in 3b. And even if I remove it, the same problem happens, but if I go back to the original database (without my new combo box), I can select different Agreements without this runtime error and the Occurrences all show up.

    How do I get this error to stop happening, should the user change their mind on the combo box for the year?

    Here are the stored Proc which breaks:
    Private Sub cboSelectOccurrenceYear_AfterUpdate()
    'Specific Occurrence year was selected, fill in a list of Occurrences
        On Error GoTo ErrorHandler
        Dim rst As DAO.Recordset
        Dim strSelect As String
        strSelect = "SELECT tblOccurrences.lngInstID,tblOccurrences.datePeriodEnd As 'Period End'" _
            & ",tblOccurrences.dateSchedStart " _
            & "FROM tblOccurrences " _
            & "WHERE (((tblOccurrences.lngEngID)=" & cboSelectAgreement & ") " _
            & "AND ((tblOccurrences.boolMaster)=False)) " _
            & "AND (Year((tblOccurrences.datePeriodEnd))=" & cboSelectOccurrenceYear & ") " _
            & "ORDER BY tblOccurrences.datePeriodEnd, tblOccurrences.dateSchedStart;"
        cboSelectOccurrence.RowSource = strSelect
        'Load Agreement master Occurrence into form
        Set rst = Forms.frmAgreement.RecordsetClone
        rst.FindFirst "[lngEngID] = " & Me.cboSelectAgreement
        Forms.frmAgreement.Bookmark = rst.Bookmark
        Set rst = Nothing
        'Save number of work days to auto update date due
        gdblWorkDays = gdblWeekDayDiff _
            (Nz(Forms.frmAgreement.sfrOccurrence!dateSchedStart, Now) _
            , Nz(Forms.frmAgreement.sfrOccurrence!dateSchedDue, Now))
        'Link Occurrence subform and position at master Occurrence   
        '(The line below is where it BREAKS – when a user changes the cboSelectOccurrenceYear without selecting an Occurrence)
        Forms.frmAgreement.sfrOccurrence!lngEngID = Forms.frmAgreement.lngEngID
        'Load master Occurrence into subform
        Set rst = Forms.frmAgreement.sfrOccurrence.Form.RecordsetClone
        rst.FindFirst "([lngEngID] = " & Me.cboSelectAgreement & ") " _
            & "AND (boolMaster = True)"
        If Not (rst.EOF And rst.BOF) Then
            'There are Occurrences to show in subform
            Forms.frmAgreement.sfrOccurrence.Form.Bookmark = rst.Bookmark
            Set rst = Nothing
            Call gSetMasterLabels(Forms.frmAgreement.sfrOccurrence!boolMaster)
        End If
        Me.cmdSelect.Enabled = True
        Me.cmdCancel.Enabled = False
        Me.cboSelectOccurrence.Enabled = True
        Me.cboSelectOccurrence.Locked = False
        Call gSetFormState(gfrmstcSelectingOccurrence)
        On Error Resume Next
        Set rst = Nothing
    Exit Sub
        Select Case Err.Number
        'No expected errors
        Case Else
            Call UnexpectedError(Err.Number, Err.Description, Err.Source, _
                Err.HelpFile, Err.HelpContext, "cboSelectOccurrenceYear_AfterUpdate")
        End Select
        Resume ExitProcedure
    End Sub
    Last edited by WendellB; 2013-05-30 at 18:22.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts
    FYI, I edited your post so that the VBA code is in a code box to make it more readable.

    First, this is quite a challenge if you don't have much VBA programming experience, or experience with DAO. The error you are getting indicates that you are doing a recordset manipulation without the necessary commands, but in this case I doubt that is the issue. One thing I try to do is always close a recordset in addition to setting it to nothing before I reuse it. I suspect the symptoms you are seeing is a situation where the code works the first time it is executed, but if it executes a second time there is something left from the previous execution that has not been cleared. To debug this, do a line by line step through the code after setting a breakpoint. Also, you may want to disable the error handling so you know for certain what the contents of the various controls and variables are.

    Down the road you may want to explore using a different User Interface for selecting the occurrence - these kind of sequence problems can drive you up the wall. I hope your debugging is successful.

  3. The Following User Says Thank You to WendellB For This Useful Post:

    I_AM_HUMAN (2013-05-30)

  4. #3
    New Lounger
    Join Date
    May 2013
    Thanked 0 Times in 0 Posts
    Thanks for the input and managing my post/code! I added the rst.close in the code and put it before the Set rst = Nothing. It did not fix my problem. The line that is breaking (causing the error) is: Forms.frmAgreement.sfrOccurrence!lngEngID = Forms.frmAgreement.lngEngID I think this would be changing the field in the subform. (Although the number doesn't change unless you select a different client). I just wish I could figure this out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts