Results 1 to 3 of 3
2013-05-30, 15:22 #1
- 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) ExitProcedure: On Error Resume Next Set rst = Nothing Exit Sub ErrorHandler: 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.
2013-05-30, 18:41 #2
- 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.Wendell
The Following User Says Thank You to WendellB For This Useful Post:
2013-05-30, 20:31 #3
- 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.