Apparent Lock on Query

[John Humble]

I've been asked to repair an Access 2000 database with about 2400 records in
TBL_Main. The table has 57 fields and is linked to several smaller tables
in straightforward ways. The user uses the database via FRM_Main. This has
71 text boxes based on QRY_Main, which contains all the fields from TBL_Main
and related fields from linked tables. All this is straightforward.

The user reported being unable to add records to the database via FRM_Main.
The Form's proerty sheet allows edits and additions. New records can be
entered directly in TBL_Main but these fail to show up in QRY_Main, which
insists on showing only the orginal 2400 records no matter how many new
records I add to TBL_Main (which has an auto-number as its primary key).
Consequently the new records don't show up in FRM_Main either. QRY_Main's
property sheet doesn't report any filters but the query does have one
selection criterion, which can be set to 'all'. I've tried deleting this
entirely without effect. QRY_Main is set to "No Locks" and "User's
permissions".

I'm guessing that the problem might be related to a superfluous filter in
FRM_Main which I can't get rid of. I can delete the contents "Order By" and
"Filter" in FRM_Main's Properties Sheet as often as I like, however they
always reappear when the form is reopened.

I'm baffled. Can anyone offer any suggestions other than rebuilding
QRY_Main and FRM_Main from scratch (without the help of the original design
notes if such ever existed!)?

TIA

John

 

[Jeff Boyce]

John

Check Access HELP on "updateable query" -- because of the joins to other
tables, there may be an issue of not having all fields required to allow
update.

Good luck

Jeff Boyce
<Access MVP>