Access 2003 Form (new to databases design)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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#];
 
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...
 
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.
 
Back
Top