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

    Error when convrting Quote to invoice automatically


    Click image for larger version. 

Name:	error.JPG 
Views:	22 
Size:	22.8 KB 
ID:	34776Click image for larger version. 

Name:	code.JPG 
Views:	22 
Size:	67.1 KB 
ID:	34777

    Hi Guys,

    I'm getting thisrror message when I click a button that is meant to automatically cnvert a quotation into a new invoice. Please s the code it highlights for the error when I click Debug.

    Any ideas why? I checked the spelling o InvoiceID and it is correct

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is InvoiceID a number type?

    Why would you save aggregate data instead of calculating it when needed?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you don't have a textbox on the form named InvoiceID, then you can't use the ME Dot syntax. Use this instead:
    TmpPmt = Nz(DSum("Amount","PaymentsT","InvoiceID=" & [InvoiceID]),0)

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As long as InvoiceID is a number, wouldn't it be
    Nz(DSum("Amount", "PaymentsT", "InvoiceID = " & InvoiceID & ""),0) ?
    I'm not sure you can use Me in a domain lookup criteria.

    EDIT: After testing in immediate window, I see that the double quotes are not needed after the control concatenation. Interesting. So maybe the Me works? I will have to test that.
    EDIT2: OK, so elsewhere that I read, they lied. It does work either way.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Hi June7

    InvoiceID is an autonumber field and shows in a textbox on the invoice frm.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If control isn't named InvoiceID but field is, Me. will still work. I don't see anything wrong with syntax. I tested code and cannot replicate error.

    Again, why save this aggregate calc?

    @Micron, no reason to concatenate empty string to number. Certainly the Me. works in VBA that is constructing domain aggregate function arguments.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    It i part of a Query that runs on the invoice form showing the payments and oustanding balance on the inovice itself. If I remove the line completely the erro disapears but shows up for the currentDb line.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doesn't alter my viewpoint. The data should be calculated when needed, not saved. Calculating a balance is a common topic.

    Aside from that, have you step debugged? Do the variables set to expected values?

    You will probably have to provide db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The double quotes were intended to result in a single ending one, not an empty string. Normally I would have used """ (3) but IIRC that raised an error. In retrospect, I agree that it was simply an empty string.

  10. #10
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Here is theDb Guys. Whn log in use Admin and Admin
    Attached Files Attached Files

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    That download cannot be assembled into a working database.
    We need 2 files, the Front End and the Back End. Nothing else.

  12. #12
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    The file extracts into the folder where EVERYTHING is. Just copy the folder SIO Accounting to the C:\ and open the mainFE file

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How do I replicate issue? Opened form and code runs without error.

    Why does invoice 12 show an amount of 218.50 but a balance of 2185.00?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    I have n idea why that invoice shows that....weird.

    If you open a quote and hit the red button it should transer verythig fro th quote into a new invoice. The button is hidden at th moent till it rks.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't like and never use Navigation form.

    You are loading form InvoiceDataEntryF with no InvoiceID parameter so of course it fails. I see 2 options:

    1. First save record to Invoice table then open Invoice form with InvoiceID filter. This is complicated because retrieving the newly created InvoiceID is tricky.

    2. Instead of Quote form code setting values of field on Invoice form, pass the QuoteID to Invoice form and have code behind Invoice form pull data from Quote record and set its fields. Open Invoice form to a new record and use OpenArgs argument to pass QuoteID.

    DoCmd.OpenForm "InvoiceDataEntryF", acNormal, , , acFormAdd, acWindowNormal, Me.QuoteID

    Then code behind Invoice form:

    If Me.NewRecord And Not IsNull(Me.OpenArgs) Then
    'code to open recordset of single Quote record and populate Invoice fields
    Else
    'other code
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 10-01-2016, 09:19 AM
  2. Replies: 2
    Last Post: 09-14-2015, 09:01 AM
  3. Automatically Creating an Invoice
    By Kelsod in forum Access
    Replies: 8
    Last Post: 05-15-2013, 08:03 PM
  4. Replies: 3
    Last Post: 11-19-2012, 05:05 PM
  5. Replies: 1
    Last Post: 10-10-2012, 01:05 PM

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