Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control Source problems (AccessXP)

    I have a subrpt that has a text box that sums up the values in four textboxes above it. The subrpt is tied to a query of a table. It works as it is suppose to. I have a second query tied to a second table which has the same fields with the same names. The two queries look exactly the same. If I tie the subrpt to the second query the values in the top part of the subrpt appear as they should, but the textbox that is suppose to sum does not sum the values. Change the control source back to the original query and the summation is done correctly. I checked both base tables and the fields are listed as Currency fields in both tables. There is no formatting in the properties of either query.

    Any idea why it will do work with the second query? Thank you for your assistance.

    Fay

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

    Re: Control Source problems (AccessXP)

    If the tables and the queries have the same structure, perhaps there is something in the values of the fields of the second table that messes things up. If you are willing to experiment, you can try several things. Your mileage may vary.
    <UL><LI>First, Compact and Repair the database (Tools/Database Utilities). If there are corrupt records, Access may find them; the MSysCompactErrors will contain details; if this table hasn't been made, Access didn't find problems.
    <LI>If that doesn't turn up anything, make a copy of the database and use that for further experiments (!)
    <LI>Make a duplicate of the second table. Delete all records in the second table. Then copy some or all records from the first table into the second table. Does the sum show up correctly now (with the second query as record source of the subreport)? If so, there was something wrong with one or more of the original records, even if Compact and Repair didn't find them. You could try importing records from the copy of the second table in groups to pinpoint the culprit.
    <LI>Make a copy of the first query Open this copy in design view. Assign an alias to the table name in the Properties window (make sure the table is selected in the top half of the query window when you do this.) It can be as simple as a single T. Now open the query in SQL view. Since you assigned an alias, the name of the first table occurs just once. Replace it by the name of the second table, then save the query. Set the record source of the subreport to this new query. Does the sum show up correctly? In that case, there must be a subtle difference with the second query, or perhaps it had become corrupt. You can replace the second query by the modified copy of the first.[/list]Good luck!

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Source problems (AccessXP)

    Thanks Hans.

    I ended up rebuilding the second table. I don't know what went wrong with the thing. I had copied the structure from the first table so you would think there wouldn't be a problem. So much for thinking.

    Fay

Posting Permissions

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