Strange Update Query Problem in Access 2000

[unhinged]

G'day,

I am experiencing a problem where the Access application hangs after an
update query that I am performing via code.

I have a form with two subforms; one subform lists the courses that
form part of the package students will take, the other subform lists
the courses available for that package based on the department offering
the package.

What I want to do is have buttons on each record in the subforms to
enable the user to click a button and add the available courses to the
package or remove a course from the package.

My code clears the packageID field in the underlying courses table for
the first subform by using the following SQL update query:

UPDATE tCourses SET packageID = NULL WHERE CourseID = Me!CourseID;

The second subform has this query:

UPDATE tCourses SET packageID = Forms!MainForm!Subform!packageID WHERE
CourseID = Me!CourseID;


The first time I ran the query to clear an existing course from the
package it worked fine. All subsequent attempts (three so far) to run
either query result in the hourglass cursor and the application hangs.


Both subforms are based on queries that limit the number of records
returned and allow me to display the name of the department and the
qualification that the course provides. Should I be updating these
queries rather than the underlying table?

Is there likely to be corruption in the database now that this has
occurred?

Is there a better way to achieve my stated goal?

Thanks in Advance,
Daniel.

 

[unhinged]

More info:

Both update queries complete successfully, and in today's testing the
process to remove a course from the package works successfully the
first time and hangs when I remove a second course. This happens even
if I close the form after removing the first course, reopen the form
and try removing the second package.

When adding a course to the package list, the problem occurs on the
first attempt. After playing around with an exit sub statement, it
seems that the line giving me grief is the Me.Requery statement
immediately following the DoCmd.RunSQL statement.

Does anyone have any ideas on why this would give me problems?
Especially when considering the behaviour of the removal process, which
succeeds the first time but hangs each subsequent time?

Thanks,
Daniel

 

[unhinged]

Yet more info:

This problem seems to be related to MS knowledge base article Q225985,
which specifies that a transaction may be extant and needs to be
committed.

I am using DoCmd.RunSQL to perform the update query, which in Access
2000 defaults to using a transaction, so I need to know how to commit
the transaction that for some reason is not completing properly. I have
tried the RunSQL method with UseTransaction set to False, but this does
not prevent the application from hanging.

Thanks in advance,
Daniel

 

[unhinged]

Another instalment in our exciting series:

Today I again tried just setting the value of the foreign key field
(something which did not work initially, which is why I switched to the
SQL process). This time, I actually paid attention to the error message
I was getting: 3164 (field not updateable).

A bit of research on these newsgroups showed that I should also be
looking at the query which forms the RecordSource for my subform; this
seems to be the problem as the recordset from that query is not
updateable in datasheet view. It seems strange to me that if I do not
update the subform and close and then re-open it, the data has been
changed correctly, but I guess that's just one of those things.

So, advice to all encountering this error is to check that the query
you are using allows an update to take place. I am going to post a new
topic now that I have a better understanding of the problem.