Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2001
    Posts
    175

    Unanswered: Updating records in a report to a table

    Hi,

    I want to be able to update a field in a table using the records from a report. For example when I run a particular report and the report return 10rows, I want to take the invoice numbers from the 10 rows and update the "status" field in a table called "clientoi" to "PAID" where it is "OS".

    Any assistance would be appreciated.

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so shove the code to do that in the reports on format or on print event

    sounds a very odd methodology you are following here reports are usually passive ie they report on the cuyrrent snapshot of data, its unusual (nut not unknown) to update the db on runing. you need to make sure you correctly handle possible instances of running the same report more than once (as could easily happen due to operator error or say a snafu with the printer requiring additional runs of the report)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2001
    Posts
    175
    it's the code that I have a problem with.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so run some SQL in the on print format
    docmd.execute is probably the right invocation
    update mytable set status = "PAID" where status="OS" and invoicenumber = <column from report>
    make sure you put soem error trapping around the execute
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2001
    Posts
    175
    Ok - thanks.

  6. #6
    Join Date
    Sep 2001
    Posts
    175
    I tried the following code:

    "update tblClientOI set insstatus = 'PAID' where [tblClientOI].[InvoiceNo] = " & Me!InvNo

    The code works fine if the report only has 1 row (1 invoice no.) but if the report has more than 1 row, only one invoice number is being updated in the table clientoi.

    Where am I going wrong?

    Regards

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where are you placing this code
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2001
    Posts
    175
    I placed it on "close" so that when the user closes the report the records would be updated.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so thats your problem
    place it in the reports on format or on print events as was suggested back in post #4

    if you put it in the on close then it will only act on the last invoice number

    you may be able to do this as a single SQL statement using a sub select.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2001
    Posts
    175
    ok - so I tried the following code on the format and print but only one invoice still being updated.

    Dim uquery As String

    uquery = "update tblClientOI set insstatus = 'PAID' where [tblClientOI].[InvoiceNo] = " & Me!InvNo
    DoCmd****nSQL uquery

    I tried it on the properties for both the Page and Report headers. Where am I going wrong?????

    Regards
    Toukey

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you put a watch on the code to see how many times it fires

    I probably didn't make it clear enough the code should go in the detail on format bot on a group footer or header

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Sep 2001
    Posts
    175
    Ok - It's finally working. Thank you so very much.

    I guess I needed to put it on the detail section.

    Regards
    Toukey

Posting Permissions

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