Query Locks

[Guest]

I have a query (see SQL below) that connects 2 tables: Contacts and Orgs.
Each table has the field Organization in it. That field is the linking field
between the two, and it is the primary key of the Orgs table. All tables
have referential integrity.

It is set up so that if the organization is not available in
tContacts.Organization, the new organization can be typed in
tOrgs.Organization.

Most of the time it works great. But sometimes, it won't let me type in the
field torgs.Organization. I don't get any "locked for editing" message ... I
type and no letters appear in the field. The database has just been
compacted/repaired, so that's not the problem.

Any ideas? Thanks!!

SELECT tContacts.ConID, tContacts.Prefix, tContacts.First, tContacts.MI,
tContacts.Last, tContacts.Suffix, tContacts.Org, tContacts.Title,
tContacts.cAddress, tContacts.cCity, tContacts.cState, tContacts.cZip,
tContacts.cZip4, tContacts.cPhone, tContacts.cFax, tContacts.cAltPhone,
tContacts.cEmail, tContacts.cHomePreferred, tContacts.cDoNotMail,
tContacts.cNotes, tContacts.cUpdated, tOrgs.Org, tOrgs.oAddress, tOrgs.oCity,
tOrgs.oState, tOrgs.oZip, tOrgs.oZip4, tOrgs.oPhone, tOrgs.oFax,
tOrgs.oWebsite, tOrgs.oDistrict, tOrgs.oUpdated, tContacts.cExt,
tContacts.[Select], tContacts.Student
FROM tOrgs RIGHT JOIN tContacts ON tOrgs.Org = tContacts.Org
ORDER BY tContacts.ConID;

 

[Guest]

The best way to handle this is a form based on Orgs and a subform based on
Contacts.

Another way is a form based on Contacts with a combo box based on Orgs. If
there isn't an Org, the Not In List event can be used to open up an Orgs form
to enter one then bring you back to the Contacts form.

 

[Aaron Kempf]

the best way to use query locks is to use WITH (NOLOCK) for all of your
queries

Access MDB doesn't have this functionality and thus when Access MDB hits a
locked record, it throws a tissyfit