Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset Not Updateable problem (Access 2K, Win 2KPro, SQL Server 2K)

    I have an Access db that is getting a little hairy, I think. The db has a linked table from SQL Server that I use for displaying info submitted via a webform. I built an edit/update form for adding additional information to the work request. in order to get this part done, the 'additional' data lives on a subform, which itself has another sub-form to input multiple notes on the assignment. since a work request may end up being re-assigned, the subform is useful as it can, in datagrid format, display the history of assignments.

    On the other side of the fence, people who have been assigned a work request need to fill out additional information pertinent to their assignement, which becomes a Work Project on assignment. The problem here is i can't pull the data together in a form that is at all useful:

    The form is intended to display info from three different tables:
    1. the Case Name and Request Number, etc. generated via the submission to a SQL Server, so this info is pulled from a linked table. <-- read-only
    2. the Date the request was Assigned/Assignment due date, etc. generated in the subform described above <-- read-only
    3. Work Project tracking time, notes, location of work project files, etc. <-- to be entered by the Assignee

    I can weave together a mess of queries to get this stuff into a source query for the form but the necessary fields are not, however, updateable. If it was I'd just lock the controls that shouldn't be edited and start thinking about what to have for dinner...

    I've tried some of the suggestions on previous posts, such as adding DISTINCTROW to the supporting queries and including unique IDs on various tables (altho I have no need for them) and am getting a bit frazzled now. is this the sort of thing that requires a subform as well? Or is there any other things I should consider in terms of the project design that might help?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset Not Updateable problem (Access 2K, Win 2KPro, SQL Server 2K)

    Perhaps you don't need to include the read-only information in the record source of the form. You could use DLookup expressions to retrieve it, or use code.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Not Updateable problem (Access 2K, Win 2KPro, SQL Server 2K)

    I keep forgetting the DLookup option -- used it in other projects some time ago. I have gotten something that looks right using a subform, tho, as they are a little hard to maintain in terms of displaying the correct info (being capable of storing multiple records when you may not need that). I don't really like subforms, but at the moment I'll press on with the current incarnation with subforms. I may go with DLookup in the end as they are a little more powerful in terms of pulling the exact info needed for a control. For example, I need to filter display on not just a RequestNumber but also the AssignedAnalyst and subforms, from what I can see, aren't going to give me that.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Not Updateable problem (Access 2K, Win 2KPro, SQL Server 2K)

    One thing that I've used just today, in fact: when I find that I have query permutations upon query permutations, and the query becomes (necessarily) un-updateable, there is one easy fix I've found: select the table that you want to update, and do a sub-select on one of your key fields to see if that key field exists withing the vastly permuted criteria query -- well, this may not work if you're working on the same data, but, give it a try. In other words, say I want to update a field in "Plain" table, look for "Plain.ID in (Select subPlainID from VastlyPermutedQuery)"

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Not Updateable problem (Access 2K, Win 2KPro, SQL Server 2K)

    hmm haven't heard this recommendation - not sure I even understand it! <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

    I ended up using another form/subform setup to get the thing to act as i wanted but the option Hans pointed out, DLookup, actually seems tailor-made to my overall desire to display data on a given form where the displayed data may be from multiple sources. In general, forms are built on a table or simple one-to-many query; if you try anything more complicated things like undateable recordsets start to occur. anyway, if you have tried to normalize your data, you will end up needing to display data on forms from more than one table/query so this issue is a fairly basic one. I'm thinking DLookup is one way to approach the issue. I've used them before but I tend to forget things when I've been away from Access for a while.

    Anyway, thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •