Results 1 to 8 of 8
  1. #1
    mdewell is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    38

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

    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.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    It could be a reason in this list.
    http://allenbrowne.com/ser-61.html

  3. #3
    mdewell is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    38
    Hello Orange,

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

    Code:
    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

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    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)

  5. #5
    mdewell is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    38
    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

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    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.

  7. #7
    mdewell is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    38
    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.

  8. #8
    mdewell is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    38
    Sorry I spoke to soon about it not working. I forgot to set the form to an Inconsistent Dynaset.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-28-2011, 02:51 PM
  2. Lookup name in a query on a non-joined table
    By karmacable in forum Queries
    Replies: 7
    Last Post: 09-21-2011, 09:01 AM
  3. Check to see if record id exists in joined table
    By csoseman in forum Programming
    Replies: 1
    Last Post: 08-18-2011, 01:06 PM
  4. Replies: 0
    Last Post: 06-15-2011, 07:02 AM
  5. Re: Adding a new record to a joined table
    By Mcdodre in forum Access
    Replies: 4
    Last Post: 06-30-2010, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums