Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If IsNull (Access 2000)

    I need help to determine the situation when the value in a table is Null.


    In the afterupdate event of the control Productid i have the following code
    for inserting the price in the control "price"


    Function product()
    Set ctl = [Forms]![FOrderInformation]![Forder details extended].[Form]![UnitPrice]
    Set client = Forms![FOrderInformation]![kindid]
    ctl = DLookup("reseller", "Products", strfilter)
    End Function

    The price is obtained from the table "products", field "reseller"

    I need to include a condition when there is nothing in the field "reseller",
    and in that case to exit the function.

    I have tried the following:
    If IsNull(ctl) Then
    Exit Function
    Else
    ctl = DLookup("office", "Products", strfilter)
    End If


    But the Access then failed and i had to close the computer.
    How can i express this condition?

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

    Re: If IsNull (Access 2000)

    By using IsNull(ctl), you are testing whether the UnitPrice control is null, not whether the reseller field is null.
    The variable strFilter is not defined in your function. Is it defined elsewhere in the code?
    The line Set client = ... does not seem to perform anything useful - you don't do anything with client.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    That is right !, i need to establlish whether the reseller price is null.When there is no reseller price, i get an error in my form.
    The variable strFiler is defined as follows:
    ' Evaluate filter before it's passed to DLookup function.
    strfilter = "ProductID = " & [Forms]![FOrderInformation]![Forder details extended].[Form]![Productid]
    Select Case client
    Case 1
    client = 1 ' unit price with cash payment
    ctl = DLookup("office", "Products", strfilter)
    Case 2 ...... etc



    Thank you in advance for your help

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

    Re: If IsNull (Access 2000)

    Try this:

    Function Product()
    Dim varValue As Variant
    varValue = DLookup("Reseller", "Products", strFilter)
    If Not IsNull(varValue) Then
    [Forms]![FOrderInformation]![Forder details extended].[Form]![UnitPrice] = varValue
    End If
    End Function

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    Thank you. I am afraid my information to you was wrong.Therefore i am sending a rough example of my database.I have included your suggestion in my module "product"
    In my example, the second product has no price.This should mean, that if i choose this product,
    the function must be suspended,to exit the function and the name of the product to disappear from the combo box.
    Thank you in advance

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

    Re: If IsNull (Access 2000)

    Instead of deciding whether a product is acceptable when it is selected from the combo box, you should modify the row source of the combo box to exclude product without a "reseller" price. That way, the user won't be able to select such a product.
    If you add reseller to Query2, you don't need the DLookup, you can simply use reseller itself.

    I have attached a version of your database, but it has probably changed beyond recognition, for it didn't make much sense to me the way it was. The only piece of code in the entire database is now

    Private Sub Productid_AfterUpdate()
    Me!UnitPrice = Me!reseller
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    For me what you have suggested is a stroke of genius.I will start to reconstruct my whole database, and i will make it much
    better and solid.
    Can i solve in this way also my other 2 obstacles, namely the fields branch0 and ietms0 in the table products.
    I want to prevent the user of making an invoice is there is nothing on stock.And there my database works
    not always well.I have added them now to the table ans sent the attachment again.

    the field branch0 means the availability of the cartons in the warehouse,so we cannot issue an invoice if
    no product is available in the warehouse.
    I want to use the afterupdate event in the field cartons:
    If Me!branch0 = 0 Then
    MsgBox "There are no cartons on stock! ", vbInformation + vbOKOnly
    DoCmd.GoToControl "productid"
    Exit Sub
    End If
    The same appllies to the quantity, if items0 = 0

    The user chooses either cartons, or quantity in order to make the invoice.


    Or, a better way, to may the field cartons disallowed,or locked?

    I have to use the afterupdate event of the cartons or quantity,since there is a possibility that in the
    warehouse exists only quantity and not cartons, and in that case an invoice for quantity is also possible.

    Of course i am sure you will suggest a professional solution to these problems



    Of course it will be excellent if the combo box does not show products that have both no branch0 and items0 = 0

    Thank you in advance

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

    Re: If IsNull (Access 2000)

    I think Items0 should be a number field.

    You can set the Row Source of the ProductID combo box to

    SELECT Productid, grade, size, pack FROM Products WHERE reseller Is Not Null AND Nz([branch0],0)+Nz([items0],0)>0 ORDER BY grade

    This will select only products for which reseller is not null, and for which branch0 and items0 are not both 0 or null.

    Locking or disabling cartons or quantity is not a good idea, for if you do this in a datasheet form, it will apply to all records, not just to the current record. So I would keep the code in the After Update event of cartons and quantity.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    Dear Hans,

    A thousand thanks. I am begining now to reconstruct my database and it will be much much

    better than the one i had it up to now.So hard work, but it is worth while.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    Dear Hans,


    I refer to your wonderful solution with which i restricted my combo box for the products only for those who are on stock.
    I am immensely grateful for that.
    I want for only one client, say client Nr 1, called aaa to refer without restrictions, since it will serve to import products
    in the warehouse.

    I imagine to construct my code in the following way:


    If Parent!CustomerId = 1 Then
    Me!ProductID.RowSourse = " QryAll"
    Else
    Me!ProductID.RowSourse = " QryOnStock"
    End If


    I will put that code in the subform, but i am not sure where.On Current or OnOpen?

    Could you please tell me is my code right and what is the most approrpiate place
    for that?
    I am applying the attachement.

    Thank you in advance

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

    Re: If IsNull (Access 2000)

    You shouldn't put the code in the subform, but in the On Current event of the main form, and in the After Update event of the customerid combo box on the main form:

    Private Sub customerid_AfterUpdate()
    SetRowSource
    End Sub

    Private Sub Form_Current()
    SetRowSource
    End Sub

    Private Sub SetRowSource()
    If Me.customerid = 1 Then
    Me.<!t>[Forder details extended]<!/t>!ProductID.RowSource = "QryAll"
    Else
    Me.<!t>[Forder details extended]<!/t>!ProductID.RowSource = "QryOnStock"
    End If
    End Sub

  12. #12
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    Dear Hans,


    I revert to the excellent solution you have given to make a combo box showing only those products that are available on stock, that is, when branch0 and items0 are not both 0 or null.
    In the afterUpdate event of the control cartons i have a function that substracts the avaibale quantity, i.e. branch0 or items0, with the number of the cartons, from the control cartons. I have problems only when there remains one carton on Stock, and I want to make an invoice for this last piece.Then by choosing it my code sticks and the products is not shown in the invoice.
    For example, if there are 2 cartons from the product A, that is branch0 = 2, if i want to subtract 1 carton, then the function is ok, and 1 carton remans on stock.After that however, since there is only 1 carton left, i.e. branch0 = 1 by substracting it and making it to 0, I cannot issue the invoice.
    I think the reason is that the row source of the control ProductID, which is the query QryOnStock is set to 0 and does not allow taking out the last product.
    How can I solve my problem ?
    Thank you in advance

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

    Re: If IsNull (Access 2000)

    In the database you attached, there is no code in the After Update event of cartons...

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If IsNull (Access 2000)

    Yes,but this code in my real database is very complicated to me i now tried to strip it down :

    Public Function SetBranches()
    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    Set cartons = [Forms]![FOrderInformation]![Forder details extended].[Form].[cartons]
    strCondition = "ProductID=" & MySubform.ProductID
    strWhere = " WHERE " & strCondition
    UpdateCartons = "UPDATE Products SET " & _
    " products.branch0 = products.branch0 - " & cartons & strWhere
    DoCmd.RunSQL UpdateCartons
    End Function

    i wanted to send you the real dataabse but it is big and i cannot send it.
    Just to repeat where my problem is. The problem appears when i have only one carton left in the warehouse
    say for product Apples branch0 = 1


    When i issue an invoice, the warehouse is updated by the SetBranches function.So when i update the last piece,
    my updating sticks. This is becasue, i think the query qryOnStock is set to > 0,and hence the problems.is it true?

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

    Re: If IsNull (Access 2000)

    I don't see the need for code like this. You can add branch0 to Query2 and then to the subform, and use one line of code in the Before Update event of the subform:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.branch0 = Me.branch0 - Me.cartons
    End Sub

    Much shorter and easier, and I don't have a problem if branch0 becomes 0.

    See attached version.

Page 1 of 2 12 LastLast

Posting Permissions

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