Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    VBA - Multiple rows


    I'm using this code to check the user is entering some costs before creating an invoice:

    Code:
    If IsNull(Me.Invoice_Generator2.Form.Inv_Quantity) = True Then
    MsgBox "Enter at least 1 line value."
    It works I'm just trying to improve this before I let people on it. Sometimes i get the message box "Enter at least 1 line value." even when lines are entered.

    I'm guessing because one line is null it triggers the event. ( but this is not all the time.)

    Is there a better way to get the results I want?

    Andy.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'd probably use a dsum on the invoice header ID assuming you have such a thing?

    Code:
    Dim InvValue as Currency
    
    InvValue= Dsum("LineValue","InvoiceLineTable","InvHeaderID = " Me.InvHeaderID )
    
    If InvValue= 0 Then msgbox "Zero Value Invoice"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    *deleted*

    Edit: misunderstood the function.

    I think this is fine.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I can't work out why im getting:

    "the expression you entered as a query parameter produced this error"
    "me.Inv_ID"

    using this code:

    Code:
    
    
    Dim InvValue As Currency
    
    
    InvValue = DSum("[Inv_Quantity]", "Invoice_Lines", "[Inv_ID] = Me.Inv_ID")
    
    
    
    
    If InvValue = 0 Then MsgBox "Zero Value Invoice"
    Ill keep troubleshooting, any ideas?

    Andy

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Think its because the query for the form is on a subform. Should be an easy fix.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You Have to concateneate the form value into the DSum;
    Code:
    InvValue = DSum("[Inv_Quantity]", "Invoice_Lines", "[Inv_ID] = " & Me.Inv_ID)
    If it's on the parent form then try

    Code:
    InvValue = DSum("[Inv_Quantity]", "Invoice_Lines", "[Inv_ID] = " & Me.Parent.Inv_ID)
    All of the above assumes ID is a number
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Brilliant. Just as an FYI. (or correct me if I'm wrong)

    The message box wont trigger if the value is null. It seems to pass this Dsum line, there must be a value, otherwise we get message box:

    "invalid use of null"

    Which is no problem. It does as intended now just not with my message box.

    Thanks a lot for the advice.

  8. #8
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Apologies the DSum will return Null if there are no records, I assumed incorrectly it would be 0

    So wrapping it in a Nz function will work to give you 0 if there are no records ;

    Code:
    InvValue = Nz(DSum("[Inv_Quantity]", "Invoice_Lines", "[Inv_ID] = " & Me.Parent.Inv_ID) , 0 )
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    You've been a great help. Thanks very much.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to split a row into multiple rows
    By razkowski in forum Queries
    Replies: 3
    Last Post: 12-30-2014, 01:23 PM
  2. Getting multiple rows into one row in select
    By rbevers in forum Queries
    Replies: 3
    Last Post: 08-30-2013, 01:58 PM
  3. Query on multiple rows
    By TPH in forum Access
    Replies: 10
    Last Post: 09-02-2011, 12:52 PM
  4. Comnbining multiple rows into one row
    By Equalizer700 in forum Queries
    Replies: 1
    Last Post: 04-19-2011, 03:51 AM
  5. Replies: 5
    Last Post: 12-10-2009, 10:33 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