Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    12

    Unanswered: Microsoft Access Can't Update All The Records In The Update Query

    hi,

    My workload table is then used to update several field in another table (tblAuditfeed). Normally, when run, I 1st get the box "You are about to run an update query....." then "You are about to update [#of rows]. All is updated - the end.

    Now I get the box "You are about to run an update query.....", then "You are about to update [#of rows], and finally “Microsoft Office Access can’t update all the records in the update query. Microsoft Office Access didn’t update….48 records due to validation rule violations…”


    This is my SQL view
    UPDATE (tblAuditFees INNER JOIN tblSumOfWorkloadByInvoice ON tblAuditFees.InvoiceID = tblSumOfWorkloadByInvoice.InvoiceID) INNER JOIN tblWorkload ON tblAuditFees.InvoiceID = tblWorkload.InvoiceID SET tblAuditFees.InvoiceID = [tblSumOfWorkloadByInvoice].[SumOfWorkloadPerWorkers];


    My Attachement documents-7.zip
    What am I doing wrong?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what validation checks have you on InvoiceID
    what dataype is invoiceID
    what values are in SumOfWorkloadPerWorkers

    just looking at your code it doesn't look right to me.

    you are updating an invoice id (an odd thing to do in any event) with the sum of workload.

    the only times Ive seen an invoice ID updated is when the system required invoice numbers to be contiguous (ie no missing numbers, in sequence) and they implemented that by writign the row, then securing the the next available sequence number and overwrote/updated the original value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2014
    Posts
    12

    Cannot run the marco

    Quote Originally Posted by healdem View Post
    so what validation checks have you on InvoiceID
    what dataype is invoiceID
    what values are in SumOfWorkloadPerWorkers

    just looking at your code it doesn't look right to me.

    you are updating an invoice id (an odd thing to do in any event) with the sum of workload.

    the only times Ive seen an invoice ID updated is when the system required invoice numbers to be contiguous (ie no missing numbers, in sequence) and they implemented that by writign the row, then securing the the next available sequence number and overwrote/updated the original value
    .

    Hi, thanks for your replies

    Data type for invoice id is text
    SumOfWorkloadPerWorkers is the value want total up in subform and show in the text box name workload (Mainform)..

    I already chnage the invoice id to workload. This is because i want show the total value from subform to text box name workload in the main form but not invoce id.

    However, i facing other problem.
    i use marco to run to sumofworkloadperworker (subform) to text box name workload (mainform).
    However, after i click the marco button. Its seems marco have function but do not show the amount of (sumofworkloadperworker) in text box name workload (mainform).

    The action for run the marco was following:

    Set warning On No

    Open query
    Query Name : qrySumOfWorkloadByInvoice

    Open query
    Query Name : qryUpdateWorkloadAmount

    Repaint Object
    Object Name ; Audit fees main form


    Attachment documents-8.zip
    what i doint wrong for this problem?
    Thanks...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no idea
    I don't use macro's

    does the query(s) work
    does the query(s) give the correct results

    what error message are you getting when you try to run the macro
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2014
    Posts
    12

    Cannot update subform value to main form

    Quote Originally Posted by healdem View Post
    no idea
    I don't use macro's

    does the query(s) work
    does the query(s) give the correct results

    what error message are you getting when you try to run the macro
    Hi,
    No any error message pop up when run the marco.

    If i dont use the marco, do u have any idea to update and display the subform value ( sumofworkloadperworker) to workload field in the main form.

    Any query or formula give the best results..

    Thanks...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no you do get an error message if you run the macro...but you may have to turn the warnings on first.....

    turning warnings off is a smart call if the application is going to be used in a production environment, but whilst you are developing its daft.... its stops you understanding what is going wrong with your code
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2014
    Posts
    12
    Quote Originally Posted by healdem View Post
    no you do get an error message if you run the macro...but you may have to turn the warnings on first.....

    turning warnings off is a smart call if the application is going to be used in a production environment, but whilst you are developing its daft.... its stops you understanding what is going wrong with your code
    Hi,
    after i rerun the marco, its show a error message.

    The object " auditfees (main form) '" isn't open
    *the marco you are runninh contains a Goto Record or selected object action but the object name argument names an object that is closed.
    * The object name argument for the go to record, repaint object, or selected object method name an object that is closes.

    Use one of the actions or methods to open the object



    I do not what open action can be done for open the object..

    Do u have any idea can update the value and display in main form??Thanks...

Posting Permissions

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