Update Form Problem

[jgroh9]

All, I have just normalized my database and I have created a form to display data from 4 different tables. The data displays fine on the form however when I try to edit the data I receive a message stating that there is a write conflict. However, the database is only on my pc and is only used by me. Here is the recordsource for the form:

strSQL = "SELECT tblTransmissionInventory.InventoryID, tblSite.Site, tblLocation.Location, tblCardType.CardType, tblCardDescriptionPartNumber.CardDescription, tblCardDescriptionPartNumber.PartNumber, tblTransmissionInventory.SerialNumber, tblTransmissionInventory.CardVersion, tblTransmissionInventory.BayNumber, tblTransmissionInventory.Shelf, tblTransmissionInventory.Slot, tblTransmissionInventory.SubSlot, tblTransmissionInventory.Comments, tblStatus.EquipmentStatus, tblTransmissionInventory.InstallDate, tblTransmissionInventory.RMANumber, tblTransmissionInventory.ShippedFrom, tblTransmissionInventory.TroubleTicket, tblTransmissionInventory.DateShipped, tblTransmissionInventory.Deleted FROM tblCardDescriptionPartNumber INNER JOIN (tblStatus INNER JOIN (tblCardType INNER JOIN (tblLocation INNER JOIN tblSite INNER JOIN tblTransmissionInventory ON tblSite.SiteID = tblTransmissionInventory.SiteID) ON tblLocation.LocationID = tblTransmissionInventory.LocationID) ON tblCardType.CardTypeID = tblTransmissionInventory.CardTypeID) ON tblStatus.StatusID = tblTransmissionInventory.StatusID) ON tblCardDescriptionPartNumber.CardDescriptionID = tblTransmissionInventory.CardDescriptionID WHERE (tblTransmissionInventory.Deleted = False) AND (tblTransmissionInventory.InventoryID = Forms!frmSearchForm!txtIdentifier)"

The form is also set to be editable, allow additions. Does anyone have any idea why this is happening or how I can fix it? Thanks

 

[PHV]

Seems that the recordset isn't updatable due to the lack of the foreign keys (tblTransmissionInventory.SiteID, tblTransmissionInventory.LocationID, ...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

jgroh9

PHV is always right. I can not recall him being wrong. In so few words, he is always right.

Based on your query, you are trying retrieve the tblTransmissionInventory record where tblTransmissionInventory.InventoryID = Forms!frmSearchForm!txtIdentifier

Everything else is "information" being displayed to the screen for the user's benefit. Site instead of SiteID, Location instead of LocationID, etc.

Now, is this information being displayed on a form?? And the end user edits the results in the form? Or is the results displayed in a query worksheet and you want the user to be able to edit the data in the worksheet?

Using a form is probably the better approach. THEN, instead of retrieving the information in a multi-table query, you just have to worry about tblTransmissionInventory.*

Access gets confused when you retrieve results from a multi-table query when you edit the recordset. The database engine ponders and thinks, "Am I going to edit the values on table tblTransmissionInventory, or tblLocation or tblSite, etc...?" I believe the rule is that you can only edit on the "one-side" or the "many-side" but on both sides of the relationship.

By using a form, your query becomes much easier...
SELECT * from tblTransmissionInventory WHERE tblTransmissionInventory.InventoryID = Forms!frmSearchForm!txtIdentifier
... and then use combo boxes, list boxes, or code with unbound text boxes to display the user firendly information.

As an added thought, I notice that you have...
tblTransmissionInventory.Deleted = False

This suggests that if the end user selects a record from your search form that is deleted, nothing happens. Would controlling the Search form to display only records that meet the criteria make sense?

Richard