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

    Can't update record in form (XP)

    I've built a tabular form with a query as its datasource. (FWIW: I will eventually use this form as a subform.) Two date fields are empty, and I want to use the form to update the records with appropriate dates. I can tab into either of the date fields, and I can see my input mask. But when I try to enter anything, my computer "barks" at me and nothing happens. In addition, I can see that the "New record" icon is grayed out, so clearly I won't be able to add any new records either. My immediate concern, however, is the problem with adding dates to records that are already in there. What's behind this behavior?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't update record in form (XP)

    Lucas,

    Run your query separately and see if you can add the dates in the empty date fields. your query might not be updateable and therefore you can't update your table.

    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Can't update record in form (XP)

    As DJLansing indicated, your query probably isn't updateable. That is, you can't modify existing records and you can't add new ones. Is your query based on two (or more) tables? If so, there must be a unique index on the field on the "one" side of the join between the tables (haven't we been here before?) If not, what kind of query is it?

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

    Re: Can't update record in form (XP)

    Oh, yes, I'm sure we've been there before! And yes, my query is based on...well, actually, three tables. And one of those tables is a junction table that creates a many-to-many relationship.

    Maybe it'll help if I explain what I'm trying to accomplish. The important tables in this scheme are Students, Courses, and Training Plan Types. (I'm modifying the names a bit to simplify things.) We have about 300 courses available. A training plan is a list of selected courses that a student(employee) must complete during a two-year period. The managers here decide which courses an employee must complete, and they make their selections based on the type of work an employee does. So my Training Plan Types table (a look-up table) has an AutoNumber field (Types) and a Title field. (There are other fields, but these are the significant ones.)

    The junction table (Training Plans) pulls the title from the Training Plan Types table and the selected course titles from the Courses table. (I used the Lookup Wizard to create these two Primary Key fields.) The junction table also includes Yes/No fields that designate "core" courses (which the student must complete before any other courses on the list); and "optional" courses. All this information displays beautifully in the query and in the form that uses this query as its datasource. This table also has two "date" fields that I want to fill in as the information comes available. If a student comes in today and completes a course, I want to fill in that date.

    What I eventually want is a form that lets me select an individual student by payroll ID. Then his or her training plan will display as a subform where I can fill in the missing information (completion dates). I'm sure there are people all over the world who do this kind of thing all the time, but I'm approaching this from the wrong angle. Can you point me down the right path? Thanks!

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

    Re: Can't update record in form (XP)

    The same problem exists in the query itself

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

    Re: Can't update record in form (XP)

    Could you post the SQL for your query or queries, or else post a stripped down version of the database?

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

    Re: Can't update record in form (XP)

    Hello, Hans: Here's the SQL for the query.

    <font color=blue>SELECT tblStudents.PID, tblStudents.EmplID, [Nickname] & " " & [LastName] AS Name, tblStudents.TrngPlanType, tblCourses.HandoutNbr, tblCourses.ShortTitle, tblTrainingPlans.Core, tblTrainingPlans.Optional, tblTrainingPlans.CompletionDateCurrent, tblTrainingPlans.CompletionDateHx
    FROM tblStudents INNER JOIN (tblCourses INNER JOIN tblTrainingPlans ON tblCourses.CourseKey = tblTrainingPlans.Courses) ON tblStudents.TrngPlanType = tblTrainingPlans.Type
    WHERE (((tblStudents.PID)=[Enter PID]))
    ORDER BY tblStudents.LastName, tblCourses.ShortTitle;
    </font color=blue>

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

    Re: Can't update record in form (XP)

    I'm sorry, but I don't understand this setup. There is a (Training)Type field in tblStudents, in tblTrainingPlans and in the table Training Plan Types you mentioned in your previous reply. This confuses me. I can't make out whether a training plan is something general, or is for an individual student.

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

    Re: Can't update record in form (XP)

    Hello, Hans, and thank you for looking at my confusing setup. It will be Tuesday before I get back to this project, but then I'll have opportunity to post a stripped-down version.

Posting Permissions

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