Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149

    Tried your code as well as the event click on a button and getting th same error. I checked the spelling again and took sxreenshots of it. please see imagesClick image for larger version. 

Name:	invoiceT.JPG 
Views:	6 
Size:	9.3 KB 
ID:	34115Click image for larger version. 

Name:	ibalance.JPG 
Views:	6 
Size:	49.9 KB 
ID:	34116

  2. #17
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    SIO Accounting.zipHere is the database. Use Admin acount to log in with Admin as pasword

  3. #18
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just tried it and it ran with no errors in A2010 32-bit using form load event and the current db execute code I suggested
    Tested with existing record and a new record

    Perhaps an issue with 64-bit? Does the project compile without error?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #19
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Yes it compiles without error in vba. Is there any other places I need to check for compile error ?

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No.
    I'm on a tablet so can't check references. Can you post a screenshot of them
    If not fixed by then, I'll try this in A2016 64-bit tomorrow
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #21
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    References? Not sure what you mean...so sorry or the "stupidity here fro my side

  7. #22
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Would you mind pasting the exact code you used s I can make sure Ive got it like it is suppose to be please?

  8. #23
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Okay, the ship has changed course a bit.

    I moved the Dsum steps to the On Current Event and the SQL section to after insert event in the form. The code I used is :
    DoCmd.RunSQL ("Update InvoiceT SET IBalance =" & Me.txtInvBalance & "Where [InvoiceID] =" & Me.InvoiceID & ";")

    Now I do not get any error messages when I open t form but the sql does not copy the value of the textbox to the field in the table........

  9. #24
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This code works for me - I've also just tested it successfully in A2016 64-bit - no errors & AFAIK it updates as intended

    EDIT: modified to update current record only

    Code:
    Private Sub Form_Load()    Dim TmpInv As Double
        Dim TmpPmt As Double
        Dim TmpBalance As Double
        Dim sSQL As String
        
        TmpInv = InvTotal
        TmpPmt = Nz(DSum("Amount", "PaymentsT", "InvoiceID= " & Me.InvoiceID), 0)
        TmpBalance = TmpInv - TmpPmt
        Me.txtInvBalance = TmpBalance
         
        DoEvents
        CurrentDb.Execute "UPDATE InvoiceT SET InvoiceT.IBalance = " & Me.txtInvBalance & " WHERE InvoiceID = " & Me.InvoiceID, dbFailOnError
     
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #25
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Ridders52 - Thank you so much for all your help. I do ppreciate it

    I dont know what is different in your code to mine but now it works. Only thing is it updates ALL the balances for every record instead of the one for the current record. Im usure its something to o with the criteria?

    Click image for larger version. 

Name:	balances.JPG 
Views:	6 
Size:	65.7 KB 
ID:	34120

  11. #26
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oops - not sure why I didn't spot that

    Corrected code:
    Code:
    CurrentDb.Execute "UPDATE InvoiceT SET InvoiceT.IBalance = " & Me.txtInvBalance & " WHERE InvoiceID = " & Me.InvoiceID, dbFailOnError
    Click image for larger version. 

Name:	Capture.PNG 
Views:	6 
Size:	29.7 KB 
ID:	34121

    I've updated the previous post
    Is that correct now?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #27
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    IT works perfectly now. Many Many Thanks, you are a champion Sir!!.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-28-2017, 07:31 AM
  2. Replies: 8
    Last Post: 12-03-2015, 10:18 AM
  3. Save data from textbox to table
    By GrayWolf in forum Access
    Replies: 7
    Last Post: 03-27-2012, 09:21 AM
  4. Replies: 13
    Last Post: 09-14-2011, 07:19 AM
  5. Replies: 4
    Last Post: 01-05-2011, 07:56 AM

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