Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Update in field does not work (XP/2000)

    We have a database that tracks our learning lab courseware. We have roughly 230 courses that are available to about 800 employees on an industrial site. Each employee has a "training plan," which is nothing more than a printed list (a report) of the courses available to him or her. We have nine versions of the training plan--the major difference among them is the courseware titles that are available to the individual employee. For example, some courses are not available to students working with, say, Training Plan 1 (TP1), but those courses are available to that student when he moves up to TP2.

    The "Courses" table has "Yes/No" fields for TP1, TP2, TP3...TP9. If a course is available for Training Plan 1, this field is "Yes." You get the picture. Today, we decided to change the status of five courses to make them available on Training Plan 1. That sounds simple enough. I went to the table and marked those five courses to say Yes for TP1. However, when I print a new TP1, those courses don't show up. I've tried Compact and Repair--no luck. I've checked the queries that drive the printing process, and I can't find a culprit.

    A little more information: We print the training plans by way of a form where we first select the student for whom we want to print a training plan. Then we click on the command button that prints the appropriate training plan. (There's a command button for each training plan.) The training plans are just Access reports, each of which has a query for its data source.

    Another point: This is a split database. The table that stores information about courses is, of course, in the back end. Everything else is in the FE. To change the status of the five courses at issue here, I opened the BE, marked the courses, and closed the BE. Then I launched my copy of the FE and got dissappointed.

    So...I know there's something I'm overlooking. Any ideas?

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

    Re: Update in field does not work (XP/2000)

    Lucas,

    Try to analyze this step by step. Do the following in a copy of the front end. I assume that the front end has linked tables pointing to the tables in the back end.

    1. Open the Courses table (or more accurately, the table in the front end linked to Courses in the back end). Does it show "Yes" in the TP1 field for the five "new" courses?

    2. You probably already have a query that selects all courses available for TP1. If not, create a query based on Courses, and set the criteria for TP1 to be Yes. Does this query list the five "new" courses?

    3. Can you open the report for TP1 directly from the database window, instead of from the student form? (You may be prompted for student info; just supply some dummy data). Does the report opened this way list the five "new" courses?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Update in field does not work (XP/2000)

    1. Yes--the five "new" courses are marked Yes in the linked table.
    2. No--the five courses DO NOT appear in the query.
    3. No, I can't open the report directly from the database window. I've tried to do that inadvertently on a few occasions, and the application goes into a long, slow mode where it seems to be trying to build a training plan for every one of the 800+ employees! So I've cautioned all my users to always launch a training plan from the form only!

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

    Re: Update in field does not work (XP/2000)

    Since 1 is OK and 2 not, it seems that there is something the matter with your query or perhaps with the table.

    - Check the query design very carefully. Is it based on the correct table in the back end or on some other table (a backup copy, a local copy, ...)?
    - Is there a difference in the TP1 column in the Courses table between the courses that had been marked for inclusion earlier, and the five new ones?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Update in field does not work (XP/2000)

    Thank you for your time and advice. I'm going back now to study that query again. May take a while <grin>.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Update in field does not work (XP/2000)

    Hello, again, Hans

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

    Re: Update in field does not work (XP/2000)

    Check the query design. On which table or tables is it based, and on which should it be based? If you find out, the easiest way to change it may be:
    - Switch to SQL view.
    - Copy the SQL statement.
    - Paste it into Word or another text editor.
    - Substitute the correct table name(s) using Find and Replace.
    - Copy the SQL statement.
    - Paste it into the SQL view of the query.
    - Switch to design view and then to datasheet view to test.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Update in field does not work (XP/2000)

    Hello, Hans

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

    Re: Update in field does not work (XP/2000)

    The capital T and the brackets don't mean much. Names are not case-sensitive in Access, and the query design algorithm tends to throw in lots of brackets [ ] and parentheses ( ) into the SQL string.

    If you are certain that the linked table has the correct records, I would create a new query from scratch that does the same as the existing one. Verify that it returns the correct records. Then, make sure that Name AutoCorrect is OFF (in the General tab of Tools | Options...), rename the original query, then the new one to take the place of the original one.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Update in field does not work (XP/2000)

    Hello, Hans

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

    Re: Update in field does not work (XP/2000)

    Phew! Glad you were able to find it!

Posting Permissions

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