Subform locks table(s)

[skydivetom]

Experts:



I need some assistance with modifying a form/sub-form operation.

Background:
- Attached is a **sample** database which contains a few tables AND one main form plus one subform
- Also, attached are two JPGs which illustrate two different scenarios.

Scenario #1:
- Open database
- Open form “F10_StaffMemberToOffice”
- In this case, the subform display the data properly

Scenario #2:
- Open database
- Next, open any table (e.g., “T01_Billets”)
- Then, open form “F10_StaffMemberToOffice”
- In this scenario, the subform does NOT display any data (i.e., user is getting an error "You tried to lock table... but table cannot be locked because it is currently in use..."

Here's the underlying issue:
- This database is utilized by multiple users
- The form/subform include data from three (3) key tables (organization, billets, staff members).
- While user #1 may be the only person accessing the form/subform, another user may utilize yet another form which only access, e.g., "T01_Billets".

My question:
- How can I potentially modify the form/subform so that other users who access any of these three tables won't have a conflict resulting in the subform not showing data (i.e., locking the tables)?
Thank you,
EEH

[ranman256]

there's no reason to have a subform open in (design mode OR datasheet) AND form mode.

[skydivetom]

ranman256 -- thanks for the prompt response... I'm not fully tracking on your comment. Could you please elaborate? Do I need to change some form properties? If so, which ones?

Thanks,
EEH

[Micron]

Your main form record locks property is set to All Records, which will over-ride your application option to only lock the edited record. Change it to 'edited record' or no locks.

[skydivetom]

Micron -- I appreciate your chiming in... please allow me to ask a follow-up question.

In the form's property (see attached JPG), I see the following properties:
Allow Additions = Yes
Allow Deletions = Yes
Allow Edits = Yes
Allow Filters = Yes
Record Locks = No Locks

Assuming I opened the correct Properties view, which one of these needs to change to "No"? "Allow Edits" only?

Thanks,
Tom

[skydivetom]

I changed the "Record Locks" property on the **subform** (vs. main form)… it appears to now work properly. I'll continue testing w/ multiple users. Standing by...


Definitely very helpful recommendation thus far!!!


On that note, while other forms don't seem to give me the same error, I'm wondering whether or not I should change this property on all other forms (not included in the sample database).
It appears the "Record Lock" appears to be the default property when creating a new form. To ensure other others don't run into similar issues, do you advise to change this property to "edited records" across all forms? Or would this result in another unanticipated error?


Thank you,
Tom

[Micron]

Sorry, I mentioned the wrong form. Check subform.

EDIT - interesting that I didn't see a post that was 18 minutes old. Sometimes I wonder...

[Micron]

To ensure other others don't run into similar issues, do you advise to change this property to "edited records" across all forms?

If anyone had asked me, I would have speculated that the property would default according to what is set in the db options but that is apparently not the case.

https://support.office.com/en-us/art...7-97fe0568019a

From what I recall reading elsewhere, record level locking locks a "page" of records. How many records a page might consist of I can't remember. Thus it's my understanding that even record level locking can impose locks on adjacent records, which means the choice of words to define what happens seems somewhat lacking. Best thing you can do is research the subject and then apply what you learn to your specific circumstances. There may be occasions when locking an entire table is safest, or that situation requires no locks. I know developers who remove all locking in their db's as a matter of routine. That might only be to avoid any issues around it but I wonder if they are playing with knives, so to speak.

[skydivetom]

Micron:

Again, thank you for the great recommendation. I'll double-check w/ the users tomorrow to determine whether or not the change of property (record lock) addresses the issues they recently experienced.

As previously mentioned, the majority of critical data is maintained across three (3) tables. As of now, we only have about 10 users who simulatanously access the system. Given that each user has a different mission (i.e., one focuses only on civilians while another only focuses on active duty personnel), chances that more than one user accesses the same record is **very** slim.

So, as long as the forms, queries, and reports won't lock a whole table, I think I should be fine changing the property to "edited records". As you recommended, I'll do little more research on this topic before updating all forms.

Thank you,
Tom