When table is JOINed it doesn't allow editing of data

[mdewell]

Hello,



I'm trying to JOIN another table to an already made query, and the new table is joined to a subtable (if that's the correct terminology) in the existing query. When I make the link, it seems I can't edit any of the data in the query. Anyone know why this happens and how it can be fixed?

Thanks.

[orange]

It could be a reason in this list.
http://allenbrowne.com/ser-61.html

[mdewell]

Hello Orange,

It does seem possible as I've got the following SQL:

SELECT tblHIPAArelational.tblOU_ID,
tblComputer.Computer,
tblHIPAArelational.[Why Not Case Locked With Nylon Wire],
tblHIPAArelational.[Why Not Boot Only From Hard Drive],
tblHIPAArelational.tblJavaVersion_ID,
tblHIPAArelational.tblAdobeVersion_ID,
tblHIPAArelational.tblBIOSVersion_ID,
tblComputer.[MAC Address],
tblHIPAArelational.[Why Not Wake On LAN Enabled],
tblHIPAArelational.[Why Not TrackIt Installed],
tblHIPAArelational.[Why Not Workstation Manger Installed],
tblHIPAArelational.[Date Installed], tblHIPAArelational.Case_Locked_Boolean,
tblHIPAArelational.Boot_Only_Boolean,
tblHIPAArelational.Wake_On_Boolean,
tblHIPAArelational.TrackIt_Boolean,
tblHIPAArelational.Manager_Installed_Boolean
 
FROM (((((
tblHIPAArelational
LEFT JOIN tblJavaVersion ON tblHIPAArelational.tblJavaVersion_ID = tblJavaVersion.ID)
LEFT JOIN tblAdobeVersion ON tblHIPAArelational.tblAdobeVersion_ID = tblAdobeVersion.ID)
LEFT JOIN tblBIOSVersion ON tblHIPAArelational.tblBIOSVersion_ID = tblBIOSVersion.ID)
LEFT JOIN tblOU ON tblHIPAArelational.tblOU_ID = tblOU.ID)
LEFT JOIN tblComputer ON tblHIPAArelational.tblComputer_ID = tblComputer.ID)
LEFT JOIN tblServicePackRelational ON tblComputer.Computer = tblServicePackRelational.Computer;

The area that it seems to mess up is when I add the LEFT JOIN tblServicePackRelational ON tblComputer.Computer = tblServicePackRelational.Computer

when that's in, it doesn't let me edit. Is there anyway I can fix this? Can or does it need some other way of indexing it all? I do know that the tblComputer.Computer each have unique names and MAC addresses. So can I remake the relations so that it is based off the tblComputer entries?

Thanks

[boblarson]

You appear to be trying to do too much with one query. I'm guessing you have some One-to-many and many-to-many relationships between the tables and that is your problem. You CAN try this, but there's no guarantee it will work. Go into the query in design view and right-click on the gray area where the tables show in the diagram and select properties. Then change the Recordset Type from Dynaset to Dynaset(Inconsistent Updates). Other than that, you should be doing it in smaller pieces (form, subforms, etc)

[mdewell]

Hello Bob,

I think the "Dynaset (Inconsistent Updates)" will do it for me. Thanks. I was thinking about using seperate subforms to pull in the data if I couldn't get the query to work on one datasheet. Yet, is there a way to make sure that each subform would scroll at the same time, so that the currently viewing computer's data would show?

Thanks

[boblarson]

Subforms would only show the data applicable to the currently selected main record. So if the main form was where you selected the computer you wanted, the subforms would only show data related to it.

[mdewell]

Hello Bob,

Ah, I guess that's where I'm not exactly using subforms correctly. I've got my main data in a subform datasheet view so that I can filter the data from unbound combo boxes and date calendars, and then go about editing the selected data from there.

I think I spoke too early. I tried the "Dynaset (Inconsistent Updates)" and it worked fine for updates from the query datasheet, but not for the subform it was in in the main form that I use for filtering the data. Sorry if this sound confusing.

Thanks for you help.

[mdewell]

Sorry I spoke to soon about it not working. I forgot to set the form to an Inconsistent Dynaset.