Subform/Subreport:Call Listing Subform

[BillD]

I have a call listing subform on a form I use to look up and edit records of
individuals. The calls/notes subform has a date field. If I sort on the date
field then the database, most times, will let me close, but I cannot reopen.
The .ldb lock file does not close and cannot be deleted as the MSAccess
process is still running. Error I think says the database is still in use so
can't be opened. It also says sharing violation if I try to rename. "The
source or destination file may be in use". I have been able to use an unlock
program to unlock the .ldb file. Then I can close the MSAccess process and
reopen the database.
What can I do to fix this? I do not usally sort this field. I just tried it
to see if it would sort. Now I need to fix as a customer may have same
problem.

 

[Allen Browne]

First thing would be to make sure you have the latest service pack for your
version of Office:
http://support.microsoft.com/sp
Unless you are using A2007, you also need to get the JET service pack:
http://support.microsoft.com/kb/239114

If that doesn't solve the problem, tell us your version of Office and
service pack, and how you sort on the date. Do you right-click?
Programmatically set OrderBy property?

Does this only happen if you are using a Filter or OrderBy in the main form
as well?

Temporarily comment out any code in the Close and Unload events of the form
and subform. Display the Navigation Buttons, so you can tell if the
form/subform is dirty (has unsaved edits) when this occurs.

 

[BillD]

I have Office SP3. I right click on the Calls and sort A-z or Z-A. Can I
disable the sort on the field in the subform.

 

[Allen Browne]

Okay, if you have Office 2003 SP3, make sure you have installed this hotfix
also:
http://support.microsoft.com/kb/945674
Explanation of why:
http://allenbrowne.com/bug-Access2003SP3.html

To disable the shortcut menu that exposes the right-click, set the form's
ShortcutMenu property to No. (It's on the Other tab of the Properties box,
when looking at properties of the form.) Alternatively, design a custom
shortcut menu bar if you want to still provide some other right-click
options.

You may need to design custom menus and toolbars as well, to prevent the
user sorting via those.

Of course, it would be better to get to the bottom of why it's not working
rather than disable the functionality. Not sure what else to suggest though.
Depending on what this problem field is, it may be to do with data types
(e.g. for a calculated field.)

 

[BillD]

Allen:
Thanks so much for your help with this problem.
I have Office 2000. When I go to Access and look at About Access I see SP3
Version 9.00.6926. When I look at Add/Remove programs, I see "Microdoft
Office SR-1 Premium Version 9.00.9327" and "Microsoft Office SR-1 Disk 2
version 9.00.9327". I do not see any other Office 2000 service packs or
hotfixes.

I copied the Access database from this machine running Windows 2000 SP4 to
my other machine rinning Windows XP Pro and Office 2002SP3. I did not have
any problem with sorting on the "Note Date" of the Call listing SubForm. This
seems to confirm that the problem is with the Access Program and SubForm
field sorting.

Bill D.

Thanks so much for your help with this problem.

 

[BillD]

Allen:
Your recommendation-

To disable the shortcut menu that exposes the right-click, set the form's
ShortcutMenu property to No.

This fixed the problem. I cannot now click on and sort any field in the Call
Listing Subform. This is the way I want it. The calls are listed by date and
should be left that way.

Thanks again.
Bill D.

 

[BillD]

Allen:
Thought that you would like to know what was causing the Access 2000
database to lock when using a Call Listing Subform on the Main Form to add,
edit Customers. It seemed to turn out to be a Caption name "Call ID" on one
of the Table Fields was spelled with a space and the Field Name was "CallID".
This was the same for at least 3 fields.

Thanks for your help.
Bill D.