Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A way around Recordset Not Updatable (2000)

    I have a query based on 5 tables, that also has some sum calculations in it. According to definiations the recordset is not updateable. I, of course, want it to be able to update just one field (ysnReconcile). I'm using this query in a subform. Is there any way around this so I can have this query and update that one particular field.

    What my overall end result is: I have the user select a name from a list, once that's done it lists all the orders placed by that person in the subform. On the subform I have the ysnReconcile field where I them to be able to then check that check box once that order shows up on the credit card bill. I am then going to be writing code that will take all the orders where ysnReconcile=yes and export to an Excel file so the person can do whatever they do to send off this report.

    Thanks,
    Deb

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

    Re: A way around Recordset Not Updatable (2000)

    Perhaps you could calculate the sums using DSum expressions instead of using a Totals query. The sum fields would not be editable, obviously, but the other fields should be.

    Or you could use code, for example in the On Dbl Click event of the check box that sets ysnReconcile directly in the table, then requeries the form.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A way around Recordset Not Updatable (2000)

    A query based on 5 tables is unlikely to be updatable even if it isn't a totals query. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way around Recordset Not Updatable (2000)

    I'm only updating one field and it is updateable if I take out the totals query. My problem is i need the totals query to group all parts per the one order together. So I'll have to figure this out.
    Deb

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way around Recordset Not Updatable (2000)

    I think I've found a way around my problem or I should say, I've figured out the better way to do this. It is using the DSum function but I can't seem to get that work properly. Here's my function: DSum("[intQty]*[curUnitPrice]","qryReconcileSubformPartsPriceList","[txtCCPRNo]= [txtCCPRNo]"), when I run it I get a total but it's summing all parts not just where the txtCCPRNo match. The above function is in another query.
    Thanks,
    Deb

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

    Re: A way around Recordset Not Updatable (2000)

    The expression [txtCCPRNo]= [txtCCPRNo] is - not very suprisingly - always true. Try

    DSum("[intQty]*[curUnitPrice]","qryReconcileSubformPartsPriceList","[txtCCPRNo]=" & Chr(34) & [txtCCPRNo] & Chr(34))

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way around Recordset Not Updatable (2000)

    Thanks Hans! That did the trick. I have one last question. I set the properties of that the calculated DSum field to currency at the query and form level but when I open the form, it won't show in currency format. It just shows the number. 15 instead of $15.00.
    Thanks,
    Deb

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

    Re: A way around Recordset Not Updatable (2000)

    That is because the result of DSum is usually a string. You can use the Val function to convert it to numeric again:

    Val(DSum(....))

    The number format should work then.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way around Recordset Not Updatable (2000)

    THANKS! That did it.

    Deb

Posting Permissions

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