Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Information lost in forms and reports (Access 2000)

    I have a simple database with one table that stores client bookings for tours. I run various forms and reports based on queries drawn from this table. I recently made some changes to my table: 1. Made a copy of the table and renamed the copy to store data for year 2002, for example. I then made another copy of the main table and created an additional table to hold 2003 data. I deleted the old records from my main table for data relating to years 2002-2003 so my main table now only has data for 2004. 2. I then proceeded to do a 'find and replace' on one column to change company codes. eg. ab to Athabasca to make the names more user friendly. There were about 5 different company codes to replace for multiple records. Now when I try to view some of my forms and reports, 0 records are returned. About 2/3 of my original reports and forms do work. Note none of the reports/forms require entering criteria relating to the company code I changed. Nobody else at the company, including myself has made any additional changes so I'm unsure as to why my reports no longer run. For trouble shooting so far I have checked the record source for all forms and reports and nothing has changed. I did not change my main table name or any properties in the table. I found a work around by creating a new query and linking it to an existing report - this does work, however I would rather find out the possible cause of the solution before proceeding to create a ton of new queries.

    Could it be some kind of permission problem relating to who created the report in the first place? The database is stored on a shared drive.
    Any advice as to what could have happened? Thanks

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

    Re: Information lost in forms and reports (Access 2000)

    Open one of the problematic queries in design view. Is it still based on the original table, or has it become based on one of the copies?

    (Have you applied user-level security to the database? If not, it does not matter who created database objects.)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Information lost in forms and reports (Access 2000)

    Hi,

    I checked the queries and they are all based on the original table. There is no user level security applied on the database either.

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

    Re: Information lost in forms and reports (Access 2000)

    Hmm, if a form based on an original query doesn't work correctly, while it does if based on a newly created query, there MUST be a difference between the queries. But perhaps it will take too much time to try and find out the exact cause. It might be more convenient to create new queries, switch the forms and reports to the new queries, and after thorough testing, delete the old queries. Don't forget to compact the database (Tools | Database Utilities | Compact and Repair Database).

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Information lost in forms and reports (Access 2000)

    Thanks for the suggestions but I promise you that the queries are identical! I already have done a compact and repair. Thanks for your help so far. In your opinion, do you think this could be some kind of bug with the program?

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

    Re: Information lost in forms and reports (Access 2000)

    Without seeing the database, I have no way of knowing what causes the problem.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Information lost in forms and reports (Access 2000)

    I would be suspicious that your change to create new company codes is what caused your problem. Do the queries that no longer work include a join on a table using the company code?

    FYI, the usual solution to the issue of keeping old data around is to create an archive table that you have a single query that is run periodically that appends records older than some predetermined date to the archive table, and then deletes the same records from the live table. Hope this helps.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Information lost in forms and reports (Access 2000)

    No there are no joins. The codes were in one column in the main table and I replaced the codes with actual company names in the same column. It's very bizarre. I'm still using my old database and working on fixing the queries in a copy of my original database that I created.

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

    Re: Information lost in forms and reports (Access 2000)

    Did you change the name of the field as well?
    Charlotte

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Information lost in forms and reports (Access 2000)

    Do the queries that are failing have a criteria of some sort? It sounds as though something in the queries or the report designs may have been using the company code to do a DLookup() for example. Is this a database you designed yourself, or did someone else develop it and you inherited it? Consider adding a new field which is the full Company Name, and leave the Company Code in place and see if things work correctly.
    Wendell

Posting Permissions

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