Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset error (a2003)

    The error message probably has to do with the RS recordset, but we don't know what the CalcAvgHours function does with it...

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordset error (a2003)

    I'm getting the following runtime error message: Update or Cancel Update without AddNew or Edit. When I click debug it highlites the lines with * in front of them below.

    Option Compare Database
    Option Explicit
    Dim rsHours As DAO.Recordset

    Public Sub UpdateAvgHours()
    'Calc Average Business hours in a year
    Dim RS As DAO.Recordset
    * txtAvgHrs = ""
    Set RS = Me.fsubHours.Form.Recordset
    * Me.txtAvgHrs = Format(CalcAvgHours(Me.txtDaysClosed, RS), "0.00")
    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Capture the date when the information on this form was last changed.
    * Me.txtLastUpdtd = Now()
    End Sub

    I've stripped out all the procedures/functions in the above form's code that are not producing an error message.

    The error just started happening when I attempt to move to the next record on a form or add a new record to a form. Apparently the problem is calling the Update or Before Update methods without first calling the AddNew or Edit methods. Unfortunately I am woefully ignorant when it comes to dealing with recordsets in code and don't know how to resolve the issue. Help anyone?

    Elizabeth

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset error (a2003)

    Here is the basAvgHours code. Hope it helps in diagnosis!

    'Public rsHours As DAO.Recordset

    '************************************************* ******************************
    ' Sub CalcAvgHours()
    '
    ' Purpose: Calculate the average number of hours a business is opened per day
    ' for the current year
    '
    ' Arguments: None
    '
    ' Returns: Nothing
    '
    ' Output: Posts average hours to the txtAvgHrs text box
    '************************************************* ******************************
    Public Function CalcAvgHours(DaysClosed As Variant, RS As DAO.Recordset) As Variant
    Dim dtmp As Date
    Dim ndays As Integer, nwks As Integer
    Dim YrTotHours As Variant
    Dim WkTotHours As Variant
    Dim i As Integer
    Dim dow As Integer
    Dim yr As Integer

    'Algorithm for calculating a leap year
    'Set starting date as Feb 28 for current year
    yr = Year(Now)
    dtmp = CDate("1/28/" & yr)
    'Increment dtmp by one (1) day
    dtmp = DateAdd("d", 1, dtmp)

    ndays = 365
    'If the day of the month is 29 then it is a leap year
    If Day(dtmp) = 29 Then ndays = ndays + 1



    'Sum total open hours for the year

    'Use dtmp (temporary date) for determining how many days remain at the end of the year
    'either one or two for totaling all the business hours in the year.
    dtmp = CDate("1/1/" & yr)

    'Get total number of hours in a business week
    WkTotHours = GetOpenHours(1, RS)

    'Calc total hours in 52 weeks
    YrTotHours = WkTotHours * 52
    dtmp = DateAdd("d", 364, dtmp)

    'Add the additional business hours for days remaining in the year
    Do While (dtmp <= CDate("12/31/" & yr))
    YrTotHours = YrTotHours + BusinessDayHours(Weekday(dtmp), RS)
    dtmp = DateAdd("d", 1, dtmp)
    Loop

    'Adjust average by the number of days closed
    'Note: If a holidays table is integrated into the database we can calculate the exact
    'number of hours the business is closed in a year
    Dim tmpavghrs
    tmpavghrs = WkTotHours / 7
    If Not IsNull(DaysClosed) Then
    YrTotHours = YrTotHours - (tmpavghrs * DaysClosed)
    End If


    'Calculate average business hours per day
    CalcAvgHours = YrTotHours / ndays


    End Function

    ESW

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset error (a2003)

    Sorry, this isn't going to work. The code posted refers to yet other functions involving the RS recordset. Instead of posting even more code, could you post a stripped down version of the database in Access 2000 or Access 97 format?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]That will enable loungers to look at the problem directly.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: recordset error (a2003)

    I fail to see why you're passing a recordset to a function in this code and the code you posted subsequently. If any of those functions make changes to that recordset, then there has to be and Edit or AddNew method called before that takes place. You can't just change a value in a subform's recordset, and it isn't possible to tell what you are actually *trying* to do. Are you trying to affect a value on the subform or what?

    Also, you are apparently setting a textbox to an empty string, but if that textbox is bound to a numeric field, that won't work.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset error (a2003)

    I was using a third party "smart form" with navigation buttons. I'd been having trouble with it since implementing subforms. Finally decided to just eliminate the smart form module, command buttons and code. It eliminated both the Update or Cancel Update w/o AddNew or Edit and Out of Stack Space errors.

    Thanks for your assistance,

    eliz

Posting Permissions

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