subform locks up

[cwhite23]

Tried posting this before, but I'm not sure it went through.


Good morning.

I have a problem with a subform locking up the database on a multi-user application. The details for the data is as follows:

Header Table
Employee ID (Primary)
Date

Subform Table
Employee ID (Linked to Primary above)
Date (Linked above)
Time_In
Time_Out
Activity
Notes

The form that I designed has a header with fields for the header details to be entered by the users; and a subform for them to enter the times, activities, etc. The parent/child relationships are on the Employee ID and the Date.

The funny thing is that the subform will lock up and give the user an error saying that the data can't be updated, but the header will still accept data.

Has anyone encountered anything like this before? I've tried changing the settings in Access that control how often the data is polled without success, and have removed record locking as well. Anybody have any ideas?

 

[MacroScope]

Try setting the Recordset Type property of the subform to Dynaset (Inconsistent Updates).

 

[cwhite23]

MacroScope,

Thanks for the idea, but it didn't work. It's still locking up.

 

[PHV]

What is the PrimaryKey of the subform table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Try updating data directly into the query that the subform is based on.

 

[cwhite23]

PHV - Both tables have their own auto number key.

MacroScope - That's how it was set up from the start.

I've tried recreating the form and the subform. I've split the app into FE/BE, I've been all over the net trying everything I could find or think of, and it still locks up!

 

[MacroScope]

You need to be more specific than just "locked up". From your original post, if I understand, the problem is an error saying that the data can't be updated.

If that's the case, you need to go back to the table or query it's based on to make sure that it will accept data. Chances are it's a query, and chances are it's not an updatable recordset. That's why I suggested you try to input data directly and see if it accepts it. If it doesn't you're wasting time looking at the form.

 

[cwhite23]

You're correct about receiving the error. When this happens, I can't even enter data into the table directly. Strange thing is...only the subform table locks up. The header table never locks up; which is why I suspected either the subform's query, or the subform itself. I just can't seem to figure out what I've missed.

 

[MacroScope]

Forget the subform. Your problem isn't there if you can't write data directly to the table. Solve the problem at the table, whatever it is, and your problem at the subform will miraculously evaporate when you do.

I frankly can't think of anything that makes a table unwritable save a record in use by someone else, but the error message would be different and it would only affect one record, not the whole table. A recordset that's not updatable is unusual at the table level, not at all uncommon at the query level.

Are there no other error messages?

If nothing else, cut half the fields from the table and try it. If it's updatable the problem is in the half of the fields you cut. Paste them back, and cut the first half of the fields out and verify. Then cut that half in half and keep trying until you narrow it down to the field that's causing the problem.