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.

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]

Open up the Relationships window. Do you have a join between the tOrgs and
tContacts define? If so is referiential integrity enabled? If RI isn't
enabled, you may run into these problems.

Actually even with RI enabled, the best way to handle this kind of data
entry isn't through a query. Rather you want a form based on tOrg and a
subform based on tContacts. Still you need to enter an Organization first
before a Contact although it is possible to use the Not In List event to do
what you want.

 

[Guest]

The link between the two tables does have referential integrity. The query
usually works -- only sometimes it doesn't. Any ideas why it would stop
working? Thanks!

 

[Guest]

What is a "not in list event"?

Open up the Relationships window. Do you have a join between the tOrgs and
tContacts define? If so is referiential integrity enabled? If RI isn't
enabled, you may run into these problems.

Actually even with RI enabled, the best way to handle this kind of data
entry isn't through a query. Rather you want a form based on tOrg and a
subform based on tContacts. Still you need to enter an Organization first
before a Contact although it is possible to use the Not In List event to do
what you want.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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.

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;