Access 2003 Form (new to databases design)

T

tvh

AAHH! Can't add or edit records to a form after adding 3rd table in the
Query builder. Edits, deletions, additions all set to "yes" and no locks.
The form works fine until I add this third (needed) table. The table also
works well, independently. Any suggestions? Thanks!
 
T

tina

the problem is probably in the RecordSource query, not in the form itself.
not all multi-table queries are updateable. try opening the query by
itself - will it allow you to add a record? if not, suggest you post the
query's SQL statement so we can look at it.

hth
 
T

tvh

thanks for the quick reply. the query will not allow edits, either. I'm
super-new to this and you asked me to post an SQL statement. If it's not what
you're asking for, please let me know. The problem occurs when I add the
"tblClientSiteContacts" table to the query. Here's the statement:

SELECT tblClientInformation.tblU2ClientID,
tblClientInformation.citblCompanyName, tblClientInformation.citblContactName,
tblClientInformation.citblContactTitle, tblClientInformation.citblAddress,
tblClientInformation.citblCity, tblClientInformation.citblState,
tblClientInformation.citblZipCode, tblClientInformation.citblWorkPhone,
tblClientInformation.citblExt, tblClientInformation.citblCellPhone,
tblClientInformation.citblFaxNumber, tblClientInformation.citblEmail,
tblWorkOrders.[wotblWorkOrder#], tblWorkOrders.wotblCompanyName,
tblWorkOrders.wotblDate, tblWorkOrders.wotblTime, tblWorkOrders.[wotblJob#],
tblWorkOrders.[wotblPO#], tblWorkOrders.wotblJobDescription,
tblWorkOrders.wotblLocation, tblWorkOrders.wotblServicesRequested,
tblWorkOrders.wotblAdditionalInformation,
tblWorkOrders.wotblInitiallyAssignedTo
FROM tblClientSiteContacts RIGHT JOIN (tblClientInformation INNER JOIN
tblWorkOrders ON
tblClientInformation.tblU2ClientID=tblWorkOrders.wotblCompanyName) ON
tblClientSiteContacts.tblCompanyName=tblClientInformation.citblCompanyName
ORDER BY tblWorkOrders.[wotblWorkOrder#];
 
S

Sylvain Lafontaine

My guess would be that using a Right Outer Join in its query source make the
underlying Recordset of the form read-only. What's happen to the Read-Only
status if you replace the Right Join with an Inner Join?

S. L.

tvh said:
thanks for the quick reply. the query will not allow edits, either. I'm
super-new to this and you asked me to post an SQL statement. If it's not
what
you're asking for, please let me know. The problem occurs when I add the
"tblClientSiteContacts" table to the query. Here's the statement:

SELECT tblClientInformation.tblU2ClientID,
tblClientInformation.citblCompanyName,
tblClientInformation.citblContactName,
tblClientInformation.citblContactTitle, tblClientInformation.citblAddress,
tblClientInformation.citblCity, tblClientInformation.citblState,
tblClientInformation.citblZipCode, tblClientInformation.citblWorkPhone,
tblClientInformation.citblExt, tblClientInformation.citblCellPhone,
tblClientInformation.citblFaxNumber, tblClientInformation.citblEmail,
tblWorkOrders.[wotblWorkOrder#], tblWorkOrders.wotblCompanyName,
tblWorkOrders.wotblDate, tblWorkOrders.wotblTime,
tblWorkOrders.[wotblJob#],
tblWorkOrders.[wotblPO#], tblWorkOrders.wotblJobDescription,
tblWorkOrders.wotblLocation, tblWorkOrders.wotblServicesRequested,
tblWorkOrders.wotblAdditionalInformation,
tblWorkOrders.wotblInitiallyAssignedTo
FROM tblClientSiteContacts RIGHT JOIN (tblClientInformation INNER JOIN
tblWorkOrders ON
tblClientInformation.tblU2ClientID=tblWorkOrders.wotblCompanyName) ON
tblClientSiteContacts.tblCompanyName=tblClientInformation.citblCompanyName
ORDER BY tblWorkOrders.[wotblWorkOrder#];

tina said:
the problem is probably in the RecordSource query, not in the form
itself.
not all multi-table queries are updateable. try opening the query by
itself - will it allow you to add a record? if not, suggest you post the
query's SQL statement so we can look at it.

hth
Click to expand...
Click to expand...
 
A

Albert D. Kallal

With more then one table...you must use left joins.

(double click on the join line...select the option for "all records from the
MAIN table...and possibility a child record from the child table).

Also, to ensure that the query is updateable, those child tables MUST have a
primary key. If any of the child tables (even ones for just simply parts
description lookups) must have a primary key.
 
Top