Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Angry Unanswered: Referring to controls on subform doesn't work locally

    I have an Access 2002 database with SQL server backend. I have mutliple subforms, each with a control that sums each month's number. When I click a button on the main form, I want the sum of the controls with sums placed in a control on the main form. This works fine.

    However, I copied the database tables to a local Access database for training purposes. The button doesn't work. It thinks the subform's control isn't numeric, so I get zero for an answer. What's going on here?!

    Example:

    Subform 2004
    Jan 1,000
    Feb 1,500
    Total 2,500

    Subform 2005
    Jan 800
    Feb 1,000
    Total 1,800

    Answer on main form should be: 4,300


    Here is the code:

    If Not (IsNumeric(Me.SUB2004.Form!curTotal)) Then
    curA2004 = 0
    curB2004 = 0
    curC2004 = 0
    curD2004 = 0
    curE2004 = 0
    curF2004 = 0
    Else
    curA2004 = Me.SUB2004.Form!ATotal
    curB2004 = Me.SUB2004.Form!BTotal
    curC2004 = Me.SUB2004.Form!CTotal
    curD2004 = Me.SUB2004.Form!DTotal
    curE2004 = Me.SUB2004.Form!ETotal
    curF2004 = Me.SUB2004.Form!FTotal
    End If

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    My first impression is that when you converted it to MSAccess tables, MSAccess translated the SQL Server field to an MSAccess field with the wrong field type. If the SQL Server field is an integer, change the MSAccess field to a number. If the SQL Server field is a float type, change the MSAccess field to a double. Etc...Etc.. you may want to look up what field types in MSAccess match the field types in SQL Server.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    The numbers being added are all currency, which is correct.

    Months ago, the tables were converted from Access to SQL Server for use when the database grew too large. Back then, it worked with the Access tables. The form has changed, but this part of adding numbers has existed since way before the conversion. I was not involved with the conversion, so I cannot be sure nothing changed...

    I just located an old copy of the form that presumably did work with local Access tables. I will look at that next. Hopefully there's something I can steal from that to fix the current version.

  4. #4
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    In case anyone is interested, I figured out the problem. There was a Me.Refresh on the main form added between the old and new versions. I removed it from the new version and the form worked properly. Perhaps there was a delay time and the numbers hadn't updated yet.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wow! That surprises me... it's the first time I've ever heard of a problem caused by running Me.Refresh.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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