Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid argument (Access 2000)

    Can somebody help me in writing a code that forbids the user to continue
    if there are no quantites on stock ? In my subform i have a combo box with the
    following row source:

    SELECT DISTINCTROW Products.Productid, Products.grade
    FROM Products;

    From this combo box the user chooses a product to make an order.However,
    if there are no goods on stock, that is when the field branch0 for the relevant product = 0,
    i want to prevet the user to continue.That is i have to check the field branch0 in the table Products.
    I have made the code shown below, but i receive the error
    " you entered an invalid argument in a domain aggregate function".




    Dim MySubform As string
    Dim StrCondition As String

    Set MySubform = [Forms]![MainForm]![Subform].[Form]
    strCondition = "ProductID=" & MySubform.ProductID

    If CLng(DLookup(branch0, "Products", strCondition)) = 0 Then


    MsgBox "There are no quantities on stock !!", vbInformation + vbOKOnly

    end if

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid argument (Access 2000)

    The first argument to DLookup must be a string representing a field name, so it should probably be "branch0" instead of just branch0.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Invalid argument (Access 2000)

    It seems that the field branch0 holds the number in stock , and is a field in the product table.

    Why not just modify the row source of the combo so that only products that are in stock are offered!


    SELECT DISTINCTROW Products.Productid, Products.grade
    FROM Products
    Where Products.branch0 >0;
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid argument (Access 2000)

    Thank you for your reply.You are right,i have corrected to be "branch0". Now i get another error :Invalid use of nul.Could you help me find out the
    reason of the error ?


    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    strCondition = "ProductID=" & MySubform.ProductID
    If CLng(DLookup("branch0", "Products", strCondition)) = 0 Then
    MsgBox "Not available in stock "", vbInformation + vbOKOnly"

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid argument (Access 2000)

    Thank you for your nice suggestion. The reson i want to stay with the full product mix is that
    use the same combo to enter new products into the warehouse.

    kind regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid argument (Access 2000)

    Click in the line Set MySubform =... and press F9. This will place a breakpoint. Next time you run the code, it will pause here. You can execute the code step by step by pressing F8 and you can inspect the value of variables and expressions by hovering the mouse over them. Try to find out what is Null and where the error occurs.

    For example, if strCondition is "ProductID=" after the line strCondition = ... has been executed, there is a problem. And if the DLookup results in Null because there is no record that satisfies the condition, CLng will cause an error. In that case, replace DLookup by DCount.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Invalid argument (Access 2000)

    Won't this mean that the message you are trying to generate will prevent you entering products into the warehouse if the current stock level is 0?

    Or will you write some code that can detect that you are entering new stock and allow you to proceed in that case?
    Regards
    John



  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid argument (Access 2000)

    of the current level of stock is 0 i want to stop and go back again with a clar box. As you can see from above, i managed to prevent the user
    from proceeding.It remains to solve only how can i clear the combo box, since if the product is with 0 value, the name of it stays on the combo box

    best regards

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid argument (Access 2000)

    Just to let you know that your suggestion did wonders !!! I replaced it with DCount, as you said, and on it goes!!!This is a wonderful site
    How can i now clear the combo box? For example, if i choose a product that has 0 quantity, then the message appears that there is no stock available, but then the product stays in the combo box and is not cleared.I want to make the combo box clear but i cant do it. If i make Me![Productid] to be 0, then i get the message that i tried to assign a Null value that is not a variant type.
    I tried another alternative, as Me![productid].Requery, but with no effect.
    How can otherwise clear the combo box ? In a word,i want to allow the choice to begin from the scratch.Is it possible ?

    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    strCondition = "ProductID=" & MySubform.ProductID
    If CLng(DCount("branch0", "Products", strCondition)) = 0 Then
    MsgBox "Not available in stock "", vbInformation + vbOKOnly"
    Me![ProductID] = "" you tried to assign a Null value to a variable that is not a Variant data type
    Or subform![productid] = "" - the same rsult
    Exit Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid argument (Access 2000)

    Does this work?

    MySubform.ProductID = Null

Posting Permissions

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